work with ADO or DAO

[walker002]

Hi,

I would like to realize with Excel by DAO or ADO two things:
The available tables of Excel should be updated by the tables i
Access.
Best of all simply overwrite.

Per table:
Either an available line should be updated by changes in Excel i
Access, or a new line be added.

More should not happen, actually. I hope that there is for this an eas
and quick solution.
Unfortunately, I cannot handle yet with ADO or DAO.
What is better ?

Who could help me there?

Many thanks already beforehand
Walke

 

[Jean-Yves]

Hi Walker,

With ADO, You can test the following. Supposing you have Payroll in col A,
named myrange and Status in column B

'First make a reference via Tool/References .... To Microsoft ActiveX Data
Objects 2.x

Sub test()
Dim connMdb As ADODB.Connection
Dim recMdb As ADODB.Recordset
Dim strStatus As String

Set connMdb = New ADODB.Connection
Set recMdb = New ADODB.Recordset
"open a connection to the DB
connMdb.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & "Data Source = " &
ThisWorkbook.Path & "\myDB.mdb;"

For Each cl In Range("MyRange")
'open the recordset corresponding to the payroll
recMdb.Open "Select Payroll, Status from STAFF ;", connMdb,
adOpenForwardOnly, adLockOptimistic
If recMdb.EOF = False Then
strStatus = recMdb.Fields("Status").Value
If cl.Offset(0, 1) <> strStatus Then Debug.Print
recMdb.Fields("Payroll").Value & " " & recMdb.Fields("Status").Value
recMdb
Else : debug.print " No data for " & cl.value
End If
recMdb.Close
Next cl

connMdb.Close
Set recMdb = Nothing
Set connMdb = Nothing
End Sub

Regards

Jean-Yves