VBScript Insert Statement Error when including Date/Time

[MartinShort]

Hi

I'm trying to import a row into an Access table; using a fairly simple VBScript.

The code below works perfectly UNTIL I add the final field - which is a date field (there's also a time field, which I haven't added yet).

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.open "Provider=Microsoft.ACE.OLEDB.12.0; Data source=C:\test.accdb"
rs.CursorLocation = adUseClient

	
rs.open "INSERT INTO tblTimings (Tester,LoadTimeOld,SaveTimeOLD,LoadTimeNew, SaveTimeNEW,LocalHost,UserName,Date) " & _ 
		"VALUES ('QTP',1,2,3,4,'PC0685','mshort',#2012/04/12#);", _ 
		cn, adOpenStatic, adLockOptimistic

cn.Close
Set rs = Nothing
Set cn = Nothing

Anyone able to help me with my syntax? It's driving me nuts and shouldn't be this complicated.

Thanks
Martin
ps - I realise this is VBS not VBA, but it's pretty well the same thing, so it seemed to make sense to bend the rules slightly on where to post this.

 

[Trevor G]

Martin I am not sure if you are aware when using dates you have to conform to the USA system in VBA so I would assume the Same in VBSCript. Perhaps this is the problem

[FONT=&quot]#2012/04/12#[/FONT]

Might work as

[FONT=&quot]#04/12/2012#[/FONT]

 

[MartinShort]

Thanks Trevor. That was the first thing I looked for as SQL hates Brits by default.

I did try both and a few other variations. No joy.

 

[spikepl]

What is the actual error text? Posting without listing the error text is a bit ineffective.

"Date" is a reserved word, and probably that's what screws it up.
#yyyy/mm/dd# as input format is fine, because it is not ambiguous.

 

[MartinShort]

Not the politest of replies Spike, but thank you anyway, you are correct. I didn't consider keywords would be an issue.