Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Createodbcdatetime does not work with MS access

I have data that I'm pullinging out of MS SQL Server and importing into an MS access database.
CF gives me a "syntax error on insert into statement".  I know it has something to do with the datetime field.  Why doesn't MS Access like ODBCDateTime function? What do I have to do to get this to work?
<CFQUERY name="InsertRequestedData" DATASOURCE="gcl2k3b">
INSERT INTO main (unit,label,classmk1,length,width,depth,email,timestamp,rush) VALUES 
(
#unit_id#,
'#label#',
'#classmk1#',
#length#,
#width#,
#depth#,
'#email#',
#CreateODBCDateTime(submitted_dt)#, 
'#YesNoformat(rush)#'
)
</CFQUERY>

Open in new window

0
xamian
Asked:
xamian
1 Solution
 
SidFishesCommented:
think access wants single quotes '#CreateODBCDateTime(submitted_dt)#',

or better still

<cfqueryparam cfsqltype="cf_sql_date" valkue="#CreateODBCDateTime(submitted_dt)#">,
0
 
_agx_Commented:
There is no problem using CreateODBCDateTime with Access.  Most likely one of your column names is a reserved word.  That should be avoided because it causes problems like this one.  My guess would be the column named "timestamp".  Though there may be others.  

Your best bet is to change the column name to something else.  Otherwise, you have to escape the name with square brackes [] in all of your queries:  

INSERT INTO TableName (  [timeStamp], ...)
VALUES (....)

That said, I agree with SidFishes about using cfqueryparam instead.
<CFQUERY name="InsertRequestedData" DATASOURCE="gcl2k3b">
INSERT INTO main (
unit,label, classmk1,length,width,depth,email, YourNewColumnNameHere,rush
) 
VALUES 
(
#unit_id#,
'#label#',
'#classmk1#',
#length#,
#width#,
#depth#,
'#email#',
#CreateODBCDateTime(submitted_dt)#, 
'#YesNoformat(rush)#'
)
</CFQUERY>

Open in new window

0
 
xamianAuthor Commented:
Thanks I knew DATE and TIME where reserved words but I didn't think TIMESTAMP would be a problem.  Putting the brackets worked.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now