Check Policy Number in Access table and populate related data in Excelsheet using vba

Hi Guys

Please see attached the Workbook. I need to check the policy Numbers in Column A to be present in Access Table. If yes then write the corresponding ScanDate and BatchNo in columns I and J.

I need to write VBA code to perform it.

Any help would be much appreciated.

Thanks

Hi nathansav,My problem is different. Actually in the attached workbook, in sheet1 there are policy numbers present so we have to start from row 2 to the last used row and check if the policy number in column A of each row is present in Access table(Polno). If yes then in access table , there are fields scandate and batchno of each polno so the code should copy scandate and batchno in the corresponding cells in the excelsheet. There are actually sheet1 to sheet5 that contains data so the code should look for all the policy numbers in all the sheets and the update them with corresponding scandate and batchno.

Many Thanks

yeah, exactly this is what I have to do but can you help me in writing the code to get it done.. Any help would be much appreciated. Thanks

Nathansav, I have tried writing this piece of code to check each policy no in Access table and then populating Scandate and BatchNo in the corresponding rows in the sheet but it doesn't work and gives me Application defined error. I hope you can make the changes accordingly..

Private Sub Workbook_Open()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
 
    ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\Copy database.mdb;"
        
Set rs = New ADODB.Recordset
Dim ws As Worksheet

Dim i As Long
Dim j As Long
Dim k As String
j = 2
k = 2


'rs.Open strSql, cn

 Set ws = ThisWorkbook.Worksheets("Sheet2")
 i = ws.UsedRange.Rows.Count
 
 Do While j <= i
  strSql = "Select * from jabberwocky where PolicyNo ='" & ws.Cells(RowNo, j) & "'"
  rs.Open strSql, cn
  If Not rs.EOF Then
    Do
          ws.Range("I" & RowNo) = rs![Scandate]
          ws.Range("J" & RowNo) = rs![BatchNo]
    rs.MoveNext
    Loop Until rs.EOF
  End If
  j = j + 1
 Loop
End Sub

It comes after Do while statement. Before that the code works fine.

When I try to execute the code then error just pops up.. don't know where exactly is the error..

The error comes after If Not rs.EOF statement.

I changed the code as below but the error still appears:

Private Sub Workbook_Open()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
 
    ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\Copy database.mdb;"
        
Set rs = New ADODB.Recordset
Dim ws As Worksheet

Dim i As Long
Dim j As Long

Dim k As String
j = 2

 Set ws = ThisWorkbook.Worksheets("Sheet2")
 i = ws.UsedRange.Rows.Count
 
 
 Do While j <= i
 k = ws.Cells(j, "A")
  strSql = "Select * from jabberwocky where PolicyNo ='" & k & "'"
  rs.Open strSql, cn
  
  If Not rs.EOF Then
    Do
          ws.Range("I" & j) = rs![Scandate]
          ws.Range("J" & j) = rs![BatchNo]
    rs.MoveNext
    Loop Until rs.EOF
  End If
  j = j + 1
 Loop
End Sub