Excel data into an Access Table

[TCH]

*** THIS IS AREPOST AS I CAN’T FIND THE ORIGINAL POST ***<o></o>
<o> </o>
Hi. I’m anewbie to Access but seasoned in Excel and VBA. <o></o>
<o> </o>
Starting in row2 of an Excel worksheet I have 1 to 50 rows containing 7 columns of data thatif not found in an Access table of over 50,000 records must be transferred tothe Access table.<o></o>
<o> </o>
The columnarlayout of the Excel file and the Access table are identical.

Within Access fields 2, 3 and 7 are designated as “Primary” fields ifthat’s an issue.<o></o>
<o> </o>
I know how tofind a record based on one field within Access. But once a recordmatches criteria 1, how to compare the additional two fields of that record andcontinuing the testing to the EOF if not found has me stumped.<o></o>
<o> </o>
In summary, thequestion is how to loop through an Access table to find if a record existsmatching three fields of a seven field record (in Excel) then adding the newrecord into Access if the Excel row’sdata wasn’t found in the Access table. If a three-way match recordwas found, I'd like to overwrite the existing record entirely andthen advance to the next Excel row then repeat the matching for that row's"record" from the beginning of the Access table.

I'm using Access 2007 and Excel 2007.<o></o>
<o> </o>
Any insightwould be most appreciated. Thanks inadvance for your help. <o></o>

 

[BengalMagic]

Modify the Unmatched Query Wizard output?

 

[SydneyGeek]

Try this code as a starting point.

Sub PushToAccess_2()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim strPath As String
    Dim strAppend As String
    Dim strUpdate As String
    Dim Rw As Long
    
    strPath = ActiveWorkbook.Path & "\Database5.mdb"
    
    'create the connection to the database
    Set cnn = New ADODB.Connection
    MyConn = strPath

    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open MyConn
    End With
    
    Sheets("Sheet3").Activate
    On Error GoTo Err_Handle
    For Rw = 2 To Range("A" & Rows.Count).End(xlUp).Row
        strAppend = "INSERT INTO Orders (Client, OrderNo, Qty, State, Postcode, Price, Item )" _
                & "VALUES ('" & Cells(Rw, 1) & "'," & Cells(Rw, 2) & "," _
                & Cells(Rw, 3) & ",'" & Cells(Rw, 4) & "','" & Cells(Rw, 5) _
                & "'," & Cells(Rw, 6) & ",'" & Cells(Rw, 7) & "' )"
        cnn.Execute strAppend
    Next Rw
    
Err_Exit:
    cnn.Close
    Set cnn = Nothing
Exit Sub

Err_Handle:
    Select Case Err.Number
        Case -2147467259 'Primary key violation
            strUpdate = "UPDATE Orders SET Client = '" & Cells(Rw, 1) _
                    & "', State = '" & Cells(Rw, 4) & "', Postcode = '" _
                    & Cells(Rw, 5) & "', Price = " & Cells(Rw, 6) _
                    & " WHERE OrderNo&Qty&Item = '" & strKey & "'"
            cnn.Execute strUpdate
            Resume Next
        Case Else
            MsgBox Err.Number & "; " & Err.Description
            Resume Err_Exit
    End Select
    
End Sub

Note assumptions:
1. Database is called Database5.mdb and is in the same directory as the Excel file. If your file is an .accdb you will need to change the provider line to this:

       .Provider = "Microsoft.ACE.OLEDB.12.0"

2. You will need to set a reference to the Microsoft ActiveX Data Objects 2.8 Library
3. The database table is called Orders. The field datatypes are Text, Number, Number, Text, Text, Number, Text

I tested an insert/append of 160 records into 180,000 records. The process is quite slow; takes maybe a minute to run.

Denis

 

[TCH]

Deniis, Thanks so much for your straight-forward solution. I'll check out next Wend. when back at work. I can't access the database until then. Once again Thanks! and Happy New Year to you and yours. Ted H.