import excel file into access table

1
votes

I am creating an Access database and some help with VBA would be much appreciated.

Help is what I need to do,

I have an Excel worksheet containing data that needs to be added to the existing table in Access. Is there a way in VBA to add the data in excel row by row?

For every row in Excel I need to:

1st: check if the sample_number already exist in Access table, then do some action if it exist.

2nd: if the sample_number doesn't already exist then copy and paste this row from excel to Access table.

Right now I am stuck on how to process the excel spreadsheet row by row rather than importing the whole sheet at once.

Helps are much appreciated! Thanks

2
vbams-access
What have you tried so far, can you post your attempts and why not suitable. You can use transferspreadsheet to a new table, then use SQL to derive the subsets of data you require by using an inner join and a left join Use SQL here, and built in functions, would be my advice. Even skipping the import and using the Excel file in the SQL. – Nathan_Sav
Hi I have been trying to use 'loop up query' and 'aggregate function' to achieve my final goal, but so far it seems like query is not powerful enough for my need. – Terek Li

2 Answers

1
votes

Access is far more structured than excel so doing searches and data validation is easier to accomplish. To import from excel with vba while validating each record the best method I have found is to do a blanket import into a temp access table and then loop through the temp table validating the data and adding it to the target table as required instead of trying to query the data while still in an excel spread sheet. Hope this helps

Dim SQL As String
Dim rs As Recordset

SQL = "SELECT field1,field2 FROM tempTable"

Set rs = CurrentDb.OpenRecordset(SQL)
Do While Not rs.EOF

    'YOUR VALIDATION CODE HERE


    rs.MoveNext
Loop
0
votes

You can use transferspreadsheet to a new table, then use SQL to derive the subsets of data you require by using an inner join and a left join Use SQL here, and built in functions, would be my advice. Even skipping the import and using the Excel file in the SQL.

Something like this, where new table is the import and db orders would be the existing table. This handles the inserting. Changing the join to an INNER JOIN and removing the WHERE clause will give you a sub-set to apply logic to of the existing records.

I need to check the SQL fully with some dummy data, but this is a good start.

Use the DB to do these sort of jobs.

INSERT INTO [tbl_DB_ORDERS] ([ORDER_NO]) SELECT NT.[ORDER_NO] FROM [NEW_TABLE] as NT LEFT JOIN [tbl_DB_ORDERS] AS ORD ON NT.[ORDER_NO]=ORD.[ORDER_NO] WHERE ORD.[ORDER_NO] IS NULL