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

 

[mrkntdrk]

I am still struggling with matching up my excel table to the blank fields in the access tables.

Does anyone have any ideas?

 

[acw]

Mark

What is the structure of the COL_2 column in each of the tables in the relevant databases? Number, Text??? And is the blank a null, space???


Tony

 

[mrkntdrk]

Tony,

It is in a Number format, (1 thru 99) or (Blank, Empty, no value).

Thanks for the help!!

 

[acw]

Mark

OK try

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)
  If IsEmpty(ce.Offset(0, 1)) Then
    Set rs1 = db1.OpenRecordset("select [access_table1].COL_3 from [access_table1] where [access_table1].col_1 = " & ce & " And [access_table1].col_2 is null")
    Set rs2 = db2.OpenRecordset("select [access_table2].COL_4 from [access_table2] where [access_table2].col_1 = " & ce & " And [access_table2].col_2 is null")
  Else
    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))
  End If

  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]

Works great!!!!

Thank you!!!!

I really appreciate your time.