VBScript Insert Statement Error when including Date/Time (1 Viewer)

MartinShort

New member
Local time
Today, 22:23
Joined
Aug 30, 2012
Messages
3
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).

Code:
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

Registered User.
Local time
Today, 22:23
Joined
Oct 1, 2009
Messages
2,341
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

New member
Local time
Today, 22:23
Joined
Aug 30, 2012
Messages
3
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

Eledittingent Beliped
Local time
Today, 23:23
Joined
Nov 3, 2010
Messages
6,144
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

New member
Local time
Today, 22:23
Joined
Aug 30, 2012
Messages
3
Not the politest of replies Spike, but thank you anyway, you are correct. I didn't consider keywords would be an issue.
 

Users who are viewing this thread

Top Bottom