=> SQL Server vs Microsoft Jet Engine

  • Thread starter Thread starter Van T. Dinh
  • Start date Start date
V

Van T. Dinh

It sounds to me that you are using MDB (and NOT ADP) with
an SQL Server Back-End. In this case the
CurrentProject.Connection points to JET Engine (of the
Front-End) and NOT the SQL Server which you need. Also,
you should use the OLEDB Provider / Connection String for
MS-SQL Server rather than for ODBC Databases (ODBC is
considered obsolete by Microsoft). See:

<http://www.able-
consulting/MDAC/ADO/Connection/OLEDB_Providers.htm>

for suitable OLEDB Privider for MS-SQL Server
Note also that the "ODBC Connect Str" used in Pass-Through
Queries is different from the Connection String for OLEDB
Provider, even OLEDB Provider for ODBC Databases.

HTH
Van T. Dinh
MVP (Access)
 
Hello Van,

Thank you for your recommendation. I have now changed my
connection string to the below stated and it works well :)
__________________________________________________________
cnn.Open "Provider=sqloledb; Data Source=ZSQUIRREL;Initial
Catalog=TurnersMgmtSystem; Integrated Security=SSPI"
__________________________________________________________

For my Pass-Through queries is there a way to change
the connection string that may speed up the query,
or is it okay left as it?
__________________________________________________________
ODBC;DRIVER=SQL
Server;SERVER=ZSQUIRREL;DATABASE=TurnersMgmtSystem;Trusted_
Connection=Yes
__________________________________________________________

Thank you very much for your thoughts.

Kind Regards
Rhonda
 
For Pass-Through Queries, I use the ODBC Connect Str (I don't know of any
other way).

If you want to use OLEDB, you can use the Execute Method (of the Connection
Object) with the SQL String or you can use Views or Stored Procedure in
MS-SQL Server.
 
Back
Top