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.NetWork")
Network_User = objNet.UserName

'open connection to database
    strConn = "DSN=VMFG_LOGS;UID=****;PWD=****"
    Set cnx = CreateObject("ADODB.CONNECTION")
    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?
LVL 3
Orion NewmanDirector of Data ServicesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
regarding

strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,DATE) VALUES ($ID, $NETWORK_USER, $ORDER_DATE);"

is $ID, $NETWORK etc some variables (beit userdefined or standard)

If so, then try putting it outside

e.g.

strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & $ID & ",'" & $NETWORK_USER & "',#" &  $ORDER_DATE & "#)"

Here I assume $ID is numeric, $NETWORK_USER is a string and Date, well a date:)

If $ID is a string then wrap in single quotes

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first, remove the ; from the query.

then, what are those $SID, $NETWORK_USER  ETC    "values"?
0
 
Orion NewmanDirector of Data ServicesAuthor Commented:
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.NetWork")

Network_User = objNet.UserName

Set objNet = Nothing

If USER_7 = "" Then
  USER_7 = Network_User
end
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Orion NewmanDirector of Data ServicesAuthor Commented:
ID is a string in this case...

will try
0
 
rockiroadsCommented:
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 & "#)"


0
 
rockiroadsCommented:
other way is to use a ADO Recordset
0
 
Orion NewmanDirector of Data ServicesAuthor Commented:
hmm still same error I tried:

 strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & ID & ",'" & Network_User & "',#" &  ORDER_DATE & "#)"
0
 
rockiroadsCommented:
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 & "#)"
0
 
Rey Obrero (Capricorn1)Commented:

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 & "#)"

 
 
 
0
 
Orion NewmanDirector of Data ServicesAuthor Commented:
I ended up havnig to completely change the way this worked...thanks for your assistance...awarded for your time and effort.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.