Insert Into Query

[shylock]

I am trying to insert a record into the LogicalServer table with an INSERT INTO query from VBA. I get a "Syntax error in INSERT INTO statement" when I use DoCmd.RunSQL sqlAppend statement.

I set up the variable I use for the VALUES portion of the query as follows:

 SNm = Me.txtServerNm
    For Each varItm In lstIP.ItemsSelected
        vIP = Me.lstIP.Column(0, varItm)
    Next varItm
    CD = Me.txtComDate
    For Each varItm In lstServerType.ItemsSelected
        vType = Me.lstServerType.Column(0, varItm)
    Next varItm
    If (Me.txtDecommDate = "") Then
        Ddaate = Empty
    Else
        Ddate = Me.txtDecommDate
    End If
    Stat = Me.txtStatus
    For Each varItm In lstSvrTypeId.ItemsSelected
        PhysS = Me.lstSvrTypeId.Column(0, varItm)
    Next varItm

My sqlAppend is

 sqlAppend = "INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical]" _
                & " VALUES (" & SNm & ", '" & vIP & ", '" & CD & ", '" & vType & ", '" & Ddate & ", '" & Stat & ", '" & PhysS & "');"

My table has the following fields:

Field Name Data Type
LogicalServerID Number This number is assigned automatically upon saving the record. It is also a GUID as are IPID and LogicalServerTypeID
Name Short Text
IPID Number
CommissionDate Date/Time
LogicalServerTypeID Number
DecommissionDate Date/Time
Status Short Text
Physical Yes/No

the DeBug>print sqlAppend shows the following:

INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical] VALUES (AAAA-JackTest, '{72FE854E-5056-9F6E-4C35-F6A172A91CA9}, '3/21/2019, '{A331FB98-E7FE-E555-74FA-AC3CD4221013}, '12:00:00 AM, 'Active, 'False');

All the VALUES are correct.

If I include the LogicalServerID field in the query I still get an error as this Value would be empty since it is not assigned until the record is saved.

Can anyone show me what is wrong?

[ranman256]

dont use vba sql, instead use an append query. It wont get the syntax wrong.

[shylock]

ranman256:

I changed from INSERT INTO to APPEND INTO as follows:

 sqlAppend = "APPEND INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical]" _
               & " VALUES (" & SNm & ", '" & vIP & ", '" & CD & ", '" & vType & ", '" & Ddate & ", '" & Stat & ", '" & PhysS & "');"

I get an error: "Run-time error '3129' Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Do you have any other suggestions?

[moke123]

Why the For each loop? are you using a multi-select listbox?

[ssanfu]

I see lots of little things and a big thing.

You don't state what the BE is.... I am thinking it is SQL Server (Express)???

Little things:
"Name" is a reserved word - shouldn't be used for object names.
Possible you have misspelled words: [Sttus]

In the code

    If (Me.txtDecommDate = "") Then
         Ddaate = Empty
    Else
        Ddate = Me.txtDecommDate
    End If

The big thing is you do not have the delimiters correct.
Without knowing what your field types are and what dB engine you are using, this is what I would start with

sqlAppend = "INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical]"

 sqlAppend = sqlAppend & " VALUES ('" & SNm & "', '" & vIP & "', '" & CD & "', '" & vType & "', '" & Ddate & "', '" & Stat & "', '" & PhysS & "');"

The variable "SNm" needs delimiters. You are missing the closing delimiter for most of the rest of the variables.



BTW, the top 2 lines of EVERY code module should be

Option Compare Database
Option Explicit      '<<-- 

[shylock]

ssnafu:

Thanks for the reply. It makes sense that I didn't have the delimiters correct. I will redo them and try again when I go back to work on Monday.

I am using MS Access 2013. Is that what you meant by "You don't state what the BE is...."?

The little things: I have corrected the mistyped words: Ddaate is Ddate; SttuS is Status.

I know Name is a reserved word but the table comes from SQL Server and I am not permitted to change the original tables.

I will let you know on, Monday, if your delimiter changes work.

[Minty]

As an extra gotcha, your date fields will need to be delimited with # # if this is Access, not ' ' .

[moke123]

I have corrected the mistyped words: Ddaate is Ddate; SttuS is Status.

Then I hope you didnt miss steve's advice

BTW, the top 2 lines of EVERY code module should be
Code:
Option Compare Database
Option Explicit '<<--

[shylock]

ssnafu:

could you please resend your version of the sqlAppend. What you sent is overlapping and garbled.

Also, can you explain why each cn with ode module should begin with "Option Explicit"?


I have modified my SQL to get it as close to what ssnafu suggests.

   sqlAppend = "INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical]" _
               & " VALUES('" & SNm & "', '" & vIP & "', '" & CD & "', '" & vType & "', '" & Ddate & "', '" & Stat & "', '" & PhysS & "');"

I still get the syntax error. Is thois because of what Minty says in post #7? If so, how do I put the # symbols around the dates?

[ssanfu]

Speaking of misspelling, it is ssanfu - not ssnafu....

I am using MS Access 2013. Is that what you meant by "You don't state what the BE is...."?

I saw the single quotes for the date and thought you might have a SQLS as the BE.


OK, when creating an SQL statement, if the data type is a number, no delimiters are needed.
If the data type is text, must use single/double quotes delimiters.
If the data type is date/time, must use hash (#) mark delimiters.
See Build an SQL statement in VBA

sqlAppend = "INSERT INTO LogicalServer([Name], [IPID], [CommissionDate], [LogicalSerTypeID], [DecommissionDate], [Sttus], [Physical]"
sqlAppend = sqlAppend & " VALUES ('" & SNm & "', '" & vIP & "', #" & CD & "#, '" & vType & "', #" & Ddate & "#, '" & Stat & "', '" & PhysS & "');"

Note: I added the delimiters (#) for the two date fields.

[shylock]

ssanfu:

Oops, sorry about that. Maybe I'm a bit dyslexic..

I tried the following code per your suggestion:

    sqlAppend = "INSERT INTO LogicalServer ([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical]"
    sqlAppend = sqlAppend & " VALUES ('" & SNm & "', '" & vIP & "', #" & CD & "#, '" & vType & "', #" & Ddate & "#, '" & Stat & "', '" & PhysS & "');"

I still get a syntax error.

the Debug.Print is correct, as shown:

INSERT INTO LogicalServer ([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical] VALUES ('A1A-JacksTest', '{AE7E8E8B-E7FE-E555-7F9E-F1025BD0D729}', #3/22/2019#, '{A331FB98-E7FE-E555-74FA-AC3CD4221013}', #12:00:00 AM#, 'Active', 'No');

Thanks for showing me how to put the # delimiters in.

Thanks,
Jack

[Micron]

these are guid values {A331FB98-E7FE-E555-74FA-AC3CD4221013} ??
They are not text. I've had very little experience with them but think they're 32 byte values and need to be referenced as

{guid{A331FB98-E7FE-E555-74FA-AC3CD4221013}} or something like that. Would have to dig deeper to be sure.

After digging, maybe:
sqlAppend = "INSERT INTO LogicalServer ([Name], [IPID], [CommissionDate], [LogicalServerTypeID], [DecommissionDate], [Status], [Physical]"
sqlAppend = sqlAppend & " VALUES ('" & SNm & "', '" & vIP & "', #" & CD & "#, {guid{" & vType & "}}, #" & Ddate & "#, '" & Stat & "', '" & PhysS & "');"

[ssanfu]

I still get a syntax error.

It always helps to say what the error is.......

I've never used GUIDs either.
I didn't/don't know what the field types are for IPID and LogicalServerTypeID (both look to be GUIDs).....

[Micron]

I see that I missed one. Oh well, hopefully the sample provided enough of a clue.

[Minty]

Also - is Physical a boolean field? If so "No" won't be valid.
Either 0 -1, True False or No Yes without the quotes.