Access to SQL VBA Code stopped working

[BuilderSpec]

Hi

I have some code in a form that writes to an Access table. this is it below.


Dim rs As New ADODB.Recordset
Dim sqlstr As String

sqlstr = "select * from Employees where EmployeeID =" & i

rs.Open sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then

rs.Fields("OnNow") = True
rs.Fields("TimeLoggedOn") = Now()
rs.Update
End If
rs.Close
Set rs = Nothing


It works perfectly I am not after suggestions of how it can be optimized etc .

I have now changed the backend Employees table to be on a SQL Server and it is linked here. I can see the data in the table ok.

When this code runs it fails on the rs.Update code with "ODBC Call failed."

yet if from the Immediate window I type :

Docmd.runsql "update Employees set "... blah blah this works perfectly.

The reason I don't want to re-engineer all the code is that there is a huge amount of code that manually updates records in this way and would be tedious to change them all to docmd.runsql's. In some cases this would not be appropriate for the business logic anyway.

Anybody any idea why this code would fail when the data is clearly available and updateable ?

Pls...








Hope this helps!

Regards

BuilderSpec

 

[vbajock]

You might try changing your open string to:

rs.Open sqlstr, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

If that doesn't work, my guess is that SQL is griping about the conversion of the Now string to a format it will recognize. I'd copy the successful update results you get doing it manually, then stop the code at the point it hits your Now() update and then see what happens if you pass the successful results value mentioned above as a hard code string. If the table accepts it without error, then create a string variable and stored the Now() value in it, the use the immediate window to see if there are any formatting differences, if not, store Now() to the string variable and pass SQL the string instead of Now().

Also, on some older versions of SQL Server, there was a bug where Access and SQL had a hard time negotiating True/False fields because Access does not have a "bit" data type. The fix was to change the SQL Server field type from bit to smallint, and to set the default value of the smallint field to 0. Since then I always convert any bit fields in tables I upsize from bit to smallint as a matter of habit, because it also makes check boxes behave better.

 

[vbajock]

Also, your variable "i" is not declared in the code. Is it a numerical or string value? Is the target field some sort of text type field ? If so, your problem could be missing quote marks.

 

[BuilderSpec]

Hi
Variable i is passed into the function.

Seems the issue is actually with the Now() bit as you identified.

Thanks

Graham

Hope this helps!

Regards

BuilderSpec