Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 computers - vba to extract data from access works on one but not the other

    I use VBA to extract data from an access database. I have 2 computers with identical builds. The following VBA code works on one but not the other.

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    ws.Cells(i, 43).CopyFromRecordset rs

    I have checked that both PC's have the same object library references added in, but my new second PC continually fails on the second line of the above code when running the same excel VBA macro.

    The error message is
    Run time error '-2147467259 (80004005)'
    Method 'CopyFromRecordset' of object 'range' failed

    Can someone please advise what the problem may be

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are you running exactly the same queries on the same data? CopyFromRecordset can be funny about Null values and errors.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi pergunas

    Has the data source been configured on both PCs????

    Check out this link
    http://www.stata.com/support/faqs/da...ring-odbc-win/

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by pergunas View Post
    I use VBA to extract data from an access database. I have 2 computers with identical builds. The following VBA code works on one but not the other.

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    ws.Cells(i, 43).CopyFromRecordset rs

    I have checked that both PC's have the same object library references added in, but my new second PC continually fails on the second line of the above code when running the same excel VBA macro.

    The error message is
    Run time error '-2147467259 (80004005)'
    Method 'CopyFromRecordset' of object 'range' failed

    Can someone please advise what the problem may be
    How is rs declared? Do you by any chance have a reference to ADO appearing in your references list above DAO? Try declaring rs as

    Dim rs as DAO.Recordset

    to make it explicit and see if that makes a difference.

Posting Permissions

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