Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Unanswered: Insert Else Update on selected Excel records

    Hello Guys.

    Iam trying to do the following in a Excel working With VB and Access:

    On File Save do the following

    SELECT[Channel],[Group],[Budget Banner], PK[Sku], PK[A/NA] (Being the Column headers) FROM Tab1(being the excel worksheet)

    Loop trough all records If not exist Insert Else Update the table in Access

    Close and save

    And thats it but i dont know how to make it work i been trying many different things but with no success and iam not very good with VB. Help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Partly because I don't know how to do it in Excel and partly because it seems easier in Access, but I personally would link to the Excel spreadsheet from Access. Then write an Update query and an Append query and run the two queries.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I alway prefer to use VBA in Excel for this type of exercise. I also seems to run faster in Excel useing an ADO connection (but I don't know why).

    I assume that the Excel 'table' is the same (or very similar) to the Access table to be updated/inserted.

    If so then is is not too complicated (but this will depend on how familiar you are with ADO, recordsets and SQL ?).

    For instance does the 'table' contain the primary key(s) (or other unique combination)?

    If yes, then cycle down the rows returning/opening a recordset from access for each row filtered on the primary key(s), if it exists (rs.EOF=False) then 'rs.Close' and got to the next row, if not then 'rs.AddNew' and assign the column values for the row to the corresponding recordset fields, 'rs.Update' and 'rs.Close', then got to next row.


    HTH. If that sound reasonable to you, and there are any specific question, then please ask.


    MTB

  4. #4
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Thank you very much for replying. To answer that question is yes the worksheet clolumns are exactly the same as in the Access table has to how to go about it is a different thing i have never done recordset. if posible could you give a small example so that i can build on.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This is straight of the top of my head air code, so will be full of bugs/anomalies.

    Code:
    Sub UpdateData()
        Dim ConString As String
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim iRow As Long
        Dim iCol As Integer
        Dim SQL As String
        Dim j As Integer
        
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        
        ConString = "DRIVER=Microsoft ACCESS Driver (*.mdb);" & _
                    "DBQ=YourDatabaseName.mdb" & ";" & _
                    "DefaultDir=YourDatabasePath;"
             
        cn.Open ConString
        
        For iRow = 2 To YourLastRow ' ASSUMING ROW 1 IS FIELD NAMES
            SQL = "SELECT Column1Field, Column2Field, ... ColumnNField FROM YourTable " & _
                    "WHERE PKFieldName = " & Cells(iRow, PKFieldColumn) 'ASSUMING NUMBER FIELD
                    
            rs.Open SQL, cn, adOpenStatic, adLockOptimistic
            
            If rs.BOF And rs.EOF Then ' No records found
                rs.AddNew
                'ASSUMING SQL FIELDS ARE IN THE SAME ORDER AS COLUMN IN THE SPREADSHEET (AND START IN COLUMN 1)
                For j = 0 To rs.Fields.Count - 1
                    rs(0) = Cells(iRow, j + 1)
                Next i
                rs.Update
            End If
            rs.Close
        Next iRow
            
        cn.Close
        
        Set rs = Nothing
        Set cn = Nothing
        
    End Sub
    If the recordset fields are not in the same order, then you will need to refer to the to them by name instead of using the loop ie.

    Code:
       rs.AddNew
        
        rs("Field1Name") = Cells(iRow, File1Column)
        rs("Field2Name") = Cells(iRow, File2Column)
        .
        .
        rs("FieldNName") = Cells(iRow, FileNColumn)
        
        rs.Update
    You will need a set a reference to Microsoft ActiveX Data Object

    This code also uses a DSNLess connection string, but it can be easier to use a DSN (if you know how?).

    Hope this is of some use, but it is, perhaps, a steep learning curve!?



    MTB

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    I actualy got my code to work but i would like to add sorta a counter to it because i never know how many rows there will be so something like if my curewnt row and in a scoecific cell is = 0 then stop my for loop and also i would whant it to execute on file close only. here is the code thanx to mike the bike.

    Sub UpData()
    Dim ConString As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim iRow As Integer
    Dim iCol As Long
    Dim SQL As String
    Dim j As Integer
    Dim Counter As Integer



    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ConString = "DRIVER=Microsoft ACCESS Driver (*.mdb);" & _
    "DBQ=BudgetF2008-Database.mdb" & ";" & _
    "DefaultDir=S:\Budget\Budget\Administration\Bu dget Database;"

    cn.Open ConString


    For iRow = 2 To 500 ' ASSUMING ROW 1 IS FIELD NAMES

    SQL = "SELECT * FROM MATT_TEST_Q " & _
    "WHERE [SKU] = " & Cells(iRow, 11) & " AND [BBD ID] = " & Cells(iRow, 5) & " AND [P&L Code] = " & Cells(iRow, 8)

    rs.Open SQL, cn, adOpenStatic, adLockOptimistic


    If rs.BOF And rs.EOF Then
    rs.AddNew

    rs("Channel") = Cells(iRow, 1)
    rs("Group") = Cells(iRow, 2)
    rs("Budget Banner") = Cells(iRow, 3)
    rs("Budget Banner Division") = Cells(iRow, 4)
    rs("BBD ID") = Cells(iRow, 5)
    rs("Division") = Cells(iRow, 6)
    rs("Budget Channel") = Cells(iRow, 7)
    rs("P&L Code") = Cells(iRow, 8)
    rs("Budget Product Group") = Cells(iRow, 9)
    rs("BPG ID") = Cells(iRow, 10)
    rs("SKU") = Cells(iRow, 11)
    rs("Description") = Cells(iRow, 12)
    rs("Activity Code") = Cells(iRow, 13)
    rs("Product Type") = Cells(iRow, 14)
    rs("Package Size") = Cells(iRow, 15)
    rs("Brand") = Cells(iRow, 16)
    rs("LYVolumes") = Cells(iRow, 17)
    rs("LYReturns") = Cells(iRow, 18)
    rs("LY NISales") = Cells(iRow, 19)

    rs.Update
    End If
    rs.Close

    Next iRow

    cn.Close

    Set rs = Nothing
    Set cn = Nothing

    End Sub

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Excellent that you have it working.

    There are a couple of way (but probably more) that I use to determine when to stop the loop.

    The most common suggested on this (and other?) forums is something like this

    Dim LastRow as long

    Cells(65536, 5).Select
    Selection.End(xlUp).Select
    LastRow = Selection.Row

    Then use use last row if For iRow = 2 to LastRow

    OR the one I use most often instead of a For statment I use Loop

    ie.

    iRow = 2

    Loop until Cells(iRow,5) = ""

    SQL = "SELECT * FROM MATT_TEST_Q " & _
    "WHERE [SKU] = " & Cells(iRow, 11) & " AND [BBD ID] = " & Cells(iRow, 5) & " AND [P&L Code] = " & Cells(iRow, 8)
    .
    .
    etc

    iRow=iRow+1
    Loop

    I used column 5 for both examples because it is one of the 'primary keys'(?) and therefore every record (row) should contain data !?

    To run this when the Wookbook Closes the code should be placed (or the sub run) in the 'ThisWorkbook' module BeforeClose event

    ie. in

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    End Sub

    I have never tried running queries/recordsets in the BeforeClose event so will be interesting if it produces any problems !?

    HTH


    MTB
    Last edited by MikeTheBike; 03-15-07 at 05:37.

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    I have just noticed you also wanted to update the DB if a record exists, in which case code should be moded to this

    If rs.BOF And rs.EOF Then rs.AddNew

    rs("Channel") = Cells(iRow, 1)
    rs("Group") = Cells(iRow, 2)
    rs("Budget Banner") = Cells(iRow, 3)
    rs("Budget Banner Division") = Cells(iRow, 4)
    rs("BBD ID") = Cells(iRow, 5)
    rs("Division") = Cells(iRow, 6)
    rs("Budget Channel") = Cells(iRow, 7)
    rs("P&L Code") = Cells(iRow, 8)
    rs("Budget Product Group") = Cells(iRow, 9)
    rs("BPG ID") = Cells(iRow, 10)
    rs("SKU") = Cells(iRow, 11)
    rs("Description") = Cells(iRow, 12)
    rs("Activity Code") = Cells(iRow, 13)
    rs("Product Type") = Cells(iRow, 14)
    rs("Package Size") = Cells(iRow, 15)
    rs("Brand") = Cells(iRow, 16)
    rs("LYVolumes") = Cells(iRow, 17)
    rs("LYReturns") = Cells(iRow, 18)
    rs("LY NISales") = Cells(iRow, 19)

    rs.Update

    rs.Close


    Strictly, in the update case you wouldn't need to update columns 5, 8 & 11 !!


    MTB

Posting Permissions

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