DB connection issue

Derek Brown
Derek Brown used Ask the Experts™
on
I'm trying to get 32 bit MSAccess 2002 .mdb to link to a 64 bit CADCAM application. They have their own Wrapper that I have installed but with the following code at cn.open strConnection I get error message Error message
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\RapidSpecManufacturing\RapidSpecManufacturingV22S.mdb"

    strSql = "SELECT * FROM qryGetDataForCADCAM;"

    cn.Open strConnection

    Set rs = cn.Execute(strSql)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
ADODB is a data lib and you must set a reference to it.  In the VBA editor, tools/references:

Region-Capture.jpg
 Then make sure you code compiles.  Note that you may have a different version of the lib installed depending on what's installed on your PC.

Jim.

Author

Commented:
I think I have tried all DLLs
Normally in my references I have to put DAO 3.6 above ADO 2.5 or it errors when debugging. But I tried all ADOs from 20 to 26 without success

I am trying this (it is supposed to be tidier)  but still have similar problem that the file will not connect.  I have one connection commented out so I can try both.
    Dim cn As ADODB.Connection, rec As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RapidSpecManufacturing\RapidSpecManufacturingV22S.mdb;"
    'cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\RapidSpecManufacturing\RapidSpecManufacturingV22S.mdb;"
    cn.Open

Is this part of the issue? Something has changed because I am not getting the original error warning message
Mark EdwardsChief Technology Officer

Commented:
When using DAO and ADODB together, you must be sure to add the name of the library in front of any common objects that the two have in common.  One of these is "Recordset"  id you just have:
Dim rs as Recordset

Open in new window

then rs becomes whatever the first reference is (DAO, or ADODB)
try this:
Dim rsDAO as DAO.Recordset
Dim rsADO as ADODB.Recordset

Open in new window

Then it doesn't matter what order the references are in.

Also, in your last post, what error are you getting when it "doesn't connect"
Mark EdwardsChief Technology Officer

Commented:

Author

Commented:
Thanks Marc.

I now have this:
Dim rs As Recordset
    Dim cn As ADODB.Connection, rec As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\RapidSpecManufacturing\RapidSpecManufacturingV22S.mdb;"
    cn.Open

error still on cn.Open Provider cannot be found

So I now have the Microsoft.ACE.OLEDB.12.0 specified as directed by the link you gave. But the link drives you round in circles saying here is a link to download, but it isn't any idea if I need to download it or how to get it?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Can't tell. But you must use JET 4 for an mdb file.
This works here right away:

    Dim cn As ADODB.Connection, rec As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder\File.mdb;"
    cn.Open

Open in new window

having a reference to "Microsoft ActiveX Data Objects 6.1 Library"

Author

Commented:
Thanks Gustav

Tried all combinations now. I'm just contacting the cad cam people to see if the issue is with them.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<error still on cn.Open Provider cannot be found>>

  it is not.  Access is clearly telling you that the provider you are giving for your connection string cannot be found.   That has nothing to do with the cad cam people.

 Go into tools/references in VBA and take a screenshot of the current reference you have set, and also the available Active X Data Objects libs that you have available.

Jim.

Author

Commented:
Hi Jim

References UsedAvailable
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That seems correct. It matches my setting that works.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Derek,

As Mark mentioned above, any time you have a reference set in your application to both DAO and ADO, then whenever you refer to objects that are contained in both, then you need to specify in the dim statement which type of object you are referring to, so replace:

Dim rs As Recordset

Open in new window

with :
Dim rs as ADODB.Recordset

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What program is actually attempting to connect to this DB...Access or Excel and what version are you working with.

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
and not only version, but whether it's 32 or 64 bit.

Jim.

Author

Commented:
Thanks Dale.

I have tried all combinations but to check should this work?: I tried it but still same error. Isn't that prefix already in the Dim cn Line

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection, rec As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RapidSpecManufacturing\RapidSpecManufacturingDataV30.mdb;"
    cn.Open

Author

Commented:
Hi Jim

The program is called AlphaCam and it is a 64 bit program. I will see if I can find out what version is being used

Author

Commented:
Visual Basic for Applications 7.1
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<The program is called AlphaCam and it is a 64 bit program.>>

 Ah, so this is not being done in an Office app.

 What you'll need to do is get the driver is download the Access database engine:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

 Install the 64 bit version.

Then in your connection string, use:

Provider=Microsoft.ACE.OLEDB.12.0;

for the provider.

Jim.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Derek,

I missed that you already had rec dimensioned appropriately.  What typ of recordset is rs?  I don't see any code for that, but it needs to be changed appropriately, either as:
Dim rs as DAO.Recordset

Open in new window

or
Dim rs as ADODB.Recordset

Open in new window

Personally, I would start with a new public procedure in a standard code module, not tied to a form, and just put the stuff in that procedure that you need to work with this one issue.  I wouldn't even worry about the recordset initially, I would start out with dimensioning the connection string, setting the connection string, attempting to open the connection.  I would add error handling, so that if an error occurs in the cn.Open command, it will jump to the error handler and give you the opportunity to look at the err properties (number and description) or loop through the errors collection with something like:
ProcError:
    For intLoop = 0 to errors.count-1
        debug.print intLoop, errors(intLoop).Number, errors(intLoop).Description
    Next

Open in new window

Once you know that the connection works, then you can worry about the recordset

Author

Commented:
Thanks Dale I will do that

Thanks Jim, I won't break anything else if I install this driver on my access 2002 pc?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Thanks Jim, I won't break anything else if I install this driver on my access 2002 pc?>>

 It should not, especially given that it's 64 bit.

 However from the reference list you posted, you have more than Access 2002 installed on this PC.  Your reference list shows Excel version 14, which is Office 2010.   If that's true, then it won't install.  Also note that I made a mistake earlier with the provider and you should try:

Provider=Microsoft.ACE.OLEDB.14.0;

  Before doing anything else.    Version 14 of the OLEDB provider came with 2010.   Version 12 is for 2007.

  If you do have Office 2010 installed and it's not 64 bit, then you'll be stuck.   You can't have 32 and 64 bit editions of office installed side by side.   But one thing at a time.   let's make sure we know what we have before we go further.

 <<They have their own Wrapper that I have installed >>

 Let me ask about this...do you have any docs on this wrapper and what it provides?  I'm wondering if they included a provider with that or if it is something else.

Jim.

Author

Commented:
I've asked for documentation for the wrapper. I won't have this until tomorrow

Author

Commented:
I found this in the wrapper:
ACamDBWrapperADODB
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
That's the Microsoft ActiveX Data Objects 6.1 lib, not the wrapper they provided.

Jim.
Mark EdwardsChief Technology Officer

Commented:
Try opening the immediate window and see what CurrentProject.Connection gives you.CurrentProject-Provider.PNGLink an Access table if you need to.
When in doubt, ASK THE MACHINE!

Author

Commented:
OK I will update tomorrow
John TsioumprisSoftware & Systems Engineer

Commented:
This is a product with quite some history...have you 1st checked if the .mdb in question is readable by modern Access Versions
Normally the way you are accessing it should be good but better to check if the Access .mdb is "good"
This how i use similar code
Dim strConn As String
Dim Conn as Object

    Set Conn = CreateObject("adodb.connection")
    strConn = "Provider=Microsoft.jet.oledb.4.0;Data source=" & SourceDatabase
    Conn.ConnectionString = strConn    
    Conn.Open

Open in new window

Author

Commented:
Had no documentation for the issue but a video showing what I need to do. Again will update asap

Author

Commented:
I have checked that the data is readable with my Access 16 version. All OK. Good idea to make absolutely sure though

Author

Commented:
Finally go connection. The code had missing lines referring to the dbWrapper. All sorted. Thanks for your input gentlemen.

      ' create connection
      Set dbWrapper = New AcamDBWrapper.ADODBWrapper
      dbWrapper.SetConnection 30, 30, CursorLocationEnum_adUseClient
      strStatus = dbWrapper.ConnectionOpen(strConnection)

I still have no idea what the 30,30 stands for unless it's referring to an array (that I don't need)

Author

Commented:
I may have cocked up the points again. If I choose a solution and mark everyone else as extremely helpful do the others get points. Been in EE for many years still don't get it!
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Just glad to hear you got it figured out.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial