ADODB Connection Problem

[alpder]

I inherited some programs written by a previous employee. We have recently upgraded all our PCs from NT4 to XP SP2 and have upgraded Office97 to Office2003. The code connects an Excel spreadsheet to an Access database and extracts data from the mdb file
This program works perfectly on some PCs but not others.
Can anyone tell me where to start looking? I have checked MDAC and all PCs are on 2.8

Dim DB As ADODB.Connection
Set DB = New ADODB.Connection
Dim RS As ADODB.Recordset
Sheets("Calculation").Activate
sFile = "P:\cutcards.mdb"
DB.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & sFile

I would be very grateful for any assistance.

[K Dales]

The usual culprit is the connection string (that is the text in the DB.Open
line). Not sure why it would work on some machines and not others unless
there is some difference in the MSOffice setup; but to troubleshoot I would
suggest this:

From Excel start MSQuery (Data... Get External Data... New Database Query).
From the dialogs choose the MS Access database driver and specify the mdb
file. Once in MSQuery, build a simple query (with a small result set) and
return the data to Excel (see MSQuery help if you do not know how to do this).

Now you can see how Excel set up the connection. In the VBA immediate pane,
type this:

? ActiveSheet.QueryTables(1).Connection

The result should be the connection string that was created by MSQuery, and
assuming you were able to see your data in Excel this connection string is
guaranteed to work. See if it is different on the different machines and if
it is different from what you have in your code. You may get some insight
from that why it works on some machines and not others. If you need further
info to help resolve any discrepancies, I suggest you go to the MSAccess
support site an do a search on the term ADO Connection String.
--
- K Dales


"alpder" wrote:

>
> I inherited some programs written by a previous employee. We have
> recently upgraded all our PCs from NT4 to XP SP2 and have upgraded
> Office97 to Office2003. The code connects an Excel spreadsheet to an
> Access database and extracts data from the mdb file
> This program works perfectly on some PCs but not others.
> Can anyone tell me where to start looking? I have checked MDAC and all
> PCs are on 2.8
>
> -Dim DB As ADODB.Connection
> Set DB = New ADODB.Connection
> Dim RS As ADODB.Recordset
> Sheets("Calculation").Activate
> sFile = "P:\cutcards.mdb"
> DB.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & sFile-
>
> I would be very grateful for any assistance.
>
>
> --
> alpder
> ------------------------------------------------------------------------
> alpder's Profile: http://www.excelforum.com/member.php...o&userid=28547
> View this thread: http://www.excelforum.com/showthread...hreadid=482074
>
>

[Jean-Yves]

Hi,

did you check if there is no missing libraray. Ref to ADO 21. or 2.2 ...2.5

I use a slide different connection :

Dim conn As ADODB.Connection
Dim rec As ADODB.Recordset
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & sfile";"
rec.Open "SELECT * FROM MYTABLE ;", conn, adOpenForwardOnly, adLockReadOnly
Do While rec.EOF = False
.....
loop
rec.Close
conn.close
set rec = nothing
set conn = nothing

Regards
JY

"alpder" <[email protected]> wrote in
message news:[email protected]...
>
> I inherited some programs written by a previous employee. We have
> recently upgraded all our PCs from NT4 to XP SP2 and have upgraded
> Office97 to Office2003. The code connects an Excel spreadsheet to an
> Access database and extracts data from the mdb file
> This program works perfectly on some PCs but not others.
> Can anyone tell me where to start looking? I have checked MDAC and all
> PCs are on 2.8
>
> -Dim DB As ADODB.Connection
> Set DB = New ADODB.Connection
> Dim RS As ADODB.Recordset
> Sheets("Calculation").Activate
> sFile = "P:\cutcards.mdb"
> DB.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & sFile-
>
> I would be very grateful for any assistance.
>
>
> --
> alpder
> ------------------------------------------------------------------------
> alpder's Profile:
http://www.excelforum.com/member.php...o&userid=28547
> View this thread: http://www.excelforum.com/showthread...hreadid=482074
>

[Huh?]

Check and make sure that drive P is pointing to the correct location on the
machines where your code fails.

"alpder" wrote:

>
> I inherited some programs written by a previous employee. We have
> recently upgraded all our PCs from NT4 to XP SP2 and have upgraded
> Office97 to Office2003. The code connects an Excel spreadsheet to an
> Access database and extracts data from the mdb file
> This program works perfectly on some PCs but not others.
> Can anyone tell me where to start looking? I have checked MDAC and all
> PCs are on 2.8
>
> -Dim DB As ADODB.Connection
> Set DB = New ADODB.Connection
> Dim RS As ADODB.Recordset
> Sheets("Calculation").Activate
> sFile = "P:\cutcards.mdb"
> DB.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & sFile-
>
> I would be very grateful for any assistance.
>
>
> --
> alpder
> ------------------------------------------------------------------------
> alpder's Profile: http://www.excelforum.com/member.php...o&userid=28547
> View this thread: http://www.excelforum.com/showthread...hreadid=482074
>
>