Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Primary Key Violations during Import Data

    Dear Seniors,

    I am importing the Data from excel to Access using the Append query. I had put the primary key in Access Table for avoiding duplicate imports and it works fine.

    Now When I import duplicate values, Message box is displayed for appending the data and it shows the no of Primary key violations.

    Now I would like to import the duplicate datas (which are primary key violated) to the temporary table to understand whether it is really duplicated or is there any data error.

    Kindly help me to understand how to do the same.

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you define a primary key for a table, you cannot import more than one row containing the same value in the column or columns composing the primary key, be it in a temporary table or not. Do not define a primary key if you know that duplicate values could possibly be stored/imported in that table.
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Yes I agree. I don't want to import the duplicate datas. I just want to know what are the values are not imported. May be a Text report is enough for me to identify the data errors.

    Since I am importing the data from excel, the people working in excel sometimes make mistake.

    So I just want to see the datas not imported to ensure it is ok or not.

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or better yet
    store data in a database, export it to a spreadsheet if required. But don't do it the other way round. RDBMS are about data, data integrity, data manipulation. They are the primary reference for data. If you allow people to tinker with that data in spreadsheets andthen re import thatdata into a DBMS then you are asking for trouble.

    what you could do, assuming you cannot wean your users from their dirty habits of meddling original data in spreadsheets is import the spreadsheet data into shadow tables. Run an append query to transfer new data. Then develop some process to try and work out which data is the most recent. Frankly whatever you do is going to be a mess.

    store data in a DB
    Play with copies of data in spreadsheets. If users require a refresh of the data in their playpen get the data from the db
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always import the data to an unindexed table then use the Find Duplicates query wizard to identify the duplicate values, however I tend to agree with Healdem when he states that your application will be a mess if Excel is used as a data entry mean.
    Have a nice day!

  6. #6
    Join Date
    Oct 2013
    Posts
    163
    Dear All,

    Thanks for your inputs. Actually I want to update the data in my Access, However the people who are updating the datas are not familiar with access. Even myself not familiar with access and I am learning now with help of this forum and books. Anyhow I will try to implement the steps for updating the data in access itself.

    In mean time I will manage to eliminate the data with help of duplicate query.

    Thanks and Regards
    R. Vadivelan.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always import the data to a buffer table, then store the rows that are not alreay present in the final table in this final table. You can do this using a query or, row by row, using a recordset. In any case, this is a "less worst" workaround: If you intend to use Access to store the data, build an application that uses Access for data input and use Excel as a reporting system. People that must supply the data do not need to know anything about Access: they just have to know how to use the data input application you supply.
    Have a nice day!

  8. #8
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

    On reading this post, I just want to ask this query whether it is possible in Access or not?

    My query is I have 2 tables, one with Primary key and another without primary key (Buffer Table).

    As you said first I will import my data to Buffer Table, then from buffer table I will append the same to Maintable. During the append action is it possible to delete the data from Buffer Table that are appeneded to Main table.

    For Ex

    MainTable
    A
    B
    C

    BufferTable
    A
    B
    C
    D
    E
    F

    After appending, my buffer table should contain only: A,B,C

    Kindly forgive me if my question doesn't make any sense. I tried this before asking this query, but my buffer table contains all the values, whereus I would like to see only the values that are not appended.

    Thanks and Regards
    R. Vadivelan

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    On reading this post, I just want to ask this query whether it is possible in Access or not?
    Yes it is.
    Quote Originally Posted by velu130486 View Post
    As you said first I will import my data to Buffer Table, then from buffer table I will append the same to Maintable. During the append action is it possible to delete the data from Buffer Table that are appeneded to Main table.
    Not in a single query but you can use two queries that will be run successively: one for appending the data and a second for deleting the data that have been appended. Only the "duplicate" data will then remain in the buffer table. You'll then have to decide what to do with it: delete, update the main table, issue a report...
    Have a nice day!

  10. #10
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Could you please help me how can I delete the appended datas only from the temp Table.

    Thanks and Regards
    R. Vadivelan

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Lets first create and fill the 2 tables: Tbl_Main and Tbl_Buffer:
    Code:
    Sub CreateTestTables()
    
        Const c_DDLMain As String = "CREATE TABLE [Tbl_Main] " & _
                                    "( [ID] TEXT(50) NULL CONSTRAINT PrimaryKey PRIMARY KEY, " & _
                                      "[Col1] INTEGER NULL, " & _
                                      "[Col2] TEXT(50) NULL " & _
                                    ");"
        Const c_DDLBuffer As String = "CREATE TABLE [Tbl_Buffer] " & _
                                    "( [ID] TEXT(50) NULL, " & _
                                      "[Col1] INTEGER NULL, " & _
                                      "[Col2] TEXT(50) NULL " & _
                                    ");"
        Const c_SQLMain As String = "INSERT INTO [Tbl_Main] ( [ID], [Col1], [Col2] ) " & _
                                    "SELECT * FROM " & _
                                        "( SELECT 'A' AS [ID], 0 AS [Col1], 'sss' AS [Col2] FROM MSysObjects UNION " & _
                                          "SELECT 'B' AS [ID], 8 AS [Col1], 'xxx' AS [Col2] FROM MSysObjects UNION " & _
                                          "SELECT 'C' AS [ID], 5 AS [Col1], 'zzz' AS [Col2] FROM MSysObjects " & _
                                        ");"
        Const c_SQLBuffer As String = "INSERT INTO [Tbl_Buffer] ( [ID], [Col1], [Col2] ) " & _
                                      "SELECT * FROM " & _
                                          "( SELECT 'A' AS [ID], 0 AS [Col1], 'sss' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'B' AS [ID], 8 AS [Col1], 'xxx' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'C' AS [ID], 2 AS [Col1], 'zzz' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'D' AS [ID], 9 AS [Col1], 'fff' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'E' AS [ID], 4 AS [Col1], 'ccc' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'F' AS [ID], 7 AS [Col1], 'ggg' AS [Col2] FROM MSysObjects UNION " & _
                                            "SELECT 'G' AS [ID], 5 AS [Col1], 'jjj' AS [Col2] FROM MSysObjects " & _
                                          ");"
        CurrentDb.Execute c_DDLMain, dbFailOnError
        CurrentDb.Execute c_SQLMain, dbFailOnError
        CurrentDb.Execute c_DDLBuffer, dbFailOnError
        CurrentDb.Execute c_SQLBuffer, dbFailOnError
    
    End Sub
    To retrieve the rows that are in Tbl_Buffer but not in Tbl_Main according to the ID column, we can use (qry_SelectNew):
    Code:
    SELECT Tbl_Buffer.ID, 
           Tbl_Buffer.Col1, 
           Tbl_Buffer.Col2
      FROM Tbl_Buffer LEFT JOIN 
           Tbl_Main ON Tbl_Buffer.ID = Tbl_Main.ID
     WHERE Tbl_Main.ID Is Null;
    To insert ther "new" rows (i.e. the rows that are in Tbl_Buffer but not in Tbl_Main), we can use (qry_InsertNew):
    Code:
    INSERT INTO Tbl_Main ( ID, Col1, Col2 )
    SELECT Tbl_Buffer.ID, 
           Tbl_Buffer.Col1, 
           Tbl_Buffer.Col2
      FROM Tbl_Buffer LEFT JOIN 
           Tbl_Main ON Tbl_Buffer.ID = Tbl_Main.ID
     WHERE Tbl_Main.ID Is Null;
    However, if we execute qry_InsertNew, qry_SelectNew won't return any rows although we need to identify the "new" rows to delete them after inserting them into Tbl_Main. We shall then use a temporaty table Tbl_Temp to store the key values retrieved by qry_SelectNew before inserting the corresponding rows into Tbl_Main.

    All in all, we have:
    Code:
        Const c_DDLTemp As String = "CREATE TABLE [Tbl_Temp] " & _
                                    "( [ID] TEXT(50) NULL );"
        Const c_SQLTemp As String = "INSERT INTO Tbl_Temp ( ID ) " & _
                                    "SELECT Tbl_Buffer.ID " & _
                                      "FROM Tbl_Buffer LEFT JOIN " & _
                                      "Tbl_Main ON Tbl_Buffer.ID = Tbl_Main.ID " & _
                                      "WHERE Tbl_Main.ID Is Null;"
        Const c_SQLInsert As String = "INSERT INTO Tbl_Main ( ID, Col1, Col2 ) " & _
                                      "SELECT Tbl_Buffer.ID, Tbl_Buffer.Col1, Tbl_Buffer.Col2 " & _
                                        "FROM Tbl_Buffer LEFT JOIN Tbl_Main ON Tbl_Buffer.ID = Tbl_Main.ID " & _
                                       "WHERE Tbl_Main.ID Is Null;"
        Const c_SQLDelete As String = "DELETE * FROM Tbl_Buffer " & _
                                      "WHERE Tbl_Buffer.ID IN ( SELECT Tbl_Temp.ID FROM Tbl_Temp );"
        
        ' If Tbl_Temp exists, delete all rows from it, else create it.
        '
        If DCount("*", "MSysObjects", "name='Tbl_Temp'") > 0 Then
            CurrentDb.Execute "DELETE FROM Tbl_Temp;", dbFailOnError
        Else
            CurrentDb.Execute c_DDLTemp, dbFailOnError
        End If
        
        ' Fill Tbl_Temp whith the IDs of the new rows.
        '
        CurrentDb.Execute c_SQLTemp, dbFailOnError
        
        ' Insert the new rows into Tbl_Main.
        '
        CurrentDb.Execute c_SQLInsert, dbFailOnError
        
        ' Delete the new rows from Tbl_Buffer.
        '
        CurrentDb.Execute c_SQLDelete, dbFailOnError
    Have a nice day!

  12. #12
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

    For your detailed explanations and Code. I will try to incorporate the same for my project and will ask you for any clarifications.

    Thanks and Regards
    R. Vadivelan

  13. #13
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    I had tried your code for my project and it works fine.

    I have only 1 clarification, Instead of creating the Table_Temp is it possible to manage with Query itself, because I am worried if I create to many tables it will cause any problem to my database.

    Thanks and Regards
    R. Vadivelan

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The table is only created once, then there will be only one additional table in the project. Moreover, you cannot use a query based on a table that does not exist:
    Code:
        ' If Tbl_Temp exists, delete all rows from it, else create it.
        '
        If DCount("*", "MSysObjects", "name='Tbl_Temp'") > 0 Then
            CurrentDb.Execute "DELETE FROM Tbl_Temp;", dbFailOnError
        Else
            CurrentDb.Execute c_DDLTemp, dbFailOnError
        End If
    Have a nice day!

  15. #15
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your inputs and advice.

    Thanks and Regards
    R. Vadivelan

Posting Permissions

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