|
Subject:
|
Access 2002 VBA with SQL using a variable
|
|
Posted By:
|
cpopham
|
Post Date:
|
12/4/2003 10:32:19 AM
|
Hello everyone,
I am new to this forum. I can not seem to find an answer anywhere. I know that you can use a variable in vba with sql, however, this is my code:
Option Compare Database
Option Explicit
Dim strSaveAs As String
Dim strSQL As String
' Set SQL variable to SQL statement
strSQL = "INSERT INTO TblAccessDate ([Date],[TableName])" & _
"VALUES (Date(), ' & strSaveAs & ');"
' Run SQL Statement
DoCmd.RunSQL strSQL
Okay the statement part to insert a date into the table works fine, but I can not get the INSERT INTO statement to pass the variable to the table also. I am basically using the table to see what date and what file was created when a user runs this code. I have tried a few different way of trying to accomplish this. Any ideas???
Thanks, Chester
|
|
Reply By:
|
Kenny Alligood
|
Reply Date:
|
12/4/2003 10:36:32 AM
|
Since you are trying to pass a string you need to include some double quotes. Try this for your SQL string...
"VALUES (Date(), '" & strSaveAs & "');"
Kenny Alligood
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/4/2003 10:43:27 AM
|
Thanks,
I tried it that way, but the single quote behind the double quote made it a comment and gave me a syntax error.
I have also tried:
"VALUES (Date(), " & strSaveAs & ");"
But it still will not pass the variable to through the INSERT INTO statement.
Chester
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/4/2003 1:49:32 PM
|
When I try double quotes in single quotes like this;
"VALUES (Date(), '" & strSaveAs & "');"
I get this error message:
Invalid SQL Statement; 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
|
|
Reply By:
|
John K. King
|
Reply Date:
|
12/4/2003 2:15:35 PM
|
Hi:
The additional double quotes were necessary, but there are a couple of other things:
1) date() is a vb function, SQL doesn't know what to do with it. Pass the date in as a value
2) Insert a space prior to the 'VALUES' keyword, otherwise the two lines run together. It's easy to lose track of that type of thing when using line continuations. That's probably where the syntax problem is coming from.
So, try
" VALUES (#" & CStr(Date()) & "#, '" & strSaveAs & "');"
I think that will do it for you.
JK
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/4/2003 2:46:35 PM
|
That worked great. Thanks for the help.  One question though,
In my original code when I had the date as date() it inserted the date fine into the table, how come it worked that way, but after changine the SQL around it would not work the same?
Thanks agian, Chester
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/4/2003 3:10:01 PM
|
Okay got the time figured out also thanks for all the help. Somethings you think are simple and a couple of little porblems in the code will drive you nuts!!!
|
|
Reply By:
|
John K. King
|
Reply Date:
|
12/4/2003 3:19:03 PM
|
I may have been too long away from Access (I work with SQL Server). It looks like Date() is a legitimate function in a SQL statement from within Access.
The best way to answer your question is to
1) add the line
Debug.Print strSQL
to the procedure after the point where you set the variable strSQL
2) modify the procedure to set the variable strSQL to the original string, step through it, and see the SQL statement printed in the immediate window.
3) modify the procedure to set the variable strSQL to the string where Date() failed, step through it, and see the SQL statement printed in the immediate window.
You may be able to identify the reason for the failure when comparing the two strings. If not, just post the results and I'll see if I can see the reason.
To be honest, I don't know why it didn't work when you first added the double-quotes. I would have expected
"VALUES (Date(), '" & strSaveAs & "');"
to work fine. It may have been that the missing space (separating the closing parenthesis and the 'VALUES' keyword) was the problem then.
JK
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/4/2003 3:24:45 PM
|
Okay thanks, either way, my goal is accomplished!!!
|
