Insert or Update data from Excel to Access

Question

I have 800 to 1000 Excel files, to upload to Access. These files have specific forms and 6-7 tables, which will be filled in by different managers at different times. The data in these files could be updated or changed once in a while.

I have two ideas:

The one I want most is to make a VBA macro in this Excel sheet, which would autorun on close and upload data from Excel to Access, when a manager fills in all the data. So I always have up to date data in Access. In this case I need to use ADO, because I don't know if any of managers have or don't have MS Access installed on their PC in order to use DAO.
The second one - I run the macro from Access, which reads all 800-1000 files, when they are ready. In this case I can use DAO as well as ADO. But I don't want to do the job by my self.

I have this test code for the first case.

Sub TestUpload()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\User\Desktop\TestDataBase.accdb;"
rs.Open "TestDB", cn, adOpenStatic, adLockOptimistic, adCmdTable

Dim contractSAP As String
contractSAP = Sheets("TestUpload").Range("A1").Value
Dim contractASU As String
contractASU = Sheets("TestUpload").Range("B1").Value

Dim strSqlSelect As String
Dim strSqlInsert As String
Dim strSqlUpdate As String

strSqlSelect = "SELECT 1 FROM TestDB WHERE ContractNumSAP = '" & contractSAP & "'"
strSqlInsert = "INSERT INTO TestDB (ContractNumSAP, ContractNumASU) VALUES ('" & contractSAP & "', '" & contractASU & "')"
strSqlUpdate = "UPDATE TestDB SET ContractNumASU = '" & contractASU & "' WHERE ContractNumSAP = '" & contractSAP & "'"

cn.Execute (strSqlInsert)

End Sub

and I have some questions.

1) Could there be a conflict if two or more managers upload the data to the same tables simultaneously? The records are differentiated by Primary Key.

2) What is the best way to check if there is already a record in the table in Access?

3) The best way to Upload or Insert?

4) Is there a way, to insert/update records from Excel to Access via ADO by the whole Range/Table, or do I need to check if the record exists for every row in the loop?

5) Is there a way to SELECT data from Excel sheet using SQL and insert/update them in the same SQL query not using Cell adressing one record by one, as I have now?

Olivier Jacot-Descombes Olivier Jacot-Descombes · Accepted Answer · 2017-07-22T18:02:00

Use the command DoCmd.TransferSpreadsheet in Access in order to link the excel worksheets as tables into access. Then you can query these tables just like regular access tables.

1) If you pull the files from within Access, you won't have conflicts with managers uploading simultaneously.

2) Each record needs a primary key which uniquely identifies it. This can be one column or a combination of columns. You can update and insert records like this (use queries, don't use loops):

First Update existing records (assuming ContractNumSAP is your PK, and you want to update ContractNumASU):

UPDATE
    MyAccessTable A
    INNER JOIN MyLinkedExcelTable X
        ON A.ContractNumSAP = X.ContractNumSAP
SET A.ContractNumASU = X.ContractNumASU

Then Insert missing ones:

INSERT INTO MyAccessTable (ContractNumSAP, ContractNumASU)
SELECT ContractNumSAP, ContractNumASU
FROM MyLinkedExcelTable X
WHERE X.ContractNumSAP NOT IN (SELECT ContractNumSAP FROM MyAccessTable)

Insert or Update data from Excel to Access

2 Answers