Need to copy some records from a backup copy using Excel

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:

Code:

---

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

Paul, you can either just ignore the fields you do not want similar to 17, 20 & 23.
Or you can just name the fields that you want update providing Access recognises them OK.
I know this is a VBA forum but you should be able do this without VBA.
You can
1. Import the backup table
2. Run an Update Query to update the missing data.
3. If the update is successful delete the imported table.
I would import the table anyway to manipulate it with VBA as you can do all the work in Access.

Thanks for prompt reply.

The reason for wanting to do it in Excel is because I use it as a front end (there are only tables in the db) and as I am not sure how it happened I may have to use the routine remotely in future (the system is installed 50 miles away).

I could read the backup DB into Excel and then write it to the 'live' DB, but 1. I have a feeling this is where some of the data got lost and 2. I think this would be quite slow because of the looping.

I would like to do it with SQL commands from one to the other, but I don't know the syntax (I'm very new to database handling!)

Many thanks and best regards

Paul Ked

Well you could import the table in tot Excel to check it, which does not involve the loop.
You could then loop through the Excel Rows and transfer the data.
To update existing records requires different syntax to your current code, you have to use the .edit command.
The syntax takes this form
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
With rs
.Edit
!ShipDate = Me.Shipped_Date
!EquipmentDueBackdate = Me.Shipped_Date + 100
.Update
.Bookmark = .LastModified
End With
rs.MoveNext
Next count
rs.Close
Set rs = Nothing

Thanks for that, I'll give it a go.

Best regards

Paul Ked

Don't forget to take another backup before you start. :yes

Paul, I have had a thought, are you sure that the fields are actually missing.
Or is it possible that the Database has the ability to "Hide" the columns like Excel?

Hi

When I said 'lost' I meant the fields were empty. I can see them :)

I have written a sub that copies the backup tables to xl and then writes the relevant fields to the live db which is working fine.

Thanks for your help, I was out of my depth there and you dragged me up :hifive:

This should just require a simple query...

Code:

---

UPDATE [K:\KKDB.accdb].ActJobs a
INNER JOIN
        (SELECT JobNo,agent, invdate, dept from [Y:\KKDB-BU.accdb].[ActJobs]) b
ON a.JobNo = b.JobNo
SET
        a.Agent = b.[Agent],
        a.InvDate = b.[InvDate],
        a.Dept = b.[Dept]

---

Paul, a good result. :)
Interesting SQL alternative Jonh.

That's interesting Jonh, as I said, SQL is new to me. Does that code go in an Access module?

In Access SQL goes in a query.

You could put it any Access database Query and run it. You could use code to run the query, currentdb.execute "yourqueryname".

You can also run it outside of Access if you create a connection.


set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0;Jet OLEDB:" '<-- This changes depending on the version of Access
conn.connectionstring = "K:\KKDB.accdb"
conn.open
conn.execute yourSQL

You can run that from anywhere. Access, Word, Excel, put it in a text file with a .vbs extension, etc..

Brilliant, much faster too!

Thank you.