Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Connecting to DB and opening RecordSets (Access 2000 SR1)

    Hi there all.

    Scenario:
    I have a situation where I attach some tables from one mdb, get some information out of them, and delete the attached tables.

    I then repeat the process, except the only difference is that the name of the mdb is different.

    I have designed it in such a way that the list of tables (which is very much dynamic during development) that I have to attach sits in a table, so all I have to do is have the problem fetch the list of names of the tables that it has to delete or attach and do the deletion/attachment.
    (Sounds confusing/complicated doesn't it! You should then see what the rest of the program will be like!)

    Problem:
    I am able to do this in Access 97 without any problem, but I am struggling to make heads or tails of the Access 2000 way of connecting to the database and opening recordsets.

    I know that my SQL statement for retrieving the list of tablenames is correct (which I have as: "SELECT TableName FROM tbl_AttachedTableORDER BY TableName;"

    Is there anyone who has a code example to help me get started off? (I'm familiar with Access 2 and Access 97 and VBA in Word 2000)

    Thanks in advance!

    James

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting to DB and opening RecordSets (Access 2000 SR1)

    Hi

    I wonder if your problems with recordsets arise from using the right reference to DAO rather than ADO.

    Step 1. From the VBA window , In tools ---> references make sure you have ADO 3.6 checked
    Setp 2. Explicity declare your database and recordset variables as DAO

    ie.

    Dim dbConnect as DAO.Database
    Dim rstConnect as DAO.Recordset

    This may help?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting to DB and opening RecordSets (Access 2000 SR1)

    You can use the same code in 2000 as in 97 using DAO if you set the DAO 3.6 library reference. Creating links is easier using DAO than ADO, since it uses familiar objects, methods and properties instead of the objects, methods and properties exposed by the OLEDB provider. However, as Jayden suggests, you need to specify the object model in your declarations so that Access knows if you're dealing with a DAO.Recordset or an ADODB.Recordset, etc.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting to DB and opening RecordSets (Access 2000 SR1)

    This is definitely it!

    Set it all to DAO and it worked first time. can't believe it was something so stupidly simple!

    Thanks for the help!

    James

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting to DB and opening RecordSets (Access 2000 SR1)

    Thanks Charlotte...

    I'll definiely remember this for the future!

    James

Posting Permissions

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