Solved

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

Posted on 2006-10-20
10
958 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

688 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