looking up values from excel in Access

enerks

New Member
Joined
Sep 20, 2006
Messages
13
I have a column with EAN Numbers in Excel and I need to lookup the matching titles in an access database. I have no clue how to do this since I hardly work with access.

The access db is called "NL_MU_021006.mdb" and the table is called "NL_MU_Productmaster". It has to do the lookup in EAN Code and put the results from columns "Local Title", "Artist" and "Distributer" in excel.

It sounds prety straight forward... ;)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
Link your Excel file to an Access table (go to the Tables object in Access, right click in the white space, then select Link and browse to your file).

Then, create a new query in Access where you set a relationship up between your Excel table you just linked to in Access and your "NL_MU_Productmaster" table, and return the fields you want.

You may want to check Access's help if you need help on setting up queries and realtionships.
 

enerks

New Member
Joined
Sep 20, 2006
Messages
13
It's a read only database so I can't make any changes to the DB.

I came up with the following code after a day strugling. I works for the first value in my excel list but then it stayss in the loop. I must be making a mistake somewhere.

Code:
Sub muziek()
Dim EAN As String, db As Database, rs As Recordset, Cel As Range

Set db = DBEngine.Workspaces(0).OpenDatabase("N:\INTSERV\Megacharts\audio\NL_MU_021006.mdb")
Set rs = db.openrecordset("NL_MU_Productmaster")

Range("c3").Select
Set Cel = ActiveCell
EAN = Cel.Value

Do Until IsEmpty(Cel)
       
    Do Until rs.EOF
       
        If rs("EAN Code") = EAN Then
        Cel.Offset(0, 1).Value = rs("Local Title")
        Cel.Offset(0, 2).Value = rs("Artist")
        Cel.Offset(0, 3).Value = rs("Distributor")
        Cel.Offset(1, 0).Select
        EAN = Cel.Value
        End If
        rs.MoveNext
        
    Loop
    
Cel.Offset(1, 0).Select

Loop

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Columns.AutoFit

End Sub

Can please someone have a look at it and make the changes so it works?

Thanks in advance!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
What you should do is use SQL for the recordset rather than the table.
Code:
Sub muziek()
Dim db As Database
Dim rs As Recordset
Dim Cel As Range
Dim strSQL As String
Dim EAN As String

Set db = DBEngine.Workspaces(0).OpenDatabase("N:\INTSERV\Megacharts\audio\NL_MU_021006.mdb")

Set Cel = Range("c3")

Do Until IsEmpty(Cel)
    strSQL = "SELECT * FROM NL_MU_Productmaster WHERE EAN='" & Cel.Value & "'"
    Set rs = db.openrecordset(strSQL)
    rs.MoveFirst
    Cel.Offset(0, 1).Value = rs("Local Title")
    Cel.Offset(0, 2).Value = rs("Artist")
    Cel.Offset(0, 3).Value = rs("Distributor")
    rs.Close
    Set Cel = Cel.Offset(1)
Loop
   

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Columns.AutoFit

End Sub
 

enerks

New Member
Joined
Sep 20, 2006
Messages
13

ADVERTISEMENT

Thanks Norie, that looks much better but it doesn't seem to work. It hangs on this part:

Code:
Set rs = db.openrecordset(strSQL)

Do you know what goes wrong? And also: Range C3 is not the complete range, is should run through 13k lines below C3 also... that's why I implemented 2 loops.

Thanks in advance for your help!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
No, I don't know what goes wrong.:)

Perhaps you could tell us what doesn't work?
 

enerks

New Member
Joined
Sep 20, 2006
Messages
13

ADVERTISEMENT

The error message I get is:

Code:
Run-time error '3075'

Syntax error (missing operator) in query expression 'EAN Code ='8711875906039".

The column it has to lookup the EAN is called EAN Code so I changed that in the code.

Thanks for you time!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
I think the problem is with the space in the field name.

Try enclosing it in [].
Code:
strSQL = "SELECT * FROM NL_MU_Productmaster WHERE [EAN Code]='" & Cel.Value & "'"
Note you should actually try and avoid field names with spaces, or any other 'unusual' characters eg #, $ etc

You can easily change the field's caption property if you need to display something other than the actual field name.
 

enerks

New Member
Joined
Sep 20, 2006
Messages
13
Hi Norie, it works as long as it finds an EAN code, when it doesn't find an EAN code I get an error telling me there is no current record. So If it the EAN code isn't available it should continue with the next ean to lookup. But I don't know how to do that. Any suggestions?
 

Plaguesd

New Member
Joined
Oct 20, 2006
Messages
1
you can add this line to the line BEFORE the SQL query
Code:
on error resume next
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top