ADODB connection creates error; using Microsoft.Jet.OLEDB.4.0 as data provider

[rt]

Hello,
I created an Access 2000-formatted MDB using Access 2003. It uses a split
front-end/back-end. I created a form which attempts to connect to the
back-end using an ADODB connection. My pc uses XP Pro and Office 2003 (I
also have Office 97 installed). Some client machines will only have Office
2000 installed, which is why I am using Access 2000 format for the MDB,
instead of Access 2003.

When testing on an Office 2000 machine, I get this error:
"Run-time error '3706':
Provider cannot be found. It may not be properly installed."

This error occurs on any machine with Office 2000 installed. They also have
Office 97 installed for some legacy Access 97 applications, and I cannot
install Office 2003 on them.

Here is the offending code:
***********START CODE************
Public Function fOpenVTSconnection()
'Creates a connection to the VTS back-end MDB.
Set cnnVTS = New ADODB.Connection
With cnnVTS
.Provider = "Microsoft.Access.OLEDB.10.0"
'CODE BREAKS ON NEXT LINE.......
.Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = "server name\path name here" ' (the
actual server name is substituted here...)
.Open
End With
End Function
************END CODE************

Other ADODB.Connections I've set up using ".Properties("Data Provider") =
"SQLOLEDB"" to a SQL Server, or using a Microsoft dBase driver with an
ADODB.Connection, work without errors.

The project's references are:
"Visual Basic For Applications"
"Microsoft Access 11.0 Object Library"
"OLE Automation"
"Microsoft DAO 3.6 Object Library"
"Microsoft ActiveX Data Objects 2.1 Library"

My understanding is that .Properties("Data Provider").Value =
"Microsoft.Jet.OLEDB.4.0" should work since it was installed with MDAC 2.6
on all of the computers in our department. I tried re-installing MDAC 2.6,
but nothing changed.

How can I track down the source of this error, and correct it?

Thank you for any assistance with this issue.

 

[Gerald Stanley]

I do not fully understand the need to create the connection
object from scratch. If the backend tables are linked
tables in the front-end, then you can set your ADODB
connection to CurrentProject.Connection. Even if they are
not linked, you should be able to set your connection
object to CurrentProject.Connection then just change the
Data Source property (as in your current code).

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Hello,
I created an Access 2000-formatted MDB using Access 2003. It uses a split
front-end/back-end. I created a form which attempts to connect to the
back-end using an ADODB connection. My pc uses XP Pro and Office 2003 (I
also have Office 97 installed). Some client machines will only have Office
2000 installed, which is why I am using Access 2000 format for the MDB,
instead of Access 2003.

When testing on an Office 2000 machine, I get this error:
"Run-time error '3706':
Provider cannot be found. It may not be properly installed."

This error occurs on any machine with Office 2000 installed. They also have
Office 97 installed for some legacy Access 97 applications, and I cannot
install Office 2003 on them.

Here is the offending code:
***********START CODE************
Public Function fOpenVTSconnection()
'Creates a connection to the VTS back-end MDB.
Set cnnVTS = New ADODB.Connection
With cnnVTS
.Provider = "Microsoft.Access.OLEDB.10.0"
'CODE BREAKS ON NEXT LINE.......
.Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = "server name\path name here" ' (the
actual server name is substituted here...)
.Open
End With
End Function
************END CODE************

Other ADODB.Connections I've set up using

".Properties("Data Provider") =

 

[Speedyman]

Thanks for the reply.
The tables in the back-end are not linked, but I did not realize that the
front-end would retain a connection to the back-end, even if there are no
linked tables. I will try your suggestion and see if that works.
Thanks again.