Access VBA Insert Into syntax check ? Okay - Not Okay ?

[CJames]

I am rusty on Insert Into VBA code syntax to append records into a SQL Server DB TABLE from MS Access VBA code with a linked to SQL Server table for doing the inserts.

I only get a little time on the Server Cloud desktop later in the week to test this... so I am trying to pre CHECK my code for accuracy before my limited time for testing

IS MY VBA INSERT CODE CORRECT, SOMEWHAT CORRECT, IN ERROR ???

I have VARiables in my insert into VBA code

mnDate = Forms!frFlies!MyDateVar '(ie: "0622') 'A string Variable
'iRps is an int Variable
'OFFDUTY_ and mnDate (both string variable) trying to concatenate that with the word OFFDUTY_ in my syntax.
'dblSumOffDuty is a int decimal variable

Debug does not give an error. Worried about runtime error or SQL insert error.

strInsert = "INSERT INTO dbo_[A4Survey_Client_1].[Survey_1_1207].[Response] RpsRespondent,RpsVariableId,RpsQuestion,RpsMatrix,RpsMention,RpsContent,RpsOpened,projectId,modifiedBy,modified,ImportedValue,Source)" & _
"VALUES (' & iRps & ',1,'OFFDUTY_' + ' & mnDate & ',1,1,' & dblSumOffDuty & ',NULL,1207,999999,GETUTCDATE(),NULL,1)"

db.Execute strInsert
strInsert = ""

 

[pbaldy]

You can use this to check the finished SQL:

Debugging

And add the red bit so you get an error if there is one.

db.Execute strInsert, dbFailOnError

Also note that date/time values need # as the delimiter, not '. Text needs the quote, numeric data types no delimiter.

 

[CJames]

Hi PBaldy... thanks for your info...

I forgot the dbfailonerror part of the execute statement... thank you for catching that gem. )

Yes, i have used the IM Window to debug access vba query strings in the past and it works great but this is SQL Server Insert Into syntax and the immediate window does not parse SQL server Insert Into syntax the same as it would sql Insert Into vba code syntax when inserting into an Access Table due to the Access parser looking for double quotes when SQL server insert into syntax requires single quotes.

When i run the code... The printout i get is this... which is what i would expect since the IM Window would not parse single apostrophes as correct for Insert Into statements for SQL server tables...

INSERT INTO dbo_A4Survey_Client_1.Survey_1_1207.Response(RpsRespondent,RpsVariableId,RpsQuestion,RpsMatrix,RpsMention,RpsContent,RpsOpened,projectId,modifiedBy,modified,ImportedValue,Source)VALUES (' & iRps & ',1,'OFFDUTY_' + ' & mnDate & ',1,1, ' & dblSumOffDuty & ' ,NULL,1207,999999,GETUTCDATE(),NULL,1)...

So i think i am still at square one (minus the excellent dbfailonerror catch)... unless I missed something in the mix ?

thanks... CJ

 

[pbaldy]

Your quoting is off. Only variables/form references outside sets of double quotes. You'd do this type of thing:

"VALUES ('" & iRps & "',1,'OFFDUTY..."

Note the double quotes closing off the string before you concatenate the variable, and then restarting the string after. In the immediate window you should see the finished string, not the concatenation.

FYI if you're using Execute, you're in Access, not SQL Server. The tables may be linked SQL Server tables, but it's being executed in Access thus needs to follow Access rules. You'd use SQL Server syntax if you were using a pass through query.

 

[CJames]

Excellent... Okay... So my assumption was wrong about using the SQL Server DB Query editor syntax... I was over complicating it... If I use the regular access vba insert into syntax it will GO INTO SQL SERVER LINK TABLE JUST FINE...

(and which also would show the correct values in the IM Window when i do Debug.Print in IM Window... as you pointed out)

I appreciate your patience... Thanks...

Edit: It is working like a charm now... )

 

[pbaldy]

No problem. Yeah, it's about where the query is being executed, not where the data actually is. I use SQL Server most of the time, but you only use T-SQL format in SSMS or pass through queries from Access.

 

[pbaldy]

Welcome to AWF by the way!

 

[arnelgp]

does your Table name has dot (.) in them?
on the first place, access will give you error when you do that.
so the tablename is Invalid.
if you get away with it, put them in Square bracket when you use in Query.