Solved

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

Posted on 2006-10-20
10
952 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 142

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

803 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