Update one table with data from another access file's table?

[cjkoontz]

How could I modify the following code to update my customer table with let's say the Northwind customer table?

Would I need another connection object or just another recordset object?

Private Sub UpdateCustomer_Click()

Dim cnnJet As ADODB.Connection
Dim rst As ADODB.Recordset

'Open a connection using the Microsoft Jet provider.

Set cnnJet = New ADODB.Connection
Set rst = New ADODB.Recordset

cnnJet.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnJet.Open "C:\Microsoft Office 2000\Office\Samples\Northwind.mdb"

With rst
.Source = "SELECT * FROM Customers"
.ActiveConnection = cnnJet
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

Do Until rst.EOF
'...???
Loop
End Sub

 

[KyleS]

Hi,
You wouldn't need another connection object because the other table is in the dB that the code is in.

So your next recordset would look something like this:

Dim rstLocal as ADODB.Recordset

Set rstLocal = New ADOBD.Recordset

rstLocal.Open "SELECt * FROM Customers", ActiveProject.Connection

Give that a shot Kyle

 

[cjkoontz]

' Heres what I came-up with (generalized) for better or worse...

Private Sub UpdateCustomer_Click()
On Error GoTo Err_UpdateCustomer_Click

'*******************************************

Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset

Dim strConnect1 As String
Dim strSQL1 As String

Dim strConnect2 As String
Dim strSQL2 As String

'------------------------------------------
' "\..\" -- you fill it in
strConnect1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\..\NorthWind.mdb"
strSQL1 = "Select * FROM Customers"

Set rst1 = New ADODB.Recordset
rst1.Open strSQL1, strConnect1

'------------------------------------------

Set rst2 = New ADODB.Recordset

' "\..\" -- you fill it in
strConnect2 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\..\My Company Master.mdb"

Do Until rst1.BOF Or rst1.EOF

strSQL2 = "Select CustomerID FROM MyCustomers" & _
"WHERE CustomerID = " & rst1!CustomerID & "' "

rst2.Open strSQL2, strConnect2

If rst2.BOF Or rst2.EOF Then
rst2.Close
rst2.Open "MyCustomers", strConnect2, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rst2.AddNew
' Assuming all the fields are the same
rst2.Update
End If

rst2.Close
rst1.MoveNext

Loop

'------------------------------------------
If Err.Number = 0 Then
MsgBox "Customer Master Updated", vbokayonly, "Customer Master DB Synch success"
End If
'------------------------------------------

Set rst1 = Nothing
Set rst2 = Nothing
Set cnn1 = Nothing

'*******************************************

Exit_UpdateCustomer_Click:
Exit Sub

Err_UpdateCustomer_Click:
DisplayError Err.Number, Err.Description, "SynchInspections"
Resume Exit_UpdateCustomer_Click

End Sub

'*******************************************

Private Sub DisplayError(DENbr As Integer, DEDescription As String, DESubRoutine As String)

MsgBox DESubRoutine & ": " & DEDescription, vbOKOnly, "Error: " & DENbr

End Sub