Hi there
I have lost a couple of columns of data from a table (not sure how his happened!) but all is not lost as I have a backup. But there have been a lot more records added and/or amended since the backup.
I use the following to update the table from Excel:
Sub UpDateAJ()
On Error GoTo Oops
Application.ScreenUpdating = False
Dim rst As ADODB.Recordset, x As Long
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM ActJobs WHERE JobNo=" & frmd.Cells(2, 19), _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\KKDB.accdb", adOpenDynamic, adLockOptimistic
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
If .Supports(adUpdate) Then
For x = 0 To 38
If x = 17 Or x = 20 Or x = 23 Then
If frmd.Cells(2, x + 3) <> "" Then
.Fields(x) = DateValue(frmd.Cells(2, x + 3))
End If
Else
.Fields(x) = frmd.Cells(2, x + 3)
End If
Next
.Update
.Close
End If
End If
End With
Set rst = Nothing
Xit:
Exit Sub
Oops:
MsgBox Err.Source & ": The following error occured: " & vbCr & Err.Description, , _
"The quote/job has not been updated, please try again." & vbCr & "Error has been logged."
Logger "Err: UpDateAJ", Err.Source, Err.Description
End Sub
How can I alter this code to copy 'Agent', 'InvDate' and 'Dept' records from 'ActJobs' table in Y:\KKDB-BU.accdb to 'ActJobs' table in K:\KKDB.accdb where the 'JobNo' fields match in both?
Thanks for any advice
Paul Ked