CopyFromRecordset (Access 97 SR2)

Topic

New Reply

‘Morning All!

I am using CopyFromRecordset to populate Excel from Access. Unfortunately, there isn’t much documentation in Access regarding this.

1. Is there any way to include field names when copying the recordset?

2. Is there any way to copy the record horizontally?

TIA!

Cecilia 🙂

Reply | Quote

Replies

If you are trying to import Access data from within Excel, try this function.
In vbe, use the menu Tools, References to set a reference to Microsoft DAO 3.xx Object Library.

Function readAccess()
Dim wrkjet As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ws As Worksheet
Dim icols As Integer
Set ws = ActiveSheet
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkjet.OpenDatabase("d:datadataaccessnamen.mdb")
Set rs = db.OpenRecordset("Namen")
For icols = 0 To rs.Fields.Count - 1
    ws.Cells(1, icols + 1).Value = rs.Fields(icols).Name
Next
ws.Range(ws.Cells(1, 1), _
    ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs
End Function

If you want to export data from Access to Excel use the DoCmd.TransferSpreadsheet. CopyFromRecordset didn’t exist in Access.

Reply | Quote

That’s funny, because CopyFromRecordset is working just fine in Access.

I’m already going from Access to Excel, at this point I can’t turn back now.

In any event, your code had what I needed:

For icols = 0 To rs.Fields.Count – 1
ws.Cells(1, icols + 1).Value = rs.Fields(icols).Name
Next

I just changed the reference from ws to xlApp and it works fine.

Thanks!

Cecilia 🙂

Reply | Quote

Can you post the code you use ?

Reply | Quote

Of course! Here’s my code:

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
‘Open the New Template
Set xlBook = Excel.Application.Workbooks.Open(strExternalFile)
Set xlSheet = xlBook.Worksheets(“Access Data”)

‘Paste the Recordset
With xlSheet
For lngColumn = 1 To rst.Fields.Count
.Cells(1, lngColumn).Value = rst.Fields(lngColumn – 1).Name
Next lngColumn
.Range(“A2”).CopyFromRecordset rst
End With

Reply | Quote

Francois, Cecilia,

As you can gather from this, a reference has been set to the Microsoft Excel x.0 Object Library in Tools/References.

Thus CopyFromRecordset is recognized within Access as a method of a range in Excel.

Regards,
Hans

Reply | Quote

Hi Hans,

Yes, I did do that. I think the reference is Excel 8.0. Mostly, it works very nicely 😉 Every so often I get an automation error and have to shut down Access and start over. It seems that bits of Excel get stuck here & there, but I can live with that.

Thanks again, both of you!

Cecilia 🙂

Reply | Quote