Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2014
    Posts
    3

    Unanswered: Not sure if this is a quick question or not!

    Board,

    I have a scenario where I am importing records to append to a table, however some of the records may be amendments to records that are already in the table. What is the best place to start going about this, should I create a table purely from the import and then use that with a query to update entries or append new ones to my existing table (I write this as if I know how to do that!)

    Apologies for poor explanation, I'm normally on MrExcel and they understand rambling there!

    Thanks for looking

    Smith...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The operation you want to perform is usually referred as an "UPSERT" UPSERT operations can:
    a) Insert rows from a Source table in a Destination table when such rows do not already exist in Destination.
    b) Update rows in a Destination table from data in a Source table where a column allows matching rows in both table (usually the Primary Key column of Destination has a matching column in Source).
    c) Optionally delete rows from a Destination table where a matching row cannot be found in a Source table (see above for the meaning of "matching").

    The following VBA procedure can perform operations a (Insert) and b (Update) with the following conditions and restrictions:
    - Source is a table imported from Excel (the procedure does not perform the import operation).
    - Destination is the target table (i.e. the table that must be updated from Source).
    - Source and Destination MUST have the same structure (i.e. same number of columns and same order of columns as far as the data type is concerned).
    - Destination MUST have a Primary Key composed of a single column (not an AutoNumber).
    - The names of the columns in both tables do not matter (but their type does: see above).
    - If the Primary Key in Destination is an AutoNumber, the procedure can be amended to cope with it.
    - If there are spaces in the name of any table or the name of any column (never a good idea!) errors will occur. The procedure can be amended to cope with spaces in names.
    - You can call the procedure Upsert from anywhere in your project:
    Code:
    Upsert "DestinationTableName", "SourceTableName"
    - The procedure is rather slow as it must analyse the srtucture of both tables (Source and Destination). The delay for performing this operation remains the same whatever the number of rows to be processed can be.
    - The procedure was tested (and works!) with Access 2003, 2010 and 2016-preview.
    Code:
    Public Sub Upsert(ByVal Destination As String, ByVal Source As String)
    
        Const c_SQL1 As String = "SELECT @S.@K FROM @S LEFT JOIN @D ON @S.@K = @D.@P WHERE @D.@P Is Null;"
        Const c_SQL2 As String = "INSERT INTO @D ( @LD ) SELECT @LS FROM @S WHERE @S.@K In (@W);"
        Const c_SQL3 As String = "UPDATE @D INNER JOIN @S ON @D.@P = @S.@K SET "
    
        
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim idx As DAO.Index
        Dim fld As DAO.Field
        Dim varDestColList As Variant
        Dim varSrcColList As Variant
        Dim strDestPKName As String
        Dim strSrcPKName As String
        Dim strDestColList As String
        Dim strSrcColList As String
        Dim strSQL As String
        Dim lngSrcPKPos As Long
        Dim i As Long
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(Destination)
        
        ' Find the name of the Primary Key column of the Destination table.
        ' Note: The Primary Key must be composed of a single column.
        '
        For Each idx In tdf.Indexes
            If idx.Primary = True Then
                strDestPKName = idx.Fields(0).Name
                Exit For
            End If
        Next idx
        
        ' Create columns list for the Destination table (except the Primary Key column).
        '
        For Each fld In tdf.Fields
            If fld.Name <> strDestPKName Then
                If Len(strDestColList) > 0 Then strDestColList = strDestColList & ", "
                strDestColList = strDestColList & fld.Name
            Else
                lngSrcPKPos = fld.OrdinalPosition
            End If
        Next fld
        
        ' Retrieve the name of the column in Source matching the Primary key of the Destination table.
        '
        Set tdf = dbs.TableDefs(Source)
        strSrcPKName = tdf.Fields(lngSrcPKPos).Name
        
        ' Create columns list for the Source table (except the column matching the Primary key of the Destination table).
        '
        For Each fld In tdf.Fields
            If fld.Name <> strSrcPKName Then
                If Len(strSrcColList) > 0 Then strSrcColList = strSrcColList & ", "
                strSrcColList = strSrcColList & fld.Name
            End If
        Next fld
        varDestColList = Split(strDestColList, ", ")
        varSrcColList = Split(strSrcColList, ", ")
        
        ' Assemble the query retrieving the Id of the new row in Source.
        '
        strSQL = Replace(Replace(Replace(Replace(c_SQL1, "@S", Source), "@K", strSrcPKName), "@D", Destination), "@P", strDestPKName)
        
        ' Assemble the query inserting new rows from Source into Destination.
        ' Note: Both Source and Destination MUST have the same number of columns.
        '
        ' a) Assemble the columns lists for both tables (including the Primary Key of Destination and the matching column in Source).
        '
        strDestColList = strDestPKName                  ' Because strDestPKName is not in varDestColList.
        strSrcColList = Source & "." & strSrcPKName     ' Because strSrcPKName is not in varSrcColList.
        For i = 0 To UBound(varDestColList)
            strDestColList = strDestColList & ", " & varDestColList(i)
            strSrcColList = strSrcColList & ", " & Source & "." & varSrcColList(i)
        Next i
        
        ' b) Assemble the INSERT query.
        '
        strSQL = Replace(Replace(Replace(Replace(c_SQL2, "@W", strSQL), "@K", strSrcPKName), "@S", Source), "@D", Destination)
        strSQL = Replace(Replace(strSQL, "@LD", strDestColList), "@LS", strSrcColList)
        ' Execute the INSERT query.
        '
        CurrentDb.Execute strSQL, dbFailOnError
    
        ' Assemble the UPDATE query (all rows in Destination matching rows in Source will be unconditionally updated).
        '
        ' a) Assemble columns names from both tables.
        '
        strSQL = ""
        For i = 0 To UBound(varDestColList)
            If Len(strSQL) > 0 Then strSQL = strSQL & ", "
            strSQL = strSQL & varDestColList(i) & " = " & varSrcColList(i)
        Next i
        
        ' b) Assemble the UPDATEquery.
        '
        strSQL = Replace(Replace(Replace(Replace(c_SQL3, "@D", Destination), "@S", Source), "@P", strDestPKName), "@K", strSrcPKName) & strSQL & ";"
        
        ' Execute the UPDATE query.
        '
        CurrentDb.Execute strSQL, dbFailOnError
        
        ' Clean up.
        '
        Set tdf = Nothing
        dbs.Close
        Set dbs = Nothing
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2014
    Posts
    3
    Sinndho,

    Thanks very much for such a detailed response, I'll crack on with it.

    Regards,

    Smith

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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