compare excel and access to update data

Question

Hi All,

I am using Remou trick shown here... to compare excel data with access table to update changes.

Is there a way to have excel to update any new data found using the same batch technique. Also can I incorporate it in the below mentioned code in any way.

Sub UpdateMDB()
Dim cn As Object
Dim rs As Object
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * FROM [Sheet1$] s " _
    & "INNER JOIN [;Database=D:\temp excel\T.mdb;].Table1 t " _
    & "ON s.id=t.id " _
    & "WHERE s.Field1<>t.Field1"
rs.Open strSQL, cn, 1, 3
strSQL = "UPDATE [;Database=D:\temp excel\T.mdb;].Table1 t " _
    & "INNER JOIN [Sheet1$] s " _
    & "ON s.id=t.id " _
    & "SET t.Field1=s.Field1 " _
    & "WHERE s.Field1<>t.Field1 "
cn.Execute strSQL
End Sub

Thank you all in advance and special thanks to Remou.

Answer 1

You can use a database ODBC comparison/sync tool such as Zidsoft CompareData to compare and sync the data (Excel to Access).

Create an ODBC data source for the Excel worksheet, create an ODBC data source for the Access table and then add a data comparison between the two. Microsoft Excel ODBC driver would allow you to insert rows into an Excel worksheet but not update/delete rows in the worksheet, but since you only want to update the Access database table that should not be an issue