SQL statement concatenation does not work

[pp8082]

Hi,
I can't figure out what I' doing wrong.

This code works: It loads 5,000 records into the table

CurrentDb.Execute "INSERT INTO OTCBU ([seq#],[datedaily],[RUT] )VALUES (" & reccount & ",#" & xDateDaily & "#, " & xrut & ");"



--------------------------------------------------------------------------
When I try to concatenate the statement as a string and execute it, it does nothing.

mysqlstring = "Insert into OTCBU "
mysqlstring = mysqlstring & " ([seq#],[datedaily], [rut])"
mysqlstring = mysqlstring & " VALUES (" & reccount & ", #" & xDateDaily & "#, " & xrut & " );"

CurrentDb.Execute mysqlstring


CurrentDb.Execute mysqlstring

 

[pr2-eugin]

Try a Debug.Print and see what is inside the mysqlstring before executing it.. To see the Immediate window use Ctrl+G

 

[Rx_]

Just another step if it is the first time to use the debugger:
on the line below the currentDb.Execute mysqlstring add this:
debug.print mysqlstring ' comment this out after testing is completed

The mysqlstring will appear in your debug window
Then open a New Query - change the format to view SQL - and past the SQL from the immediate window into the new query.

This also allows posting the SQL string back on this site.

 

[CJ_London]

I think your problem may be with currentdb, try using codedb instead or create a dim MydDb as Database, set Mydb=currentdb, MyDb.execute(mysqlstring).

Not sure if you are using US or UK date formats, but #date# expects a US format date (mm/dd/yyyy). To retain UK formats you need to use format(xDateDaily,'mmddyyyy') - point is the query may be failing if the date is invalid.

 

[pwbrown]

Surely if the SQL works in a long line, the concatenation should work as the code is fine.
Will rut not being capital like the first statement affect it?

 

[CJ_London]

Access isn't case sensitive so rut/RUT are the same and I can't see any difference in your concatenation other than some additional spaces which shouldn't make a difference. Have you tried using codedb or mydb?

This link http://msdn.microsoft.com/en-us/library/office/ff835631.aspx explains a bit more - you will see that currentdb is not used