error trying to reference Access from Excel

[neilsolaris]

Hi,

My aim is to reference an Access table from Excel. I found a Youtube clip that has code to achieve this, however, I get an error when I execute a line. I've posted what I have so far (see below). It's the very last line (Connection.Open ConnectionString = Connect) that causes the error message. I get run-time error, Automaton error, Unspecified error. I should add, I ticked to enable the Microsoft Activex Data Objects 2.0 Library in the references. Also, I'm using Excel and Access 2007.

Any ideas what I'm doing wrong? Many thanks for your help.

Sub GetDataFromAccess()

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

DBFullName = "D:\Documents\Orchestra\Musicians Details\ Orchestra.accdb"

Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString = Connect

End Sub

 

[Gasman]

Try ?

Connection.Open Connect

 

[neilsolaris]

Hi Gasman. Thanks for the suggestion. I just tried that now, but I get the same error.

 

[Gasman]

Hi Gasman. Thanks for the suggestion. I just tried that now, but I get the same error.

Well it appeared to work for me, in that it did not complain at all?, You have not defined ConnectionString?

What other references do you have?

 

[neilsolaris]

I'm not sure if I've defined ConnectionString or not! I'm not 100% sure what this code does to be honest. How do I define it? Do I need to go to references again? Otherwise, seeing as it worked for you, did you define it? I'm a little confused!

 

[Gasman]

Probably need OLE Automation?

I have that in my Excel references?

 

[Gasman]

I'm not sure if I've defined ConnectionString or not! I'm not 100% sure what this code does to be honest. How do I define it? Do I need to go to references again? Otherwise, seeing as it worked for you, did you define it? I'm a little confused!

I used your code, just replaced the file path.

 

[neilsolaris]

Thanks Gasman. I'm struggling to find OLE Automation. Do you have a full reference name for that?

Edit. I found it now. It was already ticked

Is it possible that I ticked too many references, and selecting the wrong one could cause it to fail? I could play around with it maybe.

 

[Gasman]

TBH, I have not played around with connection strings.
I just had a go as I use 2007 as well.

Have a look at this link and see if it elaborates more on what is needed.

How to Connect Excel to Access Database using VBA

To Connect Excel to Access Database through VBA, we can use ADO connection. These Access databases are easily connected to excel as data source.

www.exceltip.com

 

[neilsolaris]

Thanks Gasman, I'll have a read of that.

 

[Gasman]

That code in that link works for me.

My references are as below. I had to add the MADO 6.1 library.

HTH

 

[neilsolaris]

I have all your references, plus the following four.

Microsoft Forms 2.0 Object Library
Microsoft Outlook 12.0 Object Library
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Access database engine Object Library

I guess I need to keep the Outlook reference. But could the other three be superfluous?

 

[Gasman]

No, I'd leave well alone. I do not know enough about references to be playing with them.
Try the code in that link as it brought in the correct data for me as it stood.

 

[neilsolaris]

I entered the code in the link you sent me. It got stuck on the line Dim conn as New Connection. It brought up an error Compile Error: User-defined type not defined.

 

[Gasman]

Yes, you need to add the ActiveX library you mentioned in your first post?
I used the one with the highest version number?

 

[neilsolaris]

I opened up a new spreadsheet before, not realizing that it didn't contain all the references from before! So I entered the code to my present spreadsheet, and I got as far as conn.Open connString. I get a run-time error. It seems the same problem as I had originally.

 

[Gasman]

Are you using MADo 6.1? as that is what I am using.

What is the run time error?

 

[Gasman]

FWIW those variables are not defined correctly, but that is the least of your problems for now.

Suffice to say that code works as is, if you adjust for the correct DB .

 

[neilsolaris]

Yes, I'm using MADO 6.1 library now.

It's Run-time error '-2147467259 (800004005)':

 

[neilsolaris]

By the way, I changed the DBPath to the location and name of my database, but didn't change anything else. Should I have done?