J
José António Silva
If you work with ODBC linked tables to SQL Server, you should read this!
Certainly, you have to deal with date fields within WHERE clauses and also
have, for many other reasons, a VBA defined reference to ADOX, i.e.,
“Microsoft ADO Ext. 6.0 for DDL and Securityâ€.
The following seven easy steps should produce the SERIOUS error I’m trying
to describe.
1) Choose any SQL database that contains a table with a DATE field (very
common).
2) Create a new access database (2007, for instance), then create a linked
table to the chose SQL table with a DATE field. (you may use Windows ODBC
manager or use a DNS less connection).
3) Now, create an Access Form based on the linked table. You may use the
wizard.
4) In the form “on open†even type the following code: Me.RecordSource =
"SELECT YourSqlTable.* FROM YourSqlTable WHERE YourSqlTable.YourDateField <
#1/1/2009#"
5) Add a VBA reference to “Microsoft ADO Ext. 6.0 for DDL and Securityâ€.
Don’t worry about priority. We will not use ADOX here.
6) You have now to setup your Access Database to open the form you have just
created on startup. Test it! See if the form opens properly on startup and
then, compile the accdb creating an accde file.
7) Finally, move the accde to a machine where only Access 2007 run-time is
available (NOT the full Access version). Run the accde to open the form and …
It raises the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string. (#241)
If you simple remove the VBA reference to “Microsoft ADO Ext. 6.0 for DDL
and Security†the error disappears.
My workaround for this is to replace … < #1/1/2009# with < CDate (
“â€2009-01-01â€â€ ) (remember to use double Ҡinside the SQL string).
However, I frequently wonder if/when this workaround might fail, what will I
do?????
This comes, at least, from Access 2000. I’m looking forward seeing this
solved!
José António Silva
Certainly, you have to deal with date fields within WHERE clauses and also
have, for many other reasons, a VBA defined reference to ADOX, i.e.,
“Microsoft ADO Ext. 6.0 for DDL and Securityâ€.
The following seven easy steps should produce the SERIOUS error I’m trying
to describe.
1) Choose any SQL database that contains a table with a DATE field (very
common).
2) Create a new access database (2007, for instance), then create a linked
table to the chose SQL table with a DATE field. (you may use Windows ODBC
manager or use a DNS less connection).
3) Now, create an Access Form based on the linked table. You may use the
wizard.
4) In the form “on open†even type the following code: Me.RecordSource =
"SELECT YourSqlTable.* FROM YourSqlTable WHERE YourSqlTable.YourDateField <
#1/1/2009#"
5) Add a VBA reference to “Microsoft ADO Ext. 6.0 for DDL and Securityâ€.
Don’t worry about priority. We will not use ADOX here.
6) You have now to setup your Access Database to open the form you have just
created on startup. Test it! See if the form opens properly on startup and
then, compile the accdb creating an accde file.
7) Finally, move the accde to a machine where only Access 2007 run-time is
available (NOT the full Access version). Run the accde to open the form and …
It raises the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string. (#241)
If you simple remove the VBA reference to “Microsoft ADO Ext. 6.0 for DDL
and Security†the error disappears.
My workaround for this is to replace … < #1/1/2009# with < CDate (
“â€2009-01-01â€â€ ) (remember to use double Ҡinside the SQL string).
However, I frequently wonder if/when this workaround might fail, what will I
do?????
This comes, at least, from Access 2000. I’m looking forward seeing this
solved!
José António Silva