"Error loading DLL" when attempting to use ADO within an Access .mdb 1

[cravincreeks]

Hello all,

I'm attempting to create a VBA macro within a Microsoft Access .mdb file that is launched by clicking a button on a form. We'd like to use ADO because this database may one day be ported to a RDBMS - planning for the future. Anyhow, I'm attempting to open a recordset using the following code

Dim rsShpPath As New ADODB.RecordSet, strSQL As String
strSQL = "SELECT tAugust2004.CASENUM, tAugust2004.SHAPEPATH FROM tAugust2004;"
rsShpPath.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

But when my program enters the sub that contains those lines of code, the debugger highlights the 'CurrentProject.Connection' argument and a messagebox appears stating "Error in loading DLL"

I've tried setting a reference to the 'Microsoft ActiveX Data Objects 2.1' and 2.7. I have browsed to the location where these files are reported to be located and they are present (e.g. C:\Program Files\Common Files\System\ADO\msado21.tbl).

I've succesfully used ADO on the same machine while writing a VBA macro within another software package.

Any idea what the problem could be? I've searched the web and apparently others have encountered this problem, as similar posts can be found on several discussion forums. Unfortunately, nobody could offer a solution.

We do have the option of upgrading this version of MS Access 2002 to Access 2003...if that might help....

AZ

 

[dk87]

Maybe try defining a connection?

Dim cnConnection as New ADODB.Connection
Dim rsShpPath As New ADODB.RecordSet
Dim strConnection as String
Dim strSQL As String

strConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb" 'Note:this should point to your DB
strSQL = "SELECT tAugust2004.CASENUM, tAugust2004.SHAPEPATH FROM tAugust2004;"
cnConnection.Open strConnection
rsShpPath.Open strSQL, cnConnection

 

[crobg]

Also make sure that you have ADO checked in your references.

From VBE go to menu Tools-References...

 

[cravincreeks]

Thank you both for your response.

dk87, I tried the approach you suggested, but when the code attempts to open the connection, I get an error stating that the database is open and a the schema is locked (I'm trying to write a macro within the Access environment of the database my code intends to manipulate).

crobg, thanks for your response. As my original post said, I've tried setting ADO 2.7 and 2.1 in the references. I'm gonna look into upgrading to Access 2003 - it seems some sort of registry paths may have been corrupted, as suggested on other forums.

AZ

 

[dk87]

You could also try re-installing your original version of Access, MDAC 2.7, and JET

 

[cravincreeks]

I just tried that (re-installing MDAC 2.8) and it worked! I obtained MDAC here