Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10

    Unanswered: opening database

    I use a Access/sql - .adp database where I have used VBA-programming for a while, but never DAO.

    When I start with:

    Sub openARecordset()
    Dim db As Database
    Dim rec As Recordset

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("table1")
    rec.Close

    End Sub

    I get the following message already when I declare my database:

    Compile error: Cant find project or library

    Is something not set up rigth?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are using DAO syntax but either you don't have the DAO library referenced or you have ADO higher up the priority tree.

    fix:

    alt-F11 ¦ Tools ¦ References
    and scroll on down to Microsoft DAO 3.6 library - tick it

    and change:
    Dim db As Database
    Dim rec As Recordset

    to:
    Dim db As DAO.Database
    Dim rec As DAO.Recordset

    izy

  3. #3
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    Thanks! I had already checked for the first suggestion but the second seemed to work.

    Now I get an error on :
    Set rec = db.OpenRecordset("table1")

    Error:
    Runtime-error 91:
    Object variable or With Block variable not set.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    guess: try changing "db" to "dbxyz" and "rec" to "recxyz" throughout - maybe db is a reserved word in one of your libraries or is declared elsewhere and is causing problems.

    also - do you really have a table called "table1"?

    apart from that - it should work!

    izy

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Your error message states clear, that an object isn't set. When you get this error in the line

    SET rec=db.openrecordset()

    the only object, that could not had been set is db. Check whether
    db is Nothing

    If this is the case, check whether
    set db = currentdb()

    does anything. Maybe you replaced CurrentDB by an own function? Check whether
    CurrentDb is Nothing

    Or did you changed the order?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    I tried the dbzxy - still the same error.

    db = Nothing.

    Im not sure how I could have replaced CurrentDB, I haven't really used functions, only subprocedures.

    How can I change it?
    You ask if I changed the order. What do you mean by that?

    /RST

  7. #7
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    I tried that - still the same error-message.

    My table isn't called table1, I just changed it as an exaple.

    My debug-procedure shows that db=Nothing.


    /rst

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Okay, so
    set db=currentdb

    is actually the problem. Check whether
    CurrentDB is Nothing

    evaluates to TRUE.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    If I write ?CurrentDb in the immediate-window I get the same error-message: Variable not set.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You must write
    ?CurrentDB Is Nothing
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    The reply is True

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Okay, that's the problem. Your function CurrentDB does not return a reference to your current database.

    I've never seen this before; so I can't say anything about the cause.

    I would circumstance the problem, and replace CurrentDB() by DBEngine.OpenDatabase(<Full DB Path>)

    let me know, whether this helps.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    Ok. I haven't been here before, so you must excuse my lack of knowledge!

    Is that the link of the access-database or the sql-database where the data are located?
    On the sql-database there is a username and password, how do I include this in the link?

    Fx. The link of the access-database:

    Set db = DBEngine.OpenDatabase("G:\Folder1\Folder2\2001\dba sefolder\")

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Almost good:

    Set db = DBEngine.OpenDatabase("G:\Folder1\Folder2\2001\dba sefolder\<Your DB Name>.mdb")

    Before opening the database, however, you have to set the user name and password as properties of the DBEngine. See MSDN for details.

    Maybe this is also the reason, that CurrentDB didn't work. I've never worked with password secured databases.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Jul 2002
    Location
    Aarhus, Denmark
    Posts
    10
    Even if the password is not on the access-front end but on the sql-dbase?

    Rst

Posting Permissions

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