Consulting

Results 1 to 13 of 13

Thread: Need to copy some records from a backup copy using Excel

  1. #1
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location

    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:

    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
    Semper in excretia sumus; solum profundum variat.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

  5. #5
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Thanks for that, I'll give it a go.

    Best regards

    Paul Ked
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Don't forget to take another backup before you start.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  8. #8
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    This should just require a simple query...

    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]

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Paul, a good result.
    Interesting SQL alternative Jonh.

  11. #11
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    That's interesting Jonh, as I said, SQL is new to me. Does that code go in an Access module?
    Semper in excretia sumus; solum profundum variat.

  12. #12
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    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..

  13. #13
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Brilliant, much faster too!

    Thank you.
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •