Solved using access offline for later sync

[TipsyWolf]

hey guys !

I have a split db with around 12 tables and im wounding what's best solution for offline use and later sync with minimum actions.

im picturing it like i would keep my offline backend empty. when im offline - add records to many tables. once im online, "sync" so all my new records from all tables go to online backend (not coping, but moving). making my offline backend is empty. is there something similar ? or what's your suggestions for new access user ?

should i change PK to random to avoid collusions ?

 

[theDBguy]

Offline syncing is easy if you're only adding new data. If you're making changes while offline where others could also be making changes to the same records, that gets complicated.

 

[TipsyWolf]

Offline syncing is easy if you're only adding new data. If you're making changes while offline where others could also be making changes to the same records, that gets complicated.

no, my offline bd is not meant for editing existing records. i would like to make it very simple for 100% replication without any error \ collusions.
so no editing to existing records. i would even prefer to keep offline backend completely empty so i \ my user dont mess with existing records. only create new records and move them to online backend.

should it be split offline db so i get 1 FE and 2 backends (offline and online) or 1 a regular one ?
theDBguy, whats your suggestion then ?

 

[theDBguy]

How many people will be working offline? Will you/them be adding new records to only one table? If only one table, that could be easy. But if you have to add new child records as well, that could also get complicated.

 

[TipsyWolf]

How many people will be working offline?

quantity - there are 15 frontends but only few of them will be doing offline entry data. and rate would be once per month, so its not frequently and i plan it to be like backup plan.

Will you/them be adding new records to only one table?

no, 1 entry affects 5 tables so far. later it could be up to 10

 

[theDBguy]

no, 1 entry affects 5 tables so far. later it could be up to 10

Okay, that complicates things. You'll have to figure out a way to assign unique IDs to the new records that won't collide with the IDs created by the other users working offline.

You probably can't use an Autonumber field for that since that could produce collisions. Maybe you can use a GUID, that might be safer.

Then, during the resync process, you'll have to re-assign actual ID numbers to each new record and then match them with the new child records from the other 5 or 10 child tables.

 

[TipsyWolf]

Maybe you can use a GUID

do u think random long integer isn't enough ? i looked to remind myself whats long integer is ... it means negative 2b up to 2b.. that is 4b... it has almost no chances to catch duplicated ID with small team.

You'll have to figure out a way to assign unique IDs to the new records that won't collide with the IDs created by the other users working offline.

i was thinking to make autonumber random for online and offline backend and try sync via query using append.
5 tables - 5 queries. run them all by vba.

Maybe you can use a GUID

does access have it ? never seen it in access.

 

[theDBguy]

do u think random long integer isn't enough ? i looked to remind myself whats long integer is ... it means negative 2b up to 2b.. that is 4b... it has almost no chances to catch duplicated ID with small team.

It may be, but I wouldn't chance it.

i was thinking to make autonumber random for online and offline backend and try sync via query using append.

I would keep the main db at sequential and maybe you can get away with using random for the offline db.

does access have it ? never seen it in access.

Yes, it does have it. It's called Replication ID.

 

[Pat Hartman]

I have a split db with around 12 tables and im wounding what's best solution for offline use and later sync with minimum actions.

This feature was removed from Jet/ACE years ago. If you want it, you need to switch to SQL Server.

 

[TipsyWolf]

This feature was removed from Jet/ACE years ago. If you want it, you need to switch to SQL Server.

unfortunately sql server is out of my league... too complex for me. it requires programming skills which i obviously dont have. i can't even handle access its 10% capabilities
i just tried to sync data using append from 4 tables in a sequence it should be.
main table,
sub table
2x subsub tables

and it worked. but it crowds my navigation pane with so many tables ... 4 tables times 15 team members gonna be a lot. will group them i guess.

 

[TipsyWolf]

I would keep the main db at sequential and maybe you can get away with using random for the offline db.

but how ? i can't sync if i keep sequential autonumber. at least using append as i have 4-5 tables are linked

Yes, it does have it. It's called Replication ID.

oh, wow. really . will play with it.

 

[TipsyWolf]

guys ! i found the way to make it with 1 click !

so i created 4 queries. i tried to merge them into 1 query but failed. i found this vba and change it so it would fit me

Private Sub SYNC_test_Click()
 On Error GoTo ErrorHandler
  
    ' Start transaction
    DBEngine.BeginTrans
  
    ' Execute your INSERT statements
    CurrentDb.Execute "INSERT INTO TrainingAttendance SELECT * FROM TrainingAttendance1", dbFailOnError
    CurrentDb.Execute "INSERT INTO Employees SELECT * FROM Employees1", dbFailOnError
    CurrentDb.Execute "INSERT INTO QuestionResponse SELECT * FROM QuestionResponse1", dbFailOnError
    CurrentDb.Execute "INSERT INTO RateResponse SELECT * FROM RateResponse1", dbFailOnError
  
    ' Commit if all succeeded
    DBEngine.CommitTrans
    Exit Sub
  
ErrorHandler:
    ' Rollback if any error occurred
    DBEngine.Rollback
    MsgBox "Error occurred: " & Err.Description, vbCritical
End Sub

so now, whenever im online i can sync them.

now i need to find out the way to remove all data from 5 tables within 1 click so user start to entry data into offline bd with 0 records , reducing a chance for any further errors...

 

[The_Doc_Man]

now i need to find out the way to remove all data from 5 tables within 1 click

Do this with a Click Event for your "wipeout" button to have a sequence of DELETE * FROM Tblx queries.

The theoretically correct way to do this "remote data gather and merge" would be that you don't store the data in tables structured like the final targeted tables for your main DB. Instead, you store everything as a series of transactions in the "data entry" DBs, transactions that you can then apply to the master tables. Of course, the trick will be the design of the transaction tables.

 

[CJ_London]

if you are doing replication/syncing you need to use both replication ID and random autonumber. The replication ID is used for replication and the random autonumber number for relationships.

If your tables are all parents and no children then you can drop one of them.

Years ago I developed an app for a utility company’s meter readers. Before going out they uploaded with details of addresses and meters. On return they downloaded the meter reading and comments. They could also tweak some of the upload data.

It used both types and with perhaps 100,000 entries, never had a problem with conflicting autonumbers.

Since meter readers each had a unique round there was no risk of two meter readers trying to update the same record. But we still put protection in place in the form of two date/time fields - last updated in the parent and last updated by the meter readers. When the meter reader attempted to update a record a ‘clash’ could be identified by comparing the dates and prompting the user to make a choice.

 

[Pat Hartman]

oo complex for me. it requires programming skills which i obviously dont have.

Then you probably don't have the skillset to create a reconciliation process either.

You can't trust users to not change data in existing records. Therefore, YOU need to set up the forms to only allow adding new records. If you have done that, then you can use the simple append queries that you have listed. HOWEVER, if you have relationships between tables, you need to run the append queries in a specific order so that the parent records are inserted before the child records are inserted. AND if you actually have child records, the insert is far more complicated because you need to ensure that the FKs of the child records match the PK of their parent record and you can't do that with just these queries. You need to write actual VBA code so you can capture the parent's PK as it is generated and then pass that to the child records in the append query.

CJ said he never had a problem using simple append queries because he used a replicationID and random autonumbers. I don't doubt that, the number range is pretty large BUT, YOU need code to handle duplicates should any occur and so you essentially need to write the code I suggested to handle outliers so you could just as easily use that code for everything.

 

[TipsyWolf]

thank you guys for your advices. its always good to have expert advices. some of them i use , some of the dont because of my skills.
but i just completed a huge work for me !

i managed to import 4 Excel tabs to Access with all PKs generated correctly. HUGE work for me and i woouldn't even expect that i could do it .

so a user paste in the uploader tab a data from excel that comes from ... somehting like google sheets lets put it this way

in the settings tab a user select important data

TA TrainingAttendance table

but the problem was here it that access sees excel formulas even if its like =if(a1=""; ""; "1") if a1 is empty access takes this data (prob. as null) and i face with error. vba helped me here. next is

EMP Employees table

subChild RR and QR

RR and QR are similar .. but ... HUGE for me. tons of hours . tons of tries and errors .. at some point i though i cound't do it ..

vba of RR (RateResponse)

Sub RR()
    Dim ws As Worksheet
    Dim uploaderWs As Worksheet
    Dim empWs As Worksheet
    Dim settingsWs As Worksheet
    Dim i As Long, j As Long, uploaderRow As Long
    Dim randValue As Long
    Dim currentTime As Date
 
 
    Set ws = ActiveSheet ' Change to your sheet if needed: ThisWorkbook.Sheets("SheetName")
    Set uploaderWs = ThisWorkbook.Sheets("uploader")
    Set empWs = ThisWorkbook.Sheets("EMP")
    Set settingsWs = ThisWorkbook.Sheets("settings")
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    currentTime = Now
 
    ws.Range("A2:E321").ClearContents
 
    For i = 2 To 321 Step 8
        uploaderRow = ((i - 2) \ 8) + 2 ' Calculate corresponding uploader row
     
        If uploaderWs.Cells(uploaderRow, 1).Value = "" Then
            ' Leave empty if uploader cell is blank
            ws.Range("A" & i & ":E" & i + 7).Value = ""
        Else

            For j = 0 To 7
                ' Column A: Random number
                Randomize
                randValue = Int((2147483647 - (-2147483648#) + 1) * Rnd + (-2147483648#))
                ws.Cells(i + j, 1).Value = randValue
             
                ' Column B: EMP value
                ws.Cells(i + j, 2).Value = empWs.Cells(uploaderRow, 1).Value
             
                ' Column C: Sequence from 1 to 8
                ws.Cells(i + j, 3).Value = j + 1
             
                ' Column D: Complex logic
                Select Case j
                    Case 0 ' First row in block (D2, D10, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 5).Value - 1
                    Case 1 ' Second row in block (D3, D11, etc.)
                        If uploaderWs.Cells(uploaderRow, 6).Value = settingsWs.Range("T3").Value Then
                            ws.Cells(i + j, 4).Value = "0"
                        ElseIf uploaderWs.Cells(uploaderRow, 6).Value = settingsWs.Range("T4").Value Then
                            ws.Cells(i + j, 4).Value = "1"
                        Else
                            ws.Cells(i + j, 4).Value = "2"
                        End If
                    Case 2 ' Third row in block (D4, D12, etc.)
                        If uploaderWs.Cells(uploaderRow, 7).Value = settingsWs.Range("W3").Value Then
                            ws.Cells(i + j, 4).Value = "0"
                        Else
                            ws.Cells(i + j, 4).Value = "1"
                        End If
                    Case 3 ' Fourth row in block (D5, D13, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 8).Value - 1
                    Case 4 ' Fifth row in block (D6, D14, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 9).Value - 1
                    Case 5 ' Sixth row in block (D7, D15, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 10).Value - 1
                    Case 6 ' Seventh row in block (D8, D16, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 11).Value - 1
                    Case 7 ' Eighth row in block (D9, D17, etc.)
                        ws.Cells(i + j, 4).Value = uploaderWs.Cells(uploaderRow, 12).Value - 1
                End Select
             
                ' Column E: Current timestamp
                ws.Cells(i + j, 5).Value = currentTime
            Next j
        End If
    Next i
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
 
    msgbox "RR is OK", vbInformation
End Sub

then i combine all 4 modules and after user select what kinda training it was.. click one button and

there are delays to avoid miss calculations of PKs

and from access i just need 1 click to grab prepared tables

Public Sub xlsxUpload_Click()
    On Error GoTo ErrorHandler
 
    Application.Echo False
 
    DoCmd.RunSavedImportExport "TA"
    DoCmd.RunSavedImportExport "EMP"
    DoCmd.RunSavedImportExport "RR"
    DoCmd.RunSavedImportExport "QR"
 
    Application.Echo True
    MsgBox "All imports completed successfully!", vbInformation
    Exit Sub
 
ErrorHandler:
    Application.Echo True
    MsgBox "Error running imports: " & Err.Description, vbCritical
End Sub

for many of you guys its just nothing, but for me it was like i invented somehting HUGE
G!!!!. 2 days , 31 hours .... im hungry as fk ! but i MADE IT !

 

[CJ_London]

BUT, YOU need code to handle duplicates should any occur

that's what I said, just didn't provide the code since it depends on specific circumstances

attempted to update a record a ‘clash’ could be identified by comparing the dates and prompting the user to make a choice.