Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    New ADODB reference is debugging? (Access 2007)

    Hi,

    I am working through a course manual where they discuss using VBA to connect Access to Excel to export data from a table to a new Excel application instance. I have followed the exercise to the tee, but I get a problem with the ADODB line? The only thing I could think is a problem is that the manual is for 2003 where I am using 2007. Could anyone verify if this is the cause of the problem or have I missed setting a reference somewhere to identify ADODB?

    See attached picture...
    Attached Images Attached Images
    Regards,
    Rudi

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

    Re: New ADODB reference is debugging? (Access 2007)

    Have you set a reference in Tools | References... to the Microsoft ActiveX Data Objects 2.n Library? (I think the version for Access 2007 is 2.8 but I'm not sure of that).

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: New ADODB reference is debugging? (Access 2007)

    I was curious about that, and it appears that there are two versions - we have a client who has been using the 2.8 for about a year, but on my own system there is also a version 6.0. The latter may be courtesy of Vista.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New ADODB reference is debugging? (Access 2007)

    TX, that was the problem.

    I choose Microsoft ActiveX Data Objects 2.8 Library and it went through well.
    PS: There are many such versions of the library. Should one always take the latest one?

    Another thing:
    The macro is running fine now, but is there a way to return the recordset with the field names. The records are nicely dumped into the sheet, but any ideas how to modify the code to get field names, or does this require a different code approach altogether?

    TX
    Attached Images Attached Images
    Regards,
    Rudi

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

    Re: New ADODB reference is debugging? (Access 2007)

    The version of ADO to use depends on the database format.
    - For databases in Access 2000 format, one should use ADO version 2.1.
    - For databases in Access 2002/2003 format, one should use ADO version 2.5.
    - For databases in Access 2007 format (.accdb), I'm not sure.

    See the example in the help for CopyFromRecordset on how to add the field names. The help item for Excel 2003 can be found in CopyFromRecordset Method.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New ADODB reference is debugging? (Access 2007)

    tx a stack
    Regards,
    Rudi

Posting Permissions

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