Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CopyFromRecordset (Access 97 SR2)

    '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 :-)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    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.
    <pre>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</pre>

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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    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 :-)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    Can you post the code you use ?
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyFromRecordset (Access 97 SR2)

    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 :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •