SQL syntax error - I WILL PAY TO GET THIS RESOLVED 2

[datavisions]

I'm getting a syntax error on the SQL below now and after staring at it for FIVE hours now I'm about to go mental. Can someone help me please?

Public Function ParseAndStoreAADetailRecord(GFile)
On Error GoTo Err_Handler

Dim db As Database

Dim AN As String
Dim RC As String

Set db = DBEngine(0)(0)

AN = Mid(GFile, 1, 12)
RC = Mid(GFile, 107, 30)

db.Execute "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RejectCode)" _
& " VALUES (" & AN & _
"," & RC & ""

End Function

 

[Quehay]

If I read this correctly you're trying to insert string values. You need to wrap them in quote delimiters.

"INSERT into mytable(col1,col2)" & _
"VALUES (" & chr(39) & stringval & chr(39) & "," & _
chr(39) & straval2 & chr(39) & "}"

I used chr(39) which is single quote. This works in Access and it works in SQL Server ( chr(34) or " doesn't).

 

[datavisions]

Quehay: I tried that, but it still won't work. I've done this a million times before, so I just don't know what the problem is?!?!?!?!

 

[mike777]

Datavisions:
I feel your pain.
You have probably already tried this, but what I do in situations like this is create a dummy string with literal values, execute it, then create a string variable and use the debug window to help me modify the code that creates the string variable until it matches my literal string.
Also, I would lose the line continuation characters/format until I had it down, and then insert them after everything works.

OK, I mean, instead of:

db.Execute db.Execute "INSERT into tblAAAccidentTemp(" & "OldAccountNumber, " & "RejectCode)"  & " VALUES (" & AN & "," & RC & ");"

I would say:

db.Execute "INSERT into tblAAAccidentTemp(OldAccountNumber,RejectCode) values ('123456789111','SomeString30CharactersLong')"

or:

db.Execute "INSERT into tblAAAccidentTemp(OldAccountNumber,RejectCode) values (""123456789111"",""SomeString30CharactersLong"")"

(I'm pretty sure that's how you can force quotes into a string)

Then, when my literal string goes in smoothly:

Dim str_Test as String
str_Test="INSERT into tblAAAccidentTemp(" & "OldAccountNumber, " & "RejectCode)"  & " VALUES (" & AN & "," & RC & ");"
debug.print str_Test

I keep modifying

str_Test="INSERT into tblAAAccidentTemp(" & "OldAccountNumber, " & "RejectCode)"  & " VALUES (" & AN & "," & RC & ");"

until it matches the literal string I used to successfully execute the basic command (db.execute).

When I am confident str_Test will fly, test

db.execute str_Test

just to make sure.

Then I can do my

db.execute

thing with the exact syntax that I used to create str_Test (or just leave the string variable in and always use

db.execute str_Test

; this will help with future troubleshooting; might want to change the variable name to something more meaningful, like str_DBExecute or something).

When all is said and done, I can go back to the code that creates str_Test and make the lines fit better on the screen with the line contination characters.

That's my two cents' worth. Hope it helps.

-Mike

 

[datavisions]

mike777: I'll try that right now and let you know.

I REALLY appreciate everyone's help and I hope I get through this.

 

[datavisions]

Mike777: I tried:

I would say:
db.Execute "INSERT into tblAAAccidentTemp(OldAccountNumber,RejectCode) values ('123456789111','SomeString30CharactersLong')"

or:
db.Execute "INSERT into tblAAAccidentTemp(OldAccountNumber,RejectCode) values (""123456789111"",""SomeString30CharactersLong"&quot"

and, although neither gave me a syntax error, nothing ended up on the table.

 

[mike777]

Excellent!! We've solved one issue, now we have another. This is progress. I am not familiar with your approach to appending records to a table. Let me try is a bit on my side...standby...OK, I tried:

Sub ParseAndStoreAADetailRecord()
Dim db As Database
Set db = DBEngine(0)(0)
db.Execute "INSERT into version(version,test) VALUES (2,'test')"
End Sub

also:

Sub ParseAndStoreAADetailRecord()
Dim db As Database
Set db = DBEngine(0)(0)
db.Execute "INSERT into version(version,test) VALUES (3,""test"")"
End Sub

They both worked for me.

Anybody have any ideas?

 

[datavisions]

I GOT IT! No syntax errors, and it INSERTS perfectly. Here's the full SQL (I had narrowed it down to two fields for simplicity earlier).

The trick is to put SINGLE quotes AROUND the double quotes, like:

'" & variable & "'

so here's my new code:

db.Execute "INSERT into tblAAAccidentTemp(" _
& "OldAccountNumber, " _
& "RecordType, " _
& "CertificateId, " _
& "ArrearsIndicator, " _
& "NewAccountNumber, " _
& "LockCode, " _
& "BillingDate, " _
& "RejectCode)" _
& " VALUES('" & AN & "','" & RT & "','" & CI & _
"','" & ARRI & "','" & NAN & "','" & LC & _
"','" & BD & "','" & RC & "');"

I can't thank everyone enough for their time and energy regarding this problem of mine! Thanks a million! Beer is on ME next time you're in Toronto, Canada!

 

[mike777]

congratulations..good job

 

[datavisions]

mike777: You sure have a way of calming down a hysterical programmer-in-distress with your demeanor. Thanks a million!