error trying to reference Access from Excel (1 Viewer)

neilsolaris

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
Try ?
Code:
Connection.Open Connect
 

neilsolaris

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
Hi Gasman. Thanks for the suggestion. I just tried that now, but I get the same error.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
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

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
Probably need OLE Automation?

I have that in my Excel references?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
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

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
That code in that link works for me.

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

HTH
1594908037165.png
 

neilsolaris

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
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

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
Yes, you need to add the ActiveX library you mentioned in your first post?
I used the one with the highest version number?
 

neilsolaris

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
Are you using MADo 6.1? as that is what I am using.

What is the run time error? :(
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:10
Joined
Sep 21, 2011
Messages
14,050
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

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
Yes, I'm using MADO 6.1 library now.

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

neilsolaris

Member
Local time
Today, 11:10
Joined
Apr 19, 2020
Messages
114
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?
 

Users who are viewing this thread

Top Bottom