[ACCESS 2016] Copy paste records from tables to other tables in a different database

[annaO]

Hi,
I am ignorant in coding and in VBA and i am trying to copy/paste several records from several tables from a db to tables to another.
At first, the code included an "insert into" but it couldn't work due to autoincrement id. However, when i copy/paste manually, it works.
When i try to launch it, i got this message 2046: Copy command isn't avalaible now
I tried to fond solutions but none worked.
Here is the code, i m sure it's not the best one but trying to do my best! If someone could help me that would be awesome! thanks

Option Explicit
Private Sub btnFusionTbl_Click()
'référence : Microsoft Office xx.x Object Library pour FileDialog
On Error GoTo ErrorHandler
 
Dim dbsInt As DAO.Database, dbsExt As DAO.Database
Dim tdfInt As DAO.TableDef, tdfExt As DAO.TableDef
Dim fdg As Office.FileDialog
Dim strFileNameExt As String
 
Set dbsInt = CurrentDb ' bdd result
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
 
With fdg
    .AllowMultiSelect = False
    .Title = "Selectionnez un fichier"
    .InitialFileName = "C:\Users\Public\Documents\" ' emplacement des bdd base1, base2, ..., result
    .Filters.Clear
    .Filters.Add "Access Databases", "*.mdb, *.accdb"
    If .Show = True Then
        strFileNameExt = fdg.SelectedItems(1) ' bdd base1 et suivantes
        Set dbsExt = OpenDatabase(strFileNameExt, True, False)
        For Each tdfInt In dbsInt.TableDefs
            If Not (tdfInt.Name Like "MSys*" Or tdfInt.Name Like "~*" Or tdfInt.Name Like "USys*") Then
                For Each tdfExt In dbsExt.TableDefs
                    If Not (tdfExt.Name Like "MSys*" Or tdfExt.Name Like "~*" Or tdfExt.Name Like "USys*") Then
                        If tdfInt.Name = tdfExt.Name Then
                            DoCmd.OpenTable tdfExt.Name
                            DoCmd.RunCommand acCmdSelectAllRecords
                            DoCmd.RunCommand acCmdCopy
                            DoCmd.OpenTable tdfInt.Name
                            DoCmd.GoToRecord , , acNewRec
                            DoCmd.RunCommand acCmdSelectRecord
                            DoCmd.RunCommand acCmdPaste
                            DoCmd.RunCommand acCmdSaveRecord
                        End If
                    End If
                Next
            End If
        Next
    End If
End With
 
ExitHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Oups ! Une erreur a été rencontrée :" & vbCrLf & "Error " & Err.Number & ": " & Err.Description
    Resume ExitHandler
End Sub

 

[June7]

What do you mean by 'autoincrement id' - autonumber field type? Why would this prevent INSERT INTO?

When you manual copy/paste, don't you have to select entire new record row of destination table for the paste action? Would have to replicate that in code.

 

[jdraw]

AnnaO,

Have you tested your routine with some sample databases, tables and records?

Here is your code formatted and between code tags.

Private Sub btnFusionTbl_Click()
    'référence : Microsoft Office xx.x Object Library pour FileDialog
    On Error GoTo ErrorHandler

    Dim dbsInt As DAO.Database, dbsExt As DAO.Database
    Dim tdfInt As DAO.TableDef, tdfExt As DAO.TableDef
    Dim fdg As Office.filedialog
    Dim strFileNameExt As String

    Set dbsInt = CurrentDb ' bdd result
    Set fdg = Application.filedialog(msoFileDialogFilePicker)

    With fdg
        .allowMultiSelect = False
        .title = "Selectionnez un fichier"
        .initialFileName = "C:\Users\Public\Documents\" ' emplacement des bdd base1, base2, ..., result
        .Filters.Clear
        .Filters.Add "Access Databases", "*.mdb, *.accdb"
        If .Show = True Then
            strFileNameExt = fdg.SelectedItems(1) ' bdd base1 et suivantes
            Set dbsExt = OpenDatabase(strFileNameExt, True, False)
            For Each tdfInt In dbsInt.TableDefs
                If Not (tdfInt.name Like "MSys*" Or tdfInt.name Like "~*" Or tdfInt.name Like "USys*") Then
                    For Each tdfExt In dbsExt.TableDefs
                        If Not (tdfExt.name Like "MSys*" Or tdfExt.name Like "~*" Or tdfExt.name Like "USys*") Then
                            If tdfInt.name = tdfExt.name Then
                                DoCmd.OpenTable tdfExt.name
                                DoCmd.RunCommand acCmdSelectAllRecords
                                DoCmd.RunCommand acCmdCopy
                                DoCmd.OpenTable tdfInt.name
                                DoCmd.RunCommand acCmdPaste
                            End If
                        End If
                    Next
                End If
            Next
        End If
    End With

ExitHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Oups ! Une erreur a été rencontrée :" & vbCrLf & "Error " & Err.Number & ": " & Err.Description
    Resume ExitHandler
End Sub

 

[Pat Hartman]

It would be far simpler to run an append query.

 

[The_Doc_Man]

Let's look at what you said.

i am trying to copy/paste several records from several tables from a db to tables to another.

If the records are going to be identical in both tables, this is duplication of data, which suggests a basic design issue and/or a misunderstanding of relational database design. A normalized database has, as one of its properties, the minimization of data duplication. If the target table is not solely used for data backup or archiving, then you are building a denormalized database. I.e. one that does not use relational concepts in a relational environment to limit what is being written. Which leads to the question of WHY you want to do this?

At first, the code included an "insert into" but it couldn't work due to autoincrement id.

If the target table has an autoincrement ID field and you are attempting to copy the source table's ID into that field, you have given Access two contradictory commands: (1) to autoincrement a particular field and (2) to accept an externally defined value into that field. If that is the way you set it up, you have a built-in conflict of actions.

There are implications here based on this action. Copying a record to a table that already has an autonumber field suggests that you are possibly using the table two ways - one for new entries that would get their own number, the other for those copied entries that already have a number - but that is again a mixed use of the autonumbered field. I think it is time to step back from coding and try to decide what it is you really wanted to do - so you could then tell US what is on your mind.

Explain your problem in language, not code. We might help you see the real power of Access to minimize your difficulties here. Because at the moment, you appear to have dug yourself a hole, and by showing us only the code, we can't see out of the hole either.

 

[ebs17]

several records from several tables

Shouldn't there be a choice?
Currently this looks like copying EVERYTHING (except system tables).

 

[annaO]

Please post code between CODE tags to retain indentation and readability. DONE

What do you mean by 'autoincrement id' - autonumber field type? YES Why would this prevent INSERT INTO? It is a fact. I tried and it failed, due to the fact these fields are primary keys. I did not build the structure. these primary keys exist but are not used in relationship with other tables.

When you manual copy/paste, don't you have to select entire new record row of destination table for the paste action? Would have to replicate that in code. You are right: i updated the code above accordingly

 

[annaO]

AnnaO,

Have you tested your routine with some sample databases, tables and records?

Here is your code formatted and between code tags.

Private Sub btnFusionTbl_Click()
    'référence : Microsoft Office xx.x Object Library pour FileDialog
    On Error GoTo ErrorHandler

    Dim dbsInt As DAO.Database, dbsExt As DAO.Database
    Dim tdfInt As DAO.TableDef, tdfExt As DAO.TableDef
    Dim fdg As Office.filedialog
    Dim strFileNameExt As String

    Set dbsInt = CurrentDb ' bdd result
    Set fdg = Application.filedialog(msoFileDialogFilePicker)

    With fdg
        .allowMultiSelect = False
        .title = "Selectionnez un fichier"
        .initialFileName = "C:\Users\Public\Documents\" ' emplacement des bdd base1, base2, ..., result
        .Filters.Clear
        .Filters.Add "Access Databases", "*.mdb, *.accdb"
        If .Show = True Then
            strFileNameExt = fdg.SelectedItems(1) ' bdd base1 et suivantes
            Set dbsExt = OpenDatabase(strFileNameExt, True, False)
            For Each tdfInt In dbsInt.TableDefs
                If Not (tdfInt.name Like "MSys*" Or tdfInt.name Like "~*" Or tdfInt.name Like "USys*") Then
                    For Each tdfExt In dbsExt.TableDefs
                        If Not (tdfExt.name Like "MSys*" Or tdfExt.name Like "~*" Or tdfExt.name Like "USys*") Then
                            If tdfInt.name = tdfExt.name Then
                                DoCmd.OpenTable tdfExt.name
                                DoCmd.RunCommand acCmdSelectAllRecords
                                DoCmd.RunCommand acCmdCopy
                                DoCmd.OpenTable tdfInt.name
                                DoCmd.RunCommand acCmdPaste
                            End If
                        End If
                    Next
                End If
            Next
        End If
    End With

ExitHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Oups ! Une erreur a été rencontrée :" & vbCrLf & "Error " & Err.Number & ": " & Err.Description
    Resume ExitHandler
End Sub

Hi, yes i tried. that is why i said i got this 2046 error. Actually i would like to select only ten tables only, but i dont find how to write it.

 

[annaO]

Shouldn't there be a choice?
Currently this looks like copying EVERYTHING (except system tables).

you are right. Actually i want to select 14 tables only, others are only droplists. But i think i will just have to add the ones i dont want in the "if not" condition, with the system tables

 

[annaO]

Let's look at what you said.



If the records are going to be identical in both tables, this is duplication of data, which suggests a basic design issue and/or a misunderstanding of relational database design. A normalized database has, as one of its properties, the minimization of data duplication. If the target table is not solely used for data backup or archiving, then you are building a denormalized database. I.e. one that does not use relational concepts in a relational environment to limit what is being written. Which leads to the question of WHY you want to do this?

Hi. Each team member collects information and fills his database. there is no "front" database. these are independant DB. At the end, we want a global db with all records from all team members.

Now what i do is to
1) copy a team member db that i recalled "global" for example
2) open table A from global db
3) open another team member db, i call it db 1
4)open the same table A from db 1
5) select all records, copy
6) go to table A of global db
7) go to new record
8) paste
9) save

And i have to do this for 14 main tables, for several db.

Among them, 2 "parent" tables. Their primary keys are not autofieldtypes. Each tem member received a list of IDs (like OS_00100-OS_00199) for the 2 parent tables. You cant copy and paste if there are already this OS or OC number. As they received different lists, usauslly i have no problem with copy paste or insert into for these 2 tables. This is fine.
But for all the other tables, which have autofield primary keys, i cant insert data of course. when i do a copy and paste, the copy leads to add the records and the autofieldis filled with new autonumbers. We dont care about this. Because there is not relationship based on their primary keys. Only the foreign keys are important.

If the target table has an autoincrement ID field and you are attempting to copy the source table's ID into that field, you have given Access two contradictory commands: (1) to autoincrement a particular field and (2) to accept an externally defined value into that field. If that is the way you set it up, you have a built-in conflict of actions.

Actually it is working when you copy paste all the records from a table to another. Of course this will be bad if the primary key was needed.

There are implications here based on this action. Copying a record to a table that already has an autonumber field suggests that you are possibly using the table two ways - one for new entries that would get their own number, the other for those copied entries that already have a number - but that is again a mixed use of the autonumbered field. I think it is time to step back from coding and try to decide what it is you really wanted to do - so you could then tell US what is on your mind.

Explain your problem in language, not code. We might help you see the real power of Access to minimize your difficulties here. Because at the moment, you appear to have dug yourself a hole, and by showing us only the code, we can't see out of the hole either.

You are totally right. Basically i want to have a db with all records from all tables from all db.
first i would say i need to select the db(s), then the script would insert (or copy?) OS, then OC records, then tables records from my main tables group inside the tables of my current db.
i am attaching an example.

Thanks

 

[June7]

I tested a manual copy/paste of record from one table to another. Even though autonumber ID field is included in the copy, it is NOT pasted into second table. The table generates its own ID's into the pasted records.

I also had to deal with situations of merging databases. In one case I designed all-in-one db used by field personnel (no connection to network) to input project data. DB is brought back to main office and records are merged into master db. I did not use autonumber field at all in any tables. PK is project number so the import of records is simple. In another case, DB files are received from a contractor and I had to merge. Autonumber keys were used and the import operation was much more complicated in order to preserve the key relationships. Uses recordsets and looping fields referencing index, new keys are generated and saved as foreign key in related records. As I said, complicated but doable.

Copy/Paste process is not used in either case.

If your tables have autonumber field but don't use in relationships, don't use * wildcard in SQL, explicitly reference fields for INSERT INTO SELECT or use recordsets and looping. Otherwise, delete autonumber fields.

 

[The_Doc_Man]

OK, merging multiple databases forces you to think at different levels. You CAN use autonumbers in the distributed databases as a convenience in maintaining parent/child relationships - but you CANNOT simply copy such fields to the combined DB unless you qualify them during the merge. By "qualify" I mean that you will use compound keys / indexes all over the place to avoid the problem that whatever numbering scheme each individual user applies, there will be number overlaps for anything that involves an ID field, so you MUST retain some identity of the origin of the record even if you weren't planning to do individual user reports from the combined DB. Failure to do this correctly would destroy any and all relationships you might have established.

There are several ways to go with this and I can't tell you all of them by any means, but to my mind, the simplest is that you retain the individual user-level auto-generated record ID fields - BUT in the master table, you include the user's ID as part of a compound key that includes the individually generated keys. The fields holding those auto-generated IDs can only be ordinary, not auto-numbered.

If there are any child tables in the individual-level databases and THEY are uploaded to the combined DB, the corresponding child tables in the combined tables MUST have the same keys ... plus USER ID as an added part of the child table's key. Otherwise, you totally (and hopelessly) scramble any child tables and lose relational integrity.

You could link the two databases dynamically and write an INSERT INTO ... FROM style of query to take the data from the individual tables to the combined tables. This importation operation would apply the User ID as part of the import. If doing it this way, you must perform the imports in the order of parent tables first, then child tables, then grandchild tables, etc. in generational order, because to do otherwise would violate relational integrity (if you were going to use it.)

 

[June7]

I am testing code for copy/paste. The new record row gets selected but acCmdPaste and acCmdPasteAppend and acCmdPasteSpecial and ancient
CommandBars.ExecuteMso "Paste" all fail yet when I manually go to the open table where new record row is selected and use Ctrl+V, records are pasted. This code works:

SendKeys "^(v)"

 

[Pat Hartman]

The more I read of this discussion, the clearer it is that you should be using append queries rather than copy/paste. One of the benefits of the append query is that you will be able to include a data field with the name of the source db and perhaps other identifying information. you can keep a log table so that you have a history of what was appended and when which will help to prevent appending duplicates.

 

[annaO]

OK, merging multiple databases forces you to think at different levels. You CAN use autonumbers in the distributed databases as a convenience in maintaining parent/child relationships - but you CANNOT simply copy such fields to the combined DB unless you qualify them during the merge. By "qualify" I mean that you will use compound keys / indexes all over the place to avoid the problem that whatever numbering scheme each individual user applies, there will be number overlaps for anything that involves an ID field, so you MUST retain some identity of the origin of the record even if you weren't planning to do individual user reports from the combined DB. Failure to do this correctly would destroy any and all relationships you might have established.

There are several ways to go with this and I can't tell you all of them by any means, but to my mind, the simplest is that you retain the individual user-level auto-generated record ID fields - BUT in the master table, you include the user's ID as part of a compound key that includes the individually generated keys. The fields holding those auto-generated IDs can only be ordinary, not auto-numbered.

If there are any child tables in the individual-level databases and THEY are uploaded to the combined DB, the corresponding child tables in the combined tables MUST have the same keys ... plus USER ID as an added part of the child table's key. Otherwise, you totally (and hopelessly) scramble any child tables and lose relational integrity.

You could link the two databases dynamically and write an INSERT INTO ... FROM style of query to take the data from the individual tables to the combined tables. This importation operation would apply the User ID as part of the import. If doing it this way, you must perform the imports in the order of parent tables first, then child tables, then grandchild tables, etc. in generational order, because to do otherwise would violate relational integrity (if you were going to use it.)

Hi Doc, thank you for your answer. Actually i got this DB (newly recruited) and the project team filled this global DB by copy and paste all records from a db to another since 3 years. For the main tables OS and OC, the copy and paste is blocked when there is a violation of the PK, therefore it is not possible to copy and paste in any way. for the other tables, the initial autofield pk is not maintained (means that each time indeed there is a copy a new autonumber is incremented), but as i said there are not used in relationships, and they are as well not used for any purpose. I totally agree with good practices you mentioned in data base management but i shouldn't change the structure now at we arrived at the end of the project, because i dont need it, as these PK are not used. I think they have been created by default and because you might need a PK for each table related in a model, even if it is not used in a relationship. I might have to use intert into indeed, and test without autofields. But at least, i would have liked to know how to run a copy/paste for several tables from a db to another. I managed to do it for an insert into, for several tables. But for this copy and paste, that was my question, and why also this 2046 erro happened. Thanks

 

[annaO]

I am testing code for copy/paste. The new record row gets selected but acCmdPaste and acCmdPasteAppend and acCmdPasteSpecial and ancient
CommandBars.ExecuteMso "Paste" all fail yet when I manually go to the open table where new record row is selected and use Ctrl+V, records are pasted. This code works:

SendKeys "^(v)"

Hi, manually it works for me but not with a code. I'll try insert into with fields i will want. maybe it will be better. I did not get the code you wrote? where do i add it in my previous code above?

 

[annaO]

The more I read of this discussion, the clearer it is that you should be using append queries rather than copy/paste. One of the benefits of the append query is that you will be able to include a data field with the name of the source db and perhaps other identifying information. you can keep a log table so that you have a history of what was appended and when which will help to prevent appending duplicates.

Hi, i already run insert into before copy/paste into a vba code, and also through append queries. i think i'll re-write with insert into instruction, despite the fact that, i would have like to have a copy/paste operation for each table. thanks

 

[The_Doc_Man]

Given the number of times you run into copy/paste being blocked for key violations, I think you have to realize that you are doing something that Access thinks is wrong, so it disallows it. Copy/paste, given the structure you described, will NEVER reliably work. If it works, it will be an accident or blind luck. This problem is a DB Merge action for which the central DB design doesn't seem to support the merging operation.

 

[annaO]

Yes, i will try the id user option then. Thanks

 

[June7]

The SendKeys code would replace line DoCmd.RunCommand acCmdPaste