I am trying to write a VBA code in Microsoft Access that will create and run 60 queries to select new records from 60 linked tables and insert them into 60 tables of the same format. Some background may help here:

I have a large database (lets call the original database "A") that will eventually have over 60 tables, 60 forms & 60 reports. When one of our workers goes out into the field and doesn't have an internet connection, they are going to create new records on a copy of the database stored on their desktop (lets call the duplicate database "B"). Once they have an internet connection, I want them to be able to press a button on either database (I have been trying to code the macro on database A because I thought that would be easiest) and have the new records they created on database B inserted into database A.

I have found code online that seems to be just what I need but when I try to run the macro it gives Error 3022, which says:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship

I have tried running this macro with both databases on my desktop with only 1 linked table with a primary key that is an autonumber, I tried running it with a random autonumber, I tried not having any primary key or index or autonumber and even no records at all. I even tried running it without any linked tables. All ways of trying give me the same Error 3022. I really don't want to create 60 queries one by one so any help would be greatly appreciated. Thank you wizards in advance :)

Here is the code I have tried:

Public Sub ImportTableData(ByVal pstrTable As String, ByVal pstrDb As String)

    Dim strSql As String
    
    strSql = "INSERT INTO " & pstrTable & vbNewLine & _
        "SELECT *" & vbNewLine & _
        "FROM " & pstrTable & " IN '" & pstrDb & "';"

    CurrentDb.Execute strSql, dbFailOnError
    
End Sub

Public Sub ImportAllTables()
    Const cstrDb As String = "C:\MyPath\DatabaseB.accdb"
    Dim tdf As TableDef
    Dim strMsg As String

On Error GoTo ErrorHandler

    For Each tdf In CurrentDb.TableDefs
        'ignore system and temporary tables '
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            Call ImportTableData(tdf.Name, cstrDb)
        End If
    Next tdf

ExitHere:
    On Error GoTo 0
    Set tdf = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3078
        strMsg = "Input table " & tdf.Name & " not found."
        MsgBox strMsg
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportAllTables"
        MsgBox strMsg
        GoTo ExitHere
    End Select
    
End Sub

When I remove the piece of code: 'dbFailOnError', I get different errors. First thing that pops up when I run the macro is: ''Input table 'LocalTableName' not found'', which is the table I am trying to add the records to. Once I click 'Ok' on that pop up box, that is when Error 3134 pops up which says 'Syntax error in INSERT INTO statement'. I am assuming Error 3134 only pops up because it cannot find the local input table (the fist pop up box).

Also, I tried changing the line of code that says: 'Const cstrDb As String = ''C:\MyPath\DatabaseB.accdb''' to instead point to database A (which is the one I am coding the macro on) like this: 'Const cstrDb As String = ''C:\MyPath\DatabaseA.accdb'''. This doesn't give me the first pop up that says ''Input table 'LocalTableName' not found'' but it still gives Error 3134. I have no idea what I am doing wrong and have spent over 20 hours on this problem trying dozens of different things. Any help would be greatly appreciated :)

CodePudding user response：

Well, the main issue is how you going to ensure that the PK (primary keys) and FK (foreign keys) remain the same when they go out to the field and start entering data?

There is a good chance that PK/FK values will now be duplicated, or be the same for on one of the field users.

If a user out in the field adds a record, and someone at main work location adds record, they now are to very likly have the same PK value.

I suppose this might work if you use random autonumbers (never even knew that was a option after all these years!!!) - but I can't say even that going to be 100% reliable.

And when you import that copy from the out in the field user, then either:

You always ensure you accept and take the same PK value, or you let access generate a new PK - but if you do that, then the child records FK value would have to be updated then.

You not really try to do a simple import, but are attempting to do a database synchronization- a stunning and VERY advanced concept. And a very challenging problem.

Access (mdb format) did at one time support what is called database replication. This feature would be ideal for your setup.

However, but for newer accDB formats, it not supported anymore. (and quite sure by around access 2010, replication support was dropped anyway).

So, you could try random for the autonumber. I mean, you simple cannot have the PK's being duplicated on each computer - plain and simple.

The other possbile?

You add to each and every table that has a PK, and add a new column called PKF (f for in the field).

And for every table that has a FK, you add a new column called FKF (again, add F to this).

So, in the field, your PK/FK used is NOT the same as the main master database at work.

So, I wrote a Android sync routine based on above. The applcation was Access, but I moved the data to sql server (but same idea). I moved to sql server since my android phone could use its local database (sqlLite) and hit sql server. (but, it would be difficult to get android to hit some server and read use a accDB file - but was easy to have Android phone hit the sql server database directly).

Gee, maybe they could use Android phones!!! but, this would assume you up to speed writing android software, 100% conformable with SQL server, and also access. I was lucky, had all 3 skill sets, so that is the road and hammer I choose.

And speaking of above? Maybe your lucky, and you have sql server running at work (not express edition, but full edition). I suggest this considering, since the free edition of SQL express can be what we call a replication subscriber to a main sql database. This allows you to sync your local database with the main mothership database.

So, adopting free SQL server express on each field laptop could be a possible solution. Then when they get to a working network, they sync the database using replication.

But, you could try and roll your own sync system.

I did that for an android applcation I wrote, and for a desktop Access application I had. (but, to make all the moving parts easy, I did adopt sql server for database - continued to use Access as the application/UI part).

Now, using "random" for the PK looks to be a possible solution. I just don't know how random, and if this choice can reliable avoid PK collisions for autonumbers.

Random seems like the best road - but ONLY if that choice would prevent duplicate PK id's being used out in the field for new records.

Edit: Random - not even close - it not random enough

So, a bit of research - no, random PK will not work, you still often wind up with collisions - so that idea is off the table.

CodePudding user response：

I figured it out finally. Basically I changed the line of code that executes the SQL to debug.print. This showed me what was going on. The problem is I had linked the tables from the other database when they didn't need to be linked. The names of the local tables would be 'Table A' & the linked tables were 'Table A1'. So there would be a query generated for 'Table A' and another query generated for 'Table A1'. Since there are no tables by the name 'Table A1' in Database B, the query wouldn't work. Plus the fact that, in the line of code executing the SQL, there was an option that says 'dbFailOnError' so since half of the queries weren't working, this option rolls back any updates made by the queries that did work.

I removed all of the linked tables and the macro runs perfectly, unless there are records that are the same on both databases. If I remove 'dbFailOnError' from the code, then the macro runs well no matter what.

So the macro is doing what I want it to but I would like to keep the 'dbFailOnError' part of the code so I will have to do 2 things. First, I have to solve the problem with the primary keys. The answer on this thread by Albert describes this problem well. Second, I have to adjust the SQL to be able to only select records that don't already exist in Database A. I am assuming I can do this by adding a WHERE to the end of the SQL. I will make an update once I fix these problems, or if I just run an SQL server instead. Thank you everyone for your help :)