Updating MS - Access fields through MS-Excel cells

Question

Consider that I have an Excel workbook and an Access table not necessarily having a similar structure (i.e. they may not have same number of columns).

When I open the workbook the rows in the Excel sheet get populated by the rows in Access table (copied from the Access table into the Excel sheet's particular range of cells specified using macros).

Then I modify certain cells in the Excel sheet.

I also have a button called "Save" in the Excel sheet. When pressed, this will execute a macro.

My question: how can I update the Access table to reflect the changes in the Excel sheet when the "Save" button is clicked?

Answer 1

You can use ADO and some code.

Here are some notes.

Let us say you get some data like so:

Sub GetMDB()
Dim cn As Object
Dim rs As Object

strFile = "C:\Docs\DBFrom.mdb"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM Table1"
rs.Open strSQL, cn

With Worksheets(7)
    For i = 0 To rs.Fields.Count - 1
        .Cells(1, i + 1) = rs.Fields(i).Name
    Next

    rs.MoveFirst
    .Cells(2, 1).CopyFromRecordset rs
End With
End Sub

You could update the data using ADO like so:

Sub UpdateMDB()
Dim cn As Object
Dim rs As Object

''It wuld probably be better to use the proper name, but this is
''convenient for notes
strFile = Workbooks(1).FullName

''Note HDR=Yes, so you can use the names in the first row of the set
''to refer to columns
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

''Selecting the cell that are different
strSQL = "SELECT * FROM [Sheet7$] s " _
    & "INNER JOIN [;Database=c:\Docs\DBFrom.mdb;].Table1 t " _
    & "ON s.id=t.id " _
    & "WHERE s.Field1<>t.Field1"

rs.Open strSQL, cn, 1, 3 ''adOpenKeyset, adLockOptimistic

''Just to see
''If Not rs.EOF Then MsgBox rs.GetString

''Editing one by one (slow)
rs.MoveFirst
Do While Not rs.EOF
    rs.Fields("t.Field1") = rs.Fields("s.Field1")
    rs.Update
    rs.MoveNext
Loop

''Batch update (faster)
strSQL = "UPDATE [;Database=c:\Docs\DBFrom.mdb;].Table1 t " _
    & "INNER JOIN [Sheet7$] s " _
    & "ON s.id=t.id " _
    & "SET t.Field1=s.Field1 " _
    & "WHERE s.Field1<>t.Field1 "

cn.Execute strSQL

End Sub