Solved

VBS, SQL, Access, ODBC - Problems with an Insert Statement

Posted on 2006-10-20
10
954 Views
Last Modified: 2007-11-27
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?
0
Comment
Question by:Orion Newman
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17776597
first, remove the ; from the query.

then, what are those $SID, $NETWORK_USER  ETC    "values"?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17776781
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
 
LVL 3

Author Comment

by:Orion Newman
ID: 17776886
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Author Comment

by:Orion Newman
ID: 17776895
ID is a string in this case...

will try
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17776903
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17776909
other way is to use a ADO Recordset
0
 
LVL 3

Author Comment

by:Orion Newman
ID: 17777029
hmm still same error I tried:

 strSQL = "INSERT INTO ORDER (ORDER_ID, NET_ID,[DATE]) VALUES (" & ID & ",'" & Network_User & "',#" &  ORDER_DATE & "#)"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17777062
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17778294

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
 
LVL 3

Author Comment

by:Orion Newman
ID: 18072284
I ended up havnig to completely change the way this worked...thanks for your assistance...awarded for your time and effort.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question