Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-10-20
10
Medium Priority
?
967 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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