Access 2007 fails to run with linked SQL Server backend (1 Viewer)

[Zeke]

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 running Access 2002 or 2003 on the client side and running Access 2002/2003 or either version of SQL Server Express backend 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 iis Access and the back end is Access but fails when SQL2005 or SQL 2009 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..

 

[Galaxiom]

I wonder if you are exceeding the maximum number of characters in the argument of the Domain Expression with certain data values concatenated like that in such a big argument. Can't remember if the limit is 255 or 1024.

I'm not sure that you should be escaping the forward slash inside the quotes of the date conversion. I have never included them but I haven't worked with that expression in older versions of Access.

Try changing "MM\/DD\/YYYY" to "MM/DD/YYYY"

BTW my preferred way of converting dates is:
Format({expression}, "\#DD/MM/YYYY\#")

I think it is much tidier than concatenating the #s.

Also note that you can use a full reference to a control (Forms!formname etc) instead of concatenating a Me!etc. This deals with the date format automatically and is much easer to read especailly if strings are involved. The only down side is it isn't portable to other forms then.

 

[Zeke]

Thanks for the reply.. I don't think the limit is the issue since Access 2007 runs fine when it is using an Access backend Db. The date conversion string was someone else's creation which seems to work (The back slashes are for recognizing the next character as a character). I must be missing something that would cause Access 2007 to fails when it changes from an Access backend to a SQL Server (which neither Access 2002/2003 fail when a SQl server backend is used)

 

[Galaxiom]

What you say makes sense. Does seem odd. One would expect 2007 to be more likely to work. My post was a late night stab in the dark.

Another stab in the dark. Perhaps there are missing references in the 2007 installs that are somehow required for SQL server. VBA Editor> Tools Menu > References.

 

[Zeke]

2 machines - one has a2002 installed and has A2007 and the other has A2002 and A2007 runtime. Both run when the front end is A2002 regardless of backend. When A2007 (both versions) are used for the front end they get the conversion error for the string to date when run against the SQLServer backend but run fine if run against an Access backend. Are there specific libraries for SQLServer? oris it controlled bythe ODBC. Seems there are at least 3 versions or moreof the ODBC (SQL Server, SQL Native Client, Sql Server Native Client 10.0...). Anyexperts on what the difference is between the ODBC's.. or required libs for Sql Server?

 

[SOS]

On WinXP:
SQL Server is the driver for SQL Server 2000
SQL Native Client is the driver for SQL Server 2005
SQL Native Client 10 is the driver for SQL Server 2008

On Vista/Win7:
SQL Server is the driver for SQL Server 2000/2005
SQL Native Client 10 is the driver for SQL Server 2008

Hope that helps.

 

[Zeke]

Thanks, Good to know.

 

[charlieOng]

Thanks, Good to know.

Hi,

I have quite similar problem, the FindFirst Function of Access 2007 and the NoMuch Function always return True when using back end SQL 2005. Some of other topics in the internet quoting the problem was with Double quote error in our syntax. Hope it helps.