Check Policy Number in Access table and populate related data in Excelsheet using vba (1 Viewer)

[aman]

Hi Guys

Please see attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.
Any help would be much appreciated.

Thanks
​

 

[tehNellie]

You could:

• Use linked tables to the worksheets and have a query per linked table to perform the update.
  
• Use linked tables, but rather than have 5 explicit queries build the SQL in code to perform the update.
  
• Import all the data, do the updates and spit it back out again.
  
• Write masses of code involving excel objects, recordsets, lots of debugging and shaking fists at the sky.

You might surmise that I favour one of the first two options, the third is an annoyance that I will contemplate if I have to.

Access queries are perfectly capable of updating/inserting information in linked tables, from that perspective they act exactly the same as "local" access tables.

In an ideal world I'd always recommend having an explicit named query in the database rather than building it, behind the scenes, in code.

This is one of those instances where, despite knowing I'll be told where to shove it, I normally ask the people who produce the spreadsheet whether they'd mind awfully giving me one worksheet rather than lots but I normally find that despite wanting the sky purple tomorrow they're generally very unwilling to help make that happen.

That said if there will always be 5 sheets, there is an initial overhead to having to do everything multiple times but once it's written you've got the flexibility moving forwards to handle the fact that although they said they'll always be the same that sheet3 is now different to all the rest.