Pull data from Access to Excel

[FryGirl]

I realize many I'm might be behind the times here, but this query use to run, but not now.

I'm trying to pull some data from a query in an Access database, but get a debug on DAO.Database.

From some reading, DAO is ancient and should be using instead ADO.

I looked thru the References and clicked on Microsoft DAO 3.6 Object Library, but still get a error in loading DLL. I'm using 2016.

Sub RunAccessQuery()
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
        
    Set MyDatabase = DBEngine.OpenDatabase("C:\Users\fg\Desktop\New folder\ZalexCorp Restaurant Equipment and Supply.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("Revenue by Period")
   
    Set MyRecordset = MyQueryDef.OpenRecordset
   
    Sheets("Main").Select
    ActiveSheet.Range("A6:K10000").ClearContents
    
    ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
    For i = 1 To MyRecordset.Fields.Count
        ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
   
End Sub

 

[ranman256]

Instead of code,have you tried using CONNECTION to import access data?
(In the data tab)

 

[FryGirl]

No I have not tried using Connection before. Do you have a good tutorial you can point me to?

 

[xenou]

but still get a error in loading DLL.

What does this mean? When are you getting an error? What is the error?

Edit... hmmm nevermind I get the same error

 

[xenou]

Try instead of clicking on the reference to "browse" out to it.
You should be able to find it at:
C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao360.dll

(as far as I know DAO is still in use and maintained for Access development ... definitely not ancient. Microsoft tried to replace it with DAO but then changed their mind again)

Note that it is possible that there is a problem with 64 bit vs 32 bit going on here but I hope not.

 

[FryGirl]

Hi xenou,

My system is 64 bit. I did as you offered, that is, browsed for C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao360.dll and then selected Microsoft DAO 3.6 Object Library under Tools > References.

Upon selection OK, received the error that said, "Error in loading DLL".

When trying to run the code, the debug points to, Dim MyRecordset as DAO.Recordset with the Error, "Compile error: User-defined type not defined"
​

​

 

[xenou]

Do you install a new version of excel or get a new computer?

 

[xenou]

I'm not really sure about 64 bit DAO stuff. In this case you may want to just go ahead and use ADO (although now that I think about it I'm not really sure about 64 bit ADO stuff either - basically I cannot test anything since I'm not using 64 bit office products).

This would be an ado example:

Sub RunAccessQuery()
    
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim i As Integer
    
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "C:\myTemp\db5.accdb"
    End With
    
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "[Revenue by Period]"
    cmd.ActiveConnection = cn
    
    Set rs = cmd.Execute()
    
    With Sheets("Main")
        For i = 1 To rs.Fields.Count
            .Cells(6, i).Value = rs.Fields(i - 1).Name
        Next i
        .Range("A6:K10000").ClearContents
        .Range("A7").CopyFromRecordset rs
    End With
   
End Sub

Looks like the braces around the table name are needed if you have spaces in your query name. you'd want to use the highest ado library available - for me that was Microsoft ActiveX Data Objects 6.1 (which was a jump from 2.7 or 2.8 before that).

 

[FryGirl]

Thanks xenou. I'll give this a shot.

 

[FryGirl]

Hi xenou,

Sorry for the delay. Your offer in post #8 works great. Thanks.