I have an application built in Access 2002 and running in Access 2002 /2003. It is a split with an Access front end and multiple versions of the back end. Access, Sql Server 2005 Express, and Sql Serve 2008 Express. All of the versions in what ever combination run fine with the application front end running Access 2002 or 2003 on the client side and the backend running Access 2002/2003 or either version of SQL Server Express on the server side. The tables are linked so that the application remains an Access application except for storing the data. There is a desire to upgrade to Access 2007/2007 runtime.The application runs fine with the Access 2007/2007 runtime when the front end is Access and the back end is Access but fails when SQL2005 or SQL 2008 is the back end. The application has code that errors out when the client is Access 2007 or Access 2007 runtime and the backend is SS 2005/2008 with an ODBC error - ODBC call failed - unable to convert string to date - error #241.

The code fails in both of the following statements (a DCount and a Docmd.RunSQL).

1:
....


strSearch = "[CustomerId] = " & Chr(34) & Me![cboCustomerId] & Chr(34) _
& " and [CustomerType] = " & Chr(34) & Me![cboCustomerType] & Chr(34) _
& " and [OrderDate] = " & "#" & Format(Me![cboOrderDate], "MM\/DD\/YYYY") & "#" _
& " and [OrderNumber] = " & Me![cboOrderNumber]

intCnt = DCount("[CustomerId]", "tblCertificate", strSearch)
If intCnt = 0 Then
.....

2:
.....
strSQL = "Insert Into tblCertificate (CertificateNbr,CustomerId,CustomerType,OrderDate, OrderNumber," _
& "PONumber,Method) Values(" & intNextCertificateNbr & ",'" & Me.CustomerId & "','" & Me.CustomerType & "'," _
& "#" & Format(Me![OrderDate], "MM\/DD\/YYYY") & "#" & "," & Me.OrderNumber & "," & Me.PONumber & ",'" & strMethod & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
...

I know that SQL Server uses a different syntax for its dates ( # vs ' and " vs ' ). I wonder why the the application works on the Access 2002/2003 client side for both an Access function and a RunSql command but fails when the Access 2007/2007 runtime client is used. Could it be the ODBC driver that is being used?

Any ideas?

thanks..