Lookup Data from Access

[mrkntdrk]

I have an excel sheet with two Col of numbers. This data is changed on a daily basis (e.g. add new rows)

Col_1___Col_2
34356____5
34356____7
78974____6
24568___(Blank)
59764____1

I also have a two very large Access Databases with many rows and col of data. I would like to bring it in selectively per col_1 and col_2 to the excel sheet.

Database(A) = col_3
Database(B) = col_4

The results should look like this...

Col_1___Col_2__Col_3__Col_4
34356____5____John____ABC
34356____7____Tim____(blank)
78974____6___(blank)__FGH
24568__(blank)_Jack___XYZ
59764____1____Bob____wds

Thanks

 

[acw]

Mark

1) Is the item in column1 a unique key that is applicable to both the databases.

2) What are the names of the fields in the databases that contain the key and output information.


Tony

 

[mrkntdrk]

1) Yes... but it would need to be Col_1 and or Col_2

2) I'm not sure what you are asking for... Col_3=test2.mdb, Name and Col_4=test3.mdb, Code

 

[acw]

Mark

In test2.mdb, what is the table name and the column names that contain the unique key and the name and in test3.mdb, what is the table name and the column names that contain the unique key and the code???


Tony

 

[mrkntdrk]

Tony

Thanks for helping!

Lets say, test2.mdb has a table called “access_table1” and it has COL_0 as unique key 1,2,3,4,5,6,7,8 etc… and contains data that includes COL_1, COL_2 And COL_3.

Also lets say, test3.mdb has a table called “access_table2” and it has COL_0 as unique key 1,2,3,4,5,6,7,8 etc… and contains data that includes COL_1, COL_2 And COL_4.

It takes a combination of COL_1 and COL_2 to get me the data I want. COL_1 has duplicate values and COL_2 makes the subset unique.

Col_1___Col_2__Col_3__Col_4
34356____5____John____ABC
34356____7____Tim____(blank)
78974____6___(blank)__FGH
24568__(blank)_Jack___XYZ
59764__(blank)_Bob____wds
59764____1____Jim____wds
59764____2____Sam____wfs
59764____4____Tim____wxs
59764____6____Jo_____mjk



The excel sheet “Excel_file_1”, “Sheet_1” with two Col of numbers. COL_1 and COL_2. This data is changed manually on a daily basis (e.g. add new rows)

Col_1___Col_2
34356____5
34356____7
78974____6
24568___(Blank)
59764____1

Mark

 

[mrkntdrk]

I should also say I’m very new to Access, I wouldn’t know where to put the code if I had it.

Thanks again for your patience.

 

[acw]

Mark

This code goes into a general module on the excel spreadsheet. It assumes that the data col_1 and col_2 as shown above is in the range A1:B10.

You will need to put a number in col_2 where there are currently blanks. A zero will work. The code assumes the access databases are in C:\TEMP.

Sub aaa()
 Dim wrkjet As Workspace
 Dim db1, db2 As database
 Dim rs1, rs2 As Recordset

 Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
 Set db1 = wrkjet.OpenDatabase("c:\temp\test2.mdb")
 Set db2 = wrkjet.OpenDatabase("c:\temp\test3.mdb")
 For Each ce In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  Set rs1 = db1.OpenRecordset("select [access_table1].COL_3 from [access_table1] where [access_table1].col_1 = " & ce & " And [access_table1].col_2 = " & ce.Offset(0, 1))
  Set rs2 = db2.OpenRecordset("select [access_table2].COL_4 from [access_table2] where [access_table2].col_1 = " & ce & " And [access_table2].col_2 = " & ce.Offset(0, 1))

  rs1.MoveFirst
  ce.Offset(0, 2).Value = rs1.Fields("COL_3")
  rs2.MoveFirst
  ce.Offset(0, 3).Value = rs2.Fields("COL_4")
 Next ce


 db1.Close
 db2.Close
 wrkjet.Close
End Sub

Tony

 

[mrkntdrk]

Tony,

I’m getting a compile error.
“User-defined type not defined”

@ “Dim wrkjet As Workspace”

 

[acw]

Mark

Oops Sorry. When you are in the Macro Editor, select Tools, References, then scroll down until you find something like
Micorsoft DAO 3.6 Object Library. Select it and then try again.


Tony

 

[mrkntdrk]

Tony

Thank you!!!

The code works great as written.

But I have a small problem… The two very large databases that I don’t own and can't change does not contain the 0’s like you indicated. The fields are blank. Is this solvable?