SQL STATEMENT FROM ACCESS TO SQL SERVER

[ijitnoin]

While executing a Access Application/Program, records are inserted into a table. This from a MS Access program to a SQL Server database (backend). The following INSERT statement was executed:
INSERT INTO table_Import (RecordID, Ctrlno, [Check], CheckDate, Total, ImportDate, Side) VALUES ('3269BEA39C804C15822D826C55F5DAC2', '200605220997', '5006', #5/22/2006#, 8.32, #6/6/2006 10:49:12 AM#, 'FILE')

An error occurs after DB.SQLINSERT statement. What is the problem? In Access, #06/05/06# is valid. Data in the VALUES statement are as defined in the table. I tried to run this INSERT as a View also but errors were received. What is the problem? Thanks in advance for your help!

 

[AlexCuse]

I don't think you can insert #06/05/06# into a date field on SQL Server. Try either inserting to an access table first and appending or inserting without the # signs.

 

[SQLBill]

What is the error message?

Also as AlexCuse pointed out, that may be proper Access dates...but it's not proper SQL Server dates. SQL Server needs the dates to have single quotes around them. Plus it needs to know what format the dates are in. Are those dates mdy or dmy? Start your code with SET DATEFORMAT DMY.

-SQLBill

Posting advice: FAQ481-4875