Hi all.

I've run into an ADO issue, here's what's happening:


I'm using an Excel file on a shared drive to automate an Access database in
the background.

At some point in the code I'm using ADO to export an Access dataset.

The problem occurs when the code attempts to open an ADO connection.

Please note that this is most likely a compatibility issue; I get no errors
when I run the code from my PC.

** The error occured when another user tried to execute the code. **


Here's the VBA in question:

Dim strCon As String
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection

strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & strAppAccFullName & ";"

con.Open ConnectionString:=strCon

The strAppAccFullName string variable is used for the full path name to the
Access database on the shared drive; its value is:

"\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb"


The error message I'm getting is:

Run-time error '-2147220999 (800401f9):

Method 'Open' of object '_Connection' failed


As for the settings etc.:

The Excel file is using the following references:

- Visual Basic for Applications
- Microsoft Excel 11.0 Object Library
- OLE Automation
- Microsoft Office 11.0 Object Library
- Microsoft Forms 2.0 Object Library
- Microsoft Access 11.0 Object Library
- Microsoft DAO 3.6 Object Library
- Microsoft ActiveX Data Objects 2.8 Library

On my PC (where the code runs fine), I'm running Office 2003 & Windows XP.
My MDAC version is 2.81.

On the PC where the error occurred, the user is running Office 2002 &
Windows XP.
The MDAC version on that PC must be 2.8xx, because I tried to install MDAC
2.8 from Microsoft and the installation failed (I was told the component is a
part of Windows).


The objective is to get the code to run without errors on the other user's
PC (Office 2002).


Any insights / suggestions would be greatly appreciated.

Many thanks,
Alex

Does the user account that runs your code has read & write permission to the
network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to
Jet database, the user must have read/write permission to the folder where
*.mdb is in.

Also, you do not need to set reference to DAO3.6, since you are using ADO.
That is, either use ADO, or use DAO, not both.


"alexhatzisavas" wrote in message
...

Hi all.

I've run into an ADO issue, here's what's happening:


I'm using an Excel file on a shared drive to automate an Access database
in
the background.

At some point in the code I'm using ADO to export an Access dataset.

The problem occurs when the code attempts to open an ADO connection.

Please note that this is most likely a compatibility issue; I get no
errors
when I run the code from my PC.

** The error occured when another user tried to execute the code. **


Here's the VBA in question:

Dim strCon As String
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection

strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & strAppAccFullName & ";"

con.Open ConnectionString:=strCon

The strAppAccFullName string variable is used for the full path name to
the
Access database on the shared drive; its value is:

"\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb"


The error message I'm getting is:

Run-time error '-2147220999 (800401f9):

Method 'Open' of object '_Connection' failed


As for the settings etc.:

The Excel file is using the following references:

- Visual Basic for Applications
- Microsoft Excel 11.0 Object Library
- OLE Automation
- Microsoft Office 11.0 Object Library
- Microsoft Forms 2.0 Object Library
- Microsoft Access 11.0 Object Library
- Microsoft DAO 3.6 Object Library
- Microsoft ActiveX Data Objects 2.8 Library

On my PC (where the code runs fine), I'm running Office 2003 & Windows
XP.
My MDAC version is 2.81.

On the PC where the error occurred, the user is running Office 2002 &
Windows XP.
The MDAC version on that PC must be 2.8xx, because I tried to install MDAC
2.8 from Microsoft and the installation failed (I was told the component
is a
part of Windows).


The objective is to get the code to run without errors on the other user's
PC (Office 2002).


Any insights / suggestions would be greatly appreciated.

Many thanks,
Alex