none
Lookup value in another database from within Access 2013 RRS feed

  • Question

  • Hi, can anyone point me in the direction to do the following:

    I have an Access 2013 database (I will call this db1) and I want to look up a value in table in a different Access database (this can be db2). So basically from db1 I want to look up a value that is in a table in db2. I want to do this with VBA code. I cannot add a link to the table in db1 as I do not want db1 to contain a copy of or link to the table that is stored in db2.

    Thank you in advance for any help

    Monday, March 11, 2019 3:38 PM

Answers

  • You could use code like this:

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strResult As String
        strSQL = "SELECT AField FROM ATable In 'C:\Access\db2.accdb' WHERE ID=4000"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSQL)
        strResult = rst!AField
        MsgBox strResult
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing

    Adjust for your needs


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JustMeForNow Monday, March 11, 2019 6:06 PM
    Monday, March 11, 2019 4:28 PM

All replies

  • You could use code like this:

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strResult As String
        strSQL = "SELECT AField FROM ATable In 'C:\Access\db2.accdb' WHERE ID=4000"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSQL)
        strResult = rst!AField
        MsgBox strResult
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing

    Adjust for your needs


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JustMeForNow Monday, March 11, 2019 6:06 PM
    Monday, March 11, 2019 4:28 PM
  • I have an Access 2013 database (I will call this db1) and I want to look up a value in table in a different Access database (this can be db2).

    Hi JustMeForYou,

    You can make an SQL-string like:

        active_sql = "SELECT * FROM Table2 in 'C:\MyMap\..\..\db2.mdb' WHERE Field2 = '...' AND …

    and use this string in:

        Set cur_set = CurrentDb.OpenRecordset(active_sql)

    or

      extracted_value = CurrentDb.OpenRecordset(active_sql)!Field3

    Of course, substitute the real values for Table2, MyMap, db2.mdb, Field2, Field3.

    Imb.

    Monday, March 11, 2019 4:42 PM
  • Hans and I were thinking along the same lines.  The following is an example I was drafting while he was replying.  It allows a ContactID value to be entered in an input box, with the contact's name then returned in a message box:

        Dim rst As DAO.Recordset
        Dim lngID As Long
        Dim strSQL As String
        
        lngID = InputBox("Enter contact ID:", "FindContact")
        
        strSQL = "SELECT FirstName & "" "" & LastName AS Contact " & _
            "FROM Contacts IN ""C:\Users\kenws\Documents\Databases\Personal\ContactList_be.accdb"" " & _
            "WHERE ContactID = " & lngID
            
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        MsgBox rst.Fields("Contact")

    Ken Sheridan, Stafford, England

    Monday, March 11, 2019 4:47 PM
  • Thank you all for your prompt replies, I will try this out now. Just one additional thing, if db2 is password protected is this still possible.

    Thanks.

    Monday, March 11, 2019 5:09 PM
  • I have accepted the first answer - I never got chance to try any of the others, though I am sure they would also have been great solutions. I managed to modify the code and get it to work with a password protected version of my database.

    Thank you all for your solutions :)

    Monday, March 11, 2019 6:08 PM