Multiple tables being updated by a single INSERT/UPDATE

[dogdays]

Win 7 32 bit, Access 2003
I have created a number of temporary tables at run time, they are unique to a user for the current session. The tables are created by a template of the table with the created table name containing the sign on name of the user, this produces unique table names. The problem I am having is that when I do an INSERT or UPDATE to one of the temporary tables that action is replicated in the template and all the created tables. e.g if I have
template table = tblTemplateDisplay
created tables tblTemplateDisplayJack and tblTemplateDisplayMary
then when I INSERT a row in tblTemplateJack it is also inserted into tblTemplateDisplay and tblTemplateDisplayMary.
The code to create the tables is:

    ' drop and create a new temporary
    ' the drop is to take into account any changes to the master template of the table
    DoCmd.SetWarnings False
    If utfnTableExists(gstrTempActiveSharesTableName) = True Then
         strQuery = "DROP TABLE " & gstrTempActiveSharesTableName
         DoCmd.RunSQL strQuery
    End If

    ' now create temporary tables for this session, this user
    DoCmd.CopyObject , gstrTempActiveSharesTableName, acTable, gcstrTemplateActiveSharesTableName

Any insights will be appreciated.
tia Jack

 

[jackd]

Please show the complete vba

 

[dogdays]

The following code empties the table and then insert data for active shares.

    ' empty temp table
    strQuery = "DELETE * FROM " & gstrTempActiveSharesTableName
    DoCmd.SetWarnings False
    DoCmd.RunSQL strQuery
    DoCmd.SetWarnings True

 
        strQuery = "INSERT INTO " & gstrTempActiveSharesTableName & "("
        strQuery = strQuery & "nHouseholdNumber, nIndividualNumber, nShareNumber, tShareType, nNumberOfShares, tFullName, tReverseFullName, "
        strQuery = strQuery & "tAddressLine1, tAddressLine2, tAddressLine3, tAddressLine4, tCityStatePostcode, tShareOwnerName,"
        strQuery = strQuery & " cLastPayment, tPaymentForYear, tPaymentReceived, tShare, dtApproved, nYearsOwned"
        strQuery = strQuery & ") VALUES ("
        strQuery = strQuery & shareOwnersRecSet.Fields("nHouseholdNumber") & "," & shareOwnersRecSet.Fields("nIndividualNumber") & ","
        strQuery = strQuery & shareOwnersRecSet.Fields("nShareNumber") & ",'" & shareOwnersRecSet.Fields("tShareType") & "',"
        strQuery = strQuery & iNumberOfShares & ",'"
        strQuery = strQuery & Replace(udtNameAddress.strFullName, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strReverseFullName, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strAddressLine1, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strAddressLine2, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strAddressLine3, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strAddressLine4, "'", "''") & "','"
        strQuery = strQuery & Replace(udtNameAddress.strCityStatePostcode, "'", "''") & "','"
        strQuery = strQuery & Replace(Nz(shareOwnersRecSet.Fields("mComments"), ""), "'", "''") & "',"
        strQuery = strQuery & cPaymentAmount & ",'" & strPaymentForYear & "','"
        strQuery = strQuery & strDateReceived & "','" & strShareNumber & "',#" & shareOwnersRecSet.Fields("dtApproved") & "#,"
        strQuery = strQuery & iYearsOwned
        strQuery = strQuery & ")"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strQuery
        DoCmd.SetWarnings True

Jack