VBS, SQL, Access, ODBC - Problems with an Insert Statement
I am getting this error: '[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO Statement.' At Line 18 Position 4.
Statement is :
Dim strSQL
Dim strConn, cnx
Dim prompt, title
Dim objNet
Dim Network_User
Set objNet = CreateObject("WScript.NetW
Network_User = objNet.UserName
'open connection to database
strConn = "DSN=VMFG_LOGS;UID=****;PW
Set cnx = CreateObject("ADODB.CONNEC
cnx.Open strConn
'Create query statement
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,DATE) VALUES ($ID, $NETWORK_USER, $ORDER_DATE);"
'execute Update query
cnx.Execute strSQL
cnx.Close
Set cnx = Nothing
'wrap up macro
prompt = "Macro just recorded your order information."
title = "Macro - Master Recording"
MsgBox prompt, vbInformation + vbOKOnly, title
I have a an ODBC connection to an access database.
Database is VMFG_LOGS
table is ORDER
field names are ORDER_ID, NET_ID, DATE
Why won't this work?
Statement is :
Dim strSQL
Dim strConn, cnx
Dim prompt, title
Dim objNet
Dim Network_User
Set objNet = CreateObject("WScript.NetW
Network_User = objNet.UserName
'open connection to database
strConn = "DSN=VMFG_LOGS;UID=****;PW
Set cnx = CreateObject("ADODB.CONNEC
cnx.Open strConn
'Create query statement
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,DATE) VALUES ($ID, $NETWORK_USER, $ORDER_DATE);"
'execute Update query
cnx.Execute strSQL
cnx.Close
Set cnx = Nothing
'wrap up macro
prompt = "Macro just recorded your order information."
title = "Macro - Master Recording"
MsgBox prompt, vbInformation + vbOKOnly, title
I have a an ODBC connection to an access database.
Database is VMFG_LOGS
table is ORDER
field names are ORDER_ID, NET_ID, DATE
Why won't this work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The values that i want put into the table. They come from the application. Basically this is running in ERP software. When input is saved by the user the software will run this macro on save. The fields that the user is filling in that i want to record are ID, and ORDER_DATE.
The Network_User is something i log already with this macro below. But I want to start logging it into an access database along with the ID and Date. SO this was my best attempt i pasted together from a few other working macros in our system.
Dim objNet
Dim Network_User
Set objNet = CreateObject("WScript.NetW
Network_User = objNet.UserName
Set objNet = Nothing
If USER_7 = "" Then
USER_7 = Network_User
end
The Network_User is something i log already with this macro below. But I want to start logging it into an access database along with the ID and Date. SO this was my best attempt i pasted together from a few other working macros in our system.
Dim objNet
Dim Network_User
Set objNet = CreateObject("WScript.NetW
Network_User = objNet.UserName
Set objNet = Nothing
If USER_7 = "" Then
USER_7 = Network_User
end
ASKER
ID is a string in this case...
will try
will try
Just use the variables, u need to build the string, u cant put variables inside your string
string fields are surrounded by quotes
dates are with hashes
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & YourOrderIDField & ",'" & Network_User & "',#" & YourOrderDateVariable & "#)"
string fields are surrounded by quotes
dates are with hashes
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & YourOrderIDField & ",'" & Network_User & "',#" & YourOrderDateVariable & "#)"
other way is to use a ADO Recordset
ASKER
hmm still same error I tried:
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & ID & ",'" & Network_User & "',#" & ORDER_DATE & "#)"
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & ID & ",'" & Network_User & "',#" & ORDER_DATE & "#)"
If ID is a string then it has to be in quotes
also can u verify that these actually have values?
Whats the command again, is it WScript.Echo or something to dump the value on the screen
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES ('" & ID & "','" & Network_User & "',#" & ORDER_DATE & "#)"
also can u verify that these actually have values?
Whats the command again, is it WScript.Echo or something to dump the value on the screen
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES ('" & ID & "','" & Network_User & "',#" & ORDER_DATE & "#)"
if ID is numeric
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & ID & "," & chr(34) & Network_User & chr(34) & ",#" & ORDER_DATE & "#)"
If ID is text
strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES ('" & ID & "'," & chr(34) & Network_User & chr(34) & ",#" & ORDER_DATE & "#)"
ASKER
I ended up havnig to completely change the way this worked...thanks for your assistance...awarded for your time and effort.
then, what are those $SID, $NETWORK_USER ETC "values"?