Hi,
I started copying out some code from a Youtube clip I found, and have pasted the first part out below. For some reason it's not working for me, and I can't work out why. Here's the code.
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
It is the very last line that causes the problem. It brings up an error messsage Run-time error '-2147467259 (80004005)': Automation error, Unspecified error.
In case it's relevant, here is a list of all the references that I have ticked. Am I missing anything, or are there any superfluous references there?
Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Outlook 12.0 Object Library
Microsoft Access 12.0 Object Library
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects 6.1 Library
Microsoft Office 12.0 Access Database Engine Object Library
I've searched on Google for the problem. Could it relate to the fact that I'm using a 32 bit version of Microsoft Office. Does this code work for others?
In case you're wondering, the rest of the code is to grab data from an Access table, and enter it in an Excel workbook.
Many thanks for your help.