Cannot Use ADODB.Connection to connect to an access database

**Cardinalbags** · 03-14-2012, 04:45 PM

I am attempting to pull data from multiple access databases and populate them into an excel worksheet.

The format of the code I am using is a snippet I found that goes like this....

Please Login or Register  to view this content.

My problem of course is that I need to reference the ActiveX data objects. This is where the issue begins. The corporate policy I am working to has the VBE->Tools->References options locked and cannot add the required references. I ran into this issue on access DB project a while back and ended up using windows API's for opening a second database. (Thanks to Ken Getz and Paul Litwin for their code!)

Is there is similar way of accessing the Access database from Excel using a windows API to open the table data?

**Firefly2012** · 03-14-2012, 05:25 PM

Hi

Are you unable to use Late Binding to create the connection/recordset? eg

Please Login or Register  to view this content.

**Cardinalbags** · 03-14-2012, 06:33 PM

Yes that worked fine. Thanks... Now I will finish revealing how much of a noob I am,

Now having another issue later on. In the original code:

For Each MyField in rs.Fields

MyField was dimmed as a Range but Is giving a type mismatch error. I did some reading and thought perhaps that it was a object item being confused between DAO and ADO, but there are no other DAO references in the spreadsheet. Again if the references put a higher priority on DAO recordsets, then perhaps that is the issue?

**Cardinalbags** · 03-14-2012, 08:39 PM

I have solved my previous issues:

This is my current as written code:

Please Login or Register  to view this content.

What is really odd now, is that the first time I open the spreadsheet and run the macro, it deletes all the old data just fine at the line noted. If I run the macro a second time after pulling in the new data, i get an error Delete method of Range class failed.

**Firefly2012** · 03-15-2012, 01:37 AM

If you want to remove all data from a sheet, then you can just clear the UsedRange eg:

Please Login or Register  to view this content.

**Cardinalbags** · 03-18-2012, 07:09 PM

Originally Posted by Firefly2012

If you want to remove all data from a sheet, then you can just clear the UsedRange eg:

Please Login or Register  to view this content.

I have data in the first so many rows that I cannot delete. I have to delete specific rows of data only. There are 1550 columns of data making calculations, that are then summarized. If if clear the sheet my summaru sheet will goto #REF! in about 500 places.

Anyhow I started a new post to discuss this issue.