Access database table was created from importing 6 Excel Workbooks with multiple tabs via a Visual Basic Script. All 6 Excel workbooks all had identical field structure. I wrote a VBA script to create the Access table. The initial ACCESS table contained 502 records and 38 columns (fields). The Access table still is 502 records, but has increased the number of fields to 60.
I need write a VB script to update the Access database table from the original 6 Excel Workbooks with multiple tabs only from fields that have been updated within the 6 Excel workbooks with multiple tabs (sheets). Each Workbook contains 3 or more tabs (sheets). Each Excel sheet contains 1 to many rows and the 38 columns (fields). My office personnel want to update the data in the Access database via Excel workbook and the personnel do not have Access database on their desktops. The “requirement number” field is unique both in Access and Excel workbook worksheet. However, the “requirement number” field is not in the same physical column location in Excel table and Access table.
Keep in mind the Access record contains 60 fields and the Excel Workbook Worksheet contains 38 fields (subset of the Access table).
I showed a physical example of changes in Excel and how is corresponds to Access table field changes as seperate image. Note: The 1st row is a header for both Excel and Access.
I am looking for visual basic coding examples that will meet my requirements.
Thank you.
I need write a VB script to update the Access database table from the original 6 Excel Workbooks with multiple tabs only from fields that have been updated within the 6 Excel workbooks with multiple tabs (sheets). Each Workbook contains 3 or more tabs (sheets). Each Excel sheet contains 1 to many rows and the 38 columns (fields). My office personnel want to update the data in the Access database via Excel workbook and the personnel do not have Access database on their desktops. The “requirement number” field is unique both in Access and Excel workbook worksheet. However, the “requirement number” field is not in the same physical column location in Excel table and Access table.
Keep in mind the Access record contains 60 fields and the Excel Workbook Worksheet contains 38 fields (subset of the Access table).
I showed a physical example of changes in Excel and how is corresponds to Access table field changes as seperate image. Note: The 1st row is a header for both Excel and Access.
I am looking for visual basic coding examples that will meet my requirements.
Thank you.
Attachments
-
Table.GIF
