Problems with inline SQL for an insert query 1

[08211987]

Hi,
I am Using Access 2010/VBA. I am having problems creating inline SQL INSERT. Here's my code. The strSQL contains the sql and it doesn't like it no matter what I do:
Set db3 = CurrentDb()
Set rst3 = db3.OpenRecordset("tbl_Mass_Upload_Template_Specialist", dbOpenDynaset)
Do Until rst3.EOF
DoCmd.SetOrderBy "Specialist"
hold_Specialist = rst3![Specialist] 'errors here with No current record
If hold_Prev_Specialist <> hold_Specialist Then
hold_Prev_Specialist = hold_Specialist
Export_Report
End If
hold_GLN = Nz(rst3![GLN], " ")
hold_VBU = rst3![VBU]
hold_Highest_Level_GTIN = Nz(rst3![Highest_Level_GTIN], " ")
hold_Lowest_Level_GTIN = Nz(rst3![Lowest_Level_GTIN], " ")
hold_Assortment_Number = rst3![Assortment_Number]
hold_Item_Number = rst3![Item_Number]
hold_Model_Number = Nz(rst3![Model_Number], " ")
hold_Country = Nz(rst3![Country], " ")
hold_Internal = Nz(rst3![Internal], " ")
hold_Category = Nz(rst3![Category], " ")
hold_Item_Type = Nz(rst3![Item_Type], " ")
hold_Item_Description = Nz(rst3![Item_Description], " ")
hold_Customer_Description = Nz(rst3![Customer_Description], " ")
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values (hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)"
DoCmd.RunSQL strSQL
rst3.MoveNext
Loop

I've tried it 2 ways wondering if I need to put text fields in quotes, one with and one without.
With:
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values ('" & hold_Specialist & "', "'" & hold_GLN & "'', hold_VBU, '" & hold_Highest_Level_GTIN & "'', '" & hold_Lowest_Level_GTIN & "'', hold_Assortment_Number, hold_Item_Number, '" & hold_Model_Number & "'', '" & hold_Country & "'', '" & hold_Internal & "'', '" & hold_Category & "'', '" & hold_Item_Type & "'', '" & hold_Item_Description & "'', '" & hold_Customer_Description & "'')"


Without:
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values (hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)"

For the one without quotes it doesn't find the field/parameter.

For the one with quotes it gives me an invalid SELECT INSERT.

Appreciate any assistance!

 

[MajP]

That is pretty difficult to tackle in one bite, and impossible to debug. Break it down into pieces

In insert queries your string needs to resolve to something like

Insert into sometable (StrField, NumberField, DateField, OtherField) values ('someString', 123, #10/11/2016#, NULL)

So break this into digestable pieces. I provided some helper functions to wrap the strings and dates

Leave all variables as variants so you can return NULL
Then for every string field do something like

hold_GLN = rst3![GLN]
hold_GLN = sqlTxt(hold_GLN)

Do the same for dates using the sqlDate function

In your NZ function you do not want a space, you need to return NULL. Think about it. Your string would look something like
Insert into sometable (StrField, NumberField, DateField) values (, 123, #10/11/2016#)
Instead you want
Insert into sometable (StrField, NumberField, DateField) values (NULL, 123, #10/11/2016#)

Break it into smaller pieces

dim strSql as string
dim StrValues as string
'This functions does the concatenation of the values into a long string a lot easier than working with a bunch of & ",'" & "',"
strValues = InsertValues(hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)
'verify the value string
debug.print strValues 

strSql = INSERT Into tbl_Mass_Upload_Template_Specialist_Next
strSql = strSql & " (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description)"
strSql = strSql & " VALUES " & strValues
'verify the result
debug.print strSql

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  end if
End Function

Public Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function

Now show us the debug string and maybe we can see what is wrong.

 

[Andrzejek]

Also, when posting, wrap your code into TGML

 tags so it is easier to read.
See how much nicer MayP's post is...?

Have fun.

---- Andy

[i]There is a great need for a sarcasm font.[/i]

 

[08211987]

Wow this is some great education, thanks!!

I wanted to start first with what you said about the NZ Function:
In your NZ function you do not want a space, you need to return NULL. Think about it. Your string would look something like
Insert into sometable (StrField, NumberField, DateField) values (, 123, #10/11/2016#)
Instead you want
Insert into sometable (StrField, NumberField, DateField) values (NULL, 123, #10/11/2016#)

I coded this: hold_Item_Description = Nz(rst3![Item_Description], Null) and when I debug it gives me run-time error '94' Invalid use of Null which is what I was getting before and started using the NZ function. I typed Null as NULL but it changed it to Null. I can put it in quotes "NULL" but I am not sure what the sql will do with it or even if it recognizes it as NULL.

Thanks for all your help!

 

[08211987]

MajP,
I made the changes you suggested and here are the debug strings:

strValues = (CASSANDRA CURETON, '0837365000105', 62564, '00141181354978', '0837365000105', 28747, 612204, 'EIDW5705PB', 'USA', 'Yes', 'Gas Riding Lawn Mo', 'Stock', 'NULL', 'NULL')

strSql =
INSERT Into tbl_Mass_Upload_Template_Specialist_Next
(Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) VALUES (CASSANDRA CURETON, '0837365000105', 62564, '00141181354978', '0837365000105', 28747, 612204, 'EIDW5705PB', 'USA', 'Yes', 'Gas Riding Lawn Mo', 'Stock', 'NULL', 'NULL')

I received an error Run time error '3075' Syntax error (missing operator) in query expression 'CASSANDRA CURETON'. I did get that error yesterday when I attempted the correct punctuation for the strValues so this run seems to be matching that run. Although I like the functions you provided! Is it missing something else?

Thanks!

 

[08211987]

Wait I think I see something.
I'll reply back.

 

[Andrzejek]

If Specialist is a text field, you need quotes around CASSANDRA CURETON,
If you want Nulls in Item_Description and Customer_Description, you cannot have quotes around 'NULL', otherwise you will end up with the word NULL in Item_Description and Customer_Description fields.



Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

The way I do this is I dimension all my variables as variants and do not do any NZ functions. The function insertValues takes care of the Null values that are passed in.

dim fld1 as variant
dim fld2 as variant
dim fld3 as variant

then I do not do any NZ

fld1 = rs!fld1
'if fld1 is a text field I next do
fld1 = sqlTXT(fld1) ' If it is null it returns a null value not 'NULL'
fld2 = rs!fld2
'if fld2 is a date field i do
fld2 = sqlDate(fld2)
'if fld3 is numeric
fld3 = rs!fld3

'Then you can get your value string
strvalues = insertValues(fld1,fld2,fl3)

If there are any null values passed in it returns the string like this taking care of the null
('SomeString',#10/11/2016#, NULL)

 

[MajP]

Here is a better function I found. It is a single function so much easier to work with.

Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
            If DateValue(Value) = Value Then
               Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
            Else
               Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

Then dimension everything as a string

dim hold_Date as string
dim hold_number as string
dim hold_text as string
then
hold_Date = CSql(rs3!hold_date)
hold_number = CSql(rs3!hold_number)
hold_text = CSql(rs3!hold_text)

 

[08211987]

Thanks! The SQL is working and I have it as you had suggested up to the time 14:49. I just have a deadline of Friday so at this point I am very pleased! I had some issues with the Variant where the SQL could not run again. I didn't have time to try the last post with the CSql but will give it a try in when I have a moment.

I want to thank you for all of your help, you've saved the day!

 

[MajP]

I have tested that function, and works well. Makes writing these long inserts a lot easier.