Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connection Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connection Problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connection Problem
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connection Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Connection | Excel Worksheet Functions | |||
ADODB Connection String | Excel Programming | |||
share adodb connection in excel | Excel Programming | |||
Virus - importing excel via adodb connection | Excel Programming | |||
ADODB Connection to Access | Excel Programming |