vlookup V's Access
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vlookup V's Access

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I currently use vlookup to lookup products in a seperate woorkbook. Can I have this database of products in Access and still refer to the infomation from excel ?

    If I can, How do I do it ?

    Thanks.
    Mark Henry

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes! It does depend on what you want to do with the data, I use Database queries, check the help files for this function.
    "Have a good time......all the time"
    Ian Mac

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I don't think there's a way of doing this with built in formulas (I may be wrong). You could use this function which works in a similar way to VLOOKUP but for Access databases. You need to set a reference to the Microsoft ActiveX Data Object 2.x Library (from Tools, References in the VB editor).

    Code:
    Dim adoCN As ADODB.Connection
    Dim strSQL As String
    
    Public Function DBVLookUp(TableName As String, LookUpFieldName As String, LookupValue As String, ReturnField As String) As Variant
    Dim adoRS As ADODB.Recordset
    If adoCN Is Nothing Then SetUpConnection
    
    Set adoRS = New ADODB.Recordset
    strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & _
             "='" & LookupValue & "';"    ' If lookup value is a number then remove the two '
    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
    DBVLookUp = adoRS.Fields(ReturnField).Value
    adoRS.Close
    End Function
    
    Sub SetUpConnection()
    On Error GoTo ErrHandler
    
    Set adoCN = New Connection
    
    adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
    adoCN.ConnectionString = "K:financemanagement reportingfinance reporting 2002.mdb" 'Change to your DB path
    adoCN.Open
    Exit Sub
    
    ErrHandler:
    MsgBox Err.Description, vbExclamation, "An error occurred"
    
    End Sub
    Hopefully, the function should be straightforward to use. TableName is obviously the name of the table in the database, LookupFieldName is the name of the field whose values you are searching, LookupValue is the value in LookupfieldName you are looking for and ReturnField is the name of the field whose matching value you wish to return. If there are more than 2 entries matching LookupValue then it will only return the first one.

    HTH, any problems please repost,

    Regards,
    D

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes you can do it, as Ian Said, it's called ODBC linking (Open database Connectivity i think)....A very handy / powerful feature to pull from Access or mainframe or other databases. The only problem I've found is that it can be a bit tricky to set up the linking, but if you are familiar with access, it shouldn't be too bad. It's under: DATA:GET EXTERNAL DATA: CREATE NEW QUERY
    it walks you through, asking where the database and tables reside. One important step is where it asks you if you want to save the query......if you are going to use this more than once, save it to a name, then you can go through the DATA:GET EXTERNAL DATA: RUN EXTERNAL QUERY and the data will repopulate. Hope this helps

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have an MS-Word document that I created for a presentation that gives step by step instructions for this....I'll email it to you.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    From Access Use the link table(get external data or from new table button) option to create a link between Excel for each sheet.

    Then you'll have 2 tables in access changes in access will be referenced in Excel and vice versa.

    Then create a new query from the 2 tables, to keep it simple you can just drag a join between the 2 tables (in query). Right click the join line and choose the appropriate selection for you. Then put the fields in you want to include in the query.

    I do this all the time I have one table with stock listing and the other with order info, the query tells me how much stock is available for the orders.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com