Insert into Access Table using SQLExec

[Raven123]

Hello,

I am attempting to insert records into an Access table using the SQLExec() command... All was well until I attempted to insert a record to an Access table with a Date/Time field. Does anyone know the format for sending date/time values to Access?

I have tried:
? SQlExec( con, "Insert into events (Date) values (‘01/01/01’ )" )
? SQlExec( con, "Insert into events (Date) values (‘01/01/2001’ )" )
? SQlExec( con, "Insert into events (Date) values ( 01-01-2001 )" )
etc…


Thanks in advance for any help,

Rich.

 

[spayne]

Have you tried:

? SQlExec( con, "Insert into events (Date) values CTOD(‘01/01/2001’ )" )

 

[Raven123]

Sorry, but it doesn't seem to work. ODBC/Access doesn't understand the 'CTOD' command...

 

[spayne]

How about:

ldDate = CTOD('01/01/2001')
? SQlExec( con, "Insert into events (Date) values (ldDate )" )

 

[Raven123]

Sorry, still won't work... If you pass a string with '(ldDate)' to ODBC, it will take it literally (as 'ldDate' instead of 01/01/01)...

 

[spayne]

Where are your quotation marks? There shouldn't be any quotation marks around the variable name. I just finished a project that where I did just what you are doing, only I didn't have any date fields to worry about. All I did was pass variables, and it works great. I don't know if the versions make any difference, but I am using VFP6.0 and Access 2000.

 

[ramani]

? SQlExec( con, "Insert into events (Date) values {01/01/2001}&quot
Hope this helps you. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[jonscott8]

Access uses the # key as the date delimiter:

? SQlExec( con, "Insert into events (Date) values (#01-01-2001#)" )
Jon Hawkins

 

[jonscott8]

Appendum:

A better way would be to send the value parameterized:

mydate=CTOD('03/22/2001')
? SQlExec(con,"Insert into events(Date) values(?mydate)&quot Jon Hawkins

 

[Raven123]

Figured out the problem... The date: '01/01/2001' actually works. It was the field name, 'Date' that was causing the problem... Apparently this is a reserved word in ODBC... I changed the field name, and all is well.

Thanks a lot for all the help.

Rich.