looking up values from excel in Access

[enerks]

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...

 

[Joe4]

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]

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.

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]

What you should do is use SQL for the recordset rather than the table.

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]

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

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]

No, I don't know what goes wrong.

Perhaps you could tell us what doesn't work?

 

[enerks]

The error message I get is:

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]

I think the problem is with the space in the field name.

Try enclosing it in [].

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]

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]

you can add this line to the line BEFORE the SQL query

on error resume next