locked
Lookup Value in Access Databse From Excel and return the Record RRS feed

  • Question

  • Hi All. I have a Form in Excel and a Table in Access. What I want to be able to do is that when a Button is pressed, get te value that has been enterend in my Textbox on my Form in Excel, lookup that value in my access database table, then return a field from my access table into excel then store it in a cell on my worksheet.

    My Access table has two Fields, USER_ID (Text) and USER_NAME (Text), The USER_ID will be entered on my excel from, and then it will be used to lookup in the acess database table to return USER_NAME, If it is not found the Display a Mesage box Error

    Thanks For Your help.

    Tom

    Wednesday, February 8, 2012 4:13 PM

Answers

  • In the Visual Basic Editor, set a reference in Tools | References to the Microsoft DAO 3.6 Object Library.

    Use code like this:

    Sub MyButton_Click()
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strUserID As String
        Dim strUserName As String
        Dim strSQL As String
        strUserID = ... ' get from form
        strSQL = "SELECT USER_NAME FROM MyTable WHERE USER_ID=" & _
            Chr(34) & strUserID & Chr(34)
        Set dbs = DBEngine.OpenDatabase("C:\Databases\MyDatabase.mdb")
        Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
        If rst.EOF Then
            MsgBox "User not found!", vbCritical
        Else
            strUserName = rst!USER_NAME
            Range("A2") = strUserName
        End If
        rst.Close
        Set rst = Nothing
        dbs.Close
        Set dbs = Nothing
    End Sub


    Substitute the path and name of the database and the name of the table, and complete the line that gets the UserID.

    Regards, Hans Vogelaar


    • Edited by Hans Vogelaar MVPMVP Wednesday, February 8, 2012 5:38 PM to correct error (dbFailOnError > dbOpenDynaset)
    • Marked as answer by t0m46 Wednesday, February 8, 2012 7:17 PM
    Wednesday, February 8, 2012 4:24 PM
  • Sorry, my bad - it was air code.

    Change it to

    Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset)


    Regards, Hans Vogelaar

    • Marked as answer by t0m46 Wednesday, February 8, 2012 7:17 PM
    Wednesday, February 8, 2012 5:36 PM
  • You open a recordset that only includes the field AMT:

    SELECT AMT FROM GIFTS WHERE GIVNO=...

    You can't refer to RS!CUSTNAME, since CUSTNAME is not included in RS. Perhaps you need

    SELECT AMT, CUSTNAME FROM GIFTS WHERE GIVNO=...


    Regards, Hans Vogelaar

    • Marked as answer by t0m46 Friday, February 10, 2012 12:25 PM
    Friday, February 10, 2012 12:07 PM

All replies

  • In the Visual Basic Editor, set a reference in Tools | References to the Microsoft DAO 3.6 Object Library.

    Use code like this:

    Sub MyButton_Click()
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strUserID As String
        Dim strUserName As String
        Dim strSQL As String
        strUserID = ... ' get from form
        strSQL = "SELECT USER_NAME FROM MyTable WHERE USER_ID=" & _
            Chr(34) & strUserID & Chr(34)
        Set dbs = DBEngine.OpenDatabase("C:\Databases\MyDatabase.mdb")
        Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
        If rst.EOF Then
            MsgBox "User not found!", vbCritical
        Else
            strUserName = rst!USER_NAME
            Range("A2") = strUserName
        End If
        rst.Close
        Set rst = Nothing
        dbs.Close
        Set dbs = Nothing
    End Sub


    Substitute the path and name of the database and the name of the table, and complete the line that gets the UserID.

    Regards, Hans Vogelaar


    • Edited by Hans Vogelaar MVPMVP Wednesday, February 8, 2012 5:38 PM to correct error (dbFailOnError > dbOpenDynaset)
    • Marked as answer by t0m46 Wednesday, February 8, 2012 7:17 PM
    Wednesday, February 8, 2012 4:24 PM
  • Here is a quick sample that I wrote which is untested. Please amend it as per your requirements. Also note that I am not using early binding with ADO. I am using latebinding and hence you will not have to set any references.

    Sub Sample()
        Dim cn As Object, rs As Object
        Dim DBFullName As String
        Dim TargetRange As Range
        
        '~~> Change database path here
        DBFullName = "C:\MyDatabase.mdb"
    
        On Error GoTo Whoa
    
        Application.ScreenUpdating = False
        
        '~~> Cell where you want to have the output
        Set TargetRange = Sheets("Sheet1").Range("A1")
    
        Set cn = CreateObject("ADODB.Connection")
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    
        Set rs = CreateObject("ADODB.Recordset")
        
        '~~> Replace TABLE1 with the name of the table
        '~~> Replace TextBox1 with the relevant textbox name
        rs.Open "SELECT * FROM TABLE1 WHERE USER_ID = '" & TextBox1.Text & "'", cn, , , adCmdText
    
        TargetRange.Value = rs!USER_NAME
    
    LetsContinue:
        Application.ScreenUpdating = True
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
        On Error GoTo 0
        Exit Sub
    Whoa:
        MsgBox Err.Description
        Resume LetsContinue
    End Sub
    EDIT: Beaten by Hans! :-D


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.


    Wednesday, February 8, 2012 4:34 PM
  • EDIT: Beaten by Hans! :-D

    But still very useful - I posted DAO code and you have ADO. More choice for the OP!

    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 4:43 PM
  • >>>More choice for the OP!

    Yup :)Though I am curious as to why did you choose DAO over ADO?


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Wednesday, February 8, 2012 4:51 PM
  • I am curious as to why did you choose DAO over ADO?

    I almost exclusively use DAO in Access itself...

    (DAO was the original object model in Access. With Access 2000, Microsoft tried to push developers to replace it with ADO, but from Access 2003 on, DAO has been the default object model again.)


    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 5:06 PM
  • >>>I almost exclusively use DAO in Access itself...

    I see.

    I am aware that DAO is native to Access but thought maybe there is some other reason why you use DAO from Excel :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Wednesday, February 8, 2012 5:11 PM
  • Hi Thanks for your code, When I go to add the Reference though, I get the following error...

    "Name Colicts with Existing Module, Project or , Object Libary"

    Also if I Try to run the code I get an Error on this line "Set RS = dbs.OpenRecordset(strSQL, dbFailOnError)"

    I have a Reference to the Office 12.0 Access Databse Engine Object as I use this in another Sub where I add Items to a table in My Database, Would this be the reason I am getting this error?

    Thanks

    Tom

    Wednesday, February 8, 2012 5:19 PM
  • If you already have a reference to the Microsoft Office 12.0 Access Database Engine Object Library, you don't have to set a reference to DAO. You should then be able to run the code that I posted.

    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 5:22 PM
  • I am Still Getting an Error on this line "Set RS = DB.OpenRecordset(strSQL, dbFailOnError)" The Error is "Invalid Argument"

    Any Reasons for this?

    Wednesday, February 8, 2012 5:26 PM
  • Sorry, my bad - it was air code.

    Change it to

    Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset)


    Regards, Hans Vogelaar

    • Marked as answer by t0m46 Wednesday, February 8, 2012 7:17 PM
    Wednesday, February 8, 2012 5:36 PM
  • Hi Thanks, I now have it working fine.

    Thanks again

    Tom

    Wednesday, February 8, 2012 7:17 PM
  • Hi, Is there A way to do this and bring back more than one filed? I tried to just add another Variable = RS!Field Name, but it came up with an error.

    Thanks

    Tom

    Thursday, February 9, 2012 5:57 PM
  • That should be possible, but if the field name contains spaces or punctuation, you must enclose it in square brackets:

        MyVariable = RS![Field Name]


    Regards, Hans Vogelaar

    Thursday, February 9, 2012 8:02 PM
  • Hi, I am Getting the Error "Item Not Found in this Collection" When I add the Line "NAME=RS!CUSTNAME"

    Has it got something to do with my Paramater (PARAM) where I open the record set? This is all of my code.

    Sub GET_GV_INFO()
     
    Application.ScreenUpdating = False
    
    CK = "NA"
    
    Dim DB As Database
    Dim RS As DAO.Recordset
    Dim PARAM As String
    
    PARAM = "SELECT AMT FROM GIFTS WHERE GIVNO=" & _
        Chr(34) & NUM & Chr(34)
        
    Set DB = DBEngine.OpenDatabase("C:\EPOS\EPOS-DB1-SALE.accdb")
    Set RS = DB.OpenRecordset(PARAM, dbOpenDynaset)
    
        If RS.EOF Then
        Else
            CK = "FOUND"
            NAME = RS!CUSTNAME
            AMT(CT) = RS!AMT
        End If
        
    RS.Close
    DB.Close
    
    End Sub
    Thanks

    Friday, February 10, 2012 10:27 AM
  • You open a recordset that only includes the field AMT:

    SELECT AMT FROM GIFTS WHERE GIVNO=...

    You can't refer to RS!CUSTNAME, since CUSTNAME is not included in RS. Perhaps you need

    SELECT AMT, CUSTNAME FROM GIFTS WHERE GIVNO=...


    Regards, Hans Vogelaar

    • Marked as answer by t0m46 Friday, February 10, 2012 12:25 PM
    Friday, February 10, 2012 12:07 PM
  • This is a great resource:

    http://www.rondebruin.nl/accessexcel.htm

    Friday, February 10, 2012 11:02 PM