vbscript insert into ms access

Question

hi, I am working on a small ap that uses vbscript to write to a ms access db everytime i use it (really for personal use only so i don't need to worry about sql injection). When i run it i keep getting a "syntax error in INSERT INTO statement". The connection string is correct because the db locks when its run. table name is ors. What am I doing wrong?

sql1="INSERT INTO ors  VALUES (,,'B223234','12/22/08')"
constring="Provider=Microsoft.Jet.OLEDB.4.0;
  Data Source=C:\Documents and Settings\me\My Documents\tracker.mdb;
    User Id=admin;Password=;" 
set con=createobject("adodb.connection")
con.open constring
con.execute sql1
con.close

Answer 1

If I remember correctly I think dates in MS Access require # surrounding them instead of single quotes. Try changing your insert to

INSERT INTO ors VALUES (,,'B223234',#12/22/08#)

in addition you may have to specify blanks for the missing parameters

INSERT INTO ors VALUES (NULL,NULL,'B223234',#12/22/08#)

my MS Access knowledge is a bit rusty but give that a shot.

Answer 2

You Need to specify the fields to Insert In:

Example: Insert Into Table1 (test1, test2, test3) Values ('1','2','3')

Answer 3

You need NULL for the "blank" values. You also need to use # surrounding a date instead of the single quotes for date fields.

If you don't want to specify all values, you could specify only the fields you want to set.

(Assuming date field, not text for date value)

This:

INSERT INTO ors VALUES (NULL,NULL,'B223234',#12/22/08#)

Or This:

INSERT INTO ors (Field3, Field4) VALUES ('B223234',#12/22/08#)

Answer 4

INSERT INTO ors(field1, fiedl2) VALUES ('B223234',#12/22/08#)

or

INSERT INTO ors VALUES (NULL, NULL, 'B223234',#12/22/08#)

Might work ..

Answer 5

Also note that the date field must be in either mm/dd/yy format or in another unambiguous format such as mm/dd/yyyy, yyyy/mm/dd or yyyy-mm-dd. If someone runs their system in dd/mm/yy or yy/mm/dd this will cause wrong dates to be inserted.

See http://mvps.org/access/datetime/date0005.htm for a solution that works within Access.

I know you stated this is for personal use only but you should be aware of these details for the future.