work with ADO or DAO

  • Thread starter Thread starter walker002
  • Start date Start date
W

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
 
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
 
Back
Top