Import variables from the first database to the main version

[azhar2006]

Hello guys.
I have a database that contains almost general employee information. This database is distributed over the group of departments in which these employees work. Almost every month, changes occur to these employees, such as promotions, travel, and other matters. The employee concerned with working on the database comes and has a copy of it loaded on a flash memory, and the employee working on the main database is interviewed and interviewed for the purpose of updating the data. What I want is to put a button on the database form that imports the variables from the first database to the main copy. So that we do not review employee after employee

 

[Uncle Gizmo]

Access used to have a feature called Replication for syncing offline changes, but it's gone now.

What about trying:

Disconnected Recordsets:
You can use VBA to load data locally, work offline, then sync back to the master. It takes some setup and code but can work well.

SQL Server Backend:
Consider using SQL Server, which has built-in tools for syncing data.

Clarification required:
What specific data needs syncing?

How do you handle conflicts (if the same data is changed in both databases)?

How often are these updates done?

 

[Gasman]

So could be solved with a single back end?

 

[azhar2006]

So could be solved with a single back end?

Thanks Gasman
Yes, I need to update only one table's information fields.
But how can I call this table and a specific department in it from the other database? Because the table contains 16 departments. And maybe I need to update the employees of department number 3 for example.
Command15 =Update Employee Information

Private Sub Command15_Click()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUnifiedNumber As Long
Dim strSpecialization As Variant
Dim strNumberUpgradeAfter As Variant
Dim strDateUpgradeAfter As Variant

Dim intCounter As Integer
Dim StrSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryUnifiedNumber", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
If rs.RecordCount < 1 Then
    MsgBox "There is no new procedure to update it"
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
End If
'rs.MoveFirst

rs.MoveLast
rs.MoveFirst
intCounter = rs.RecordCount
MsgBox "You are about to perform an update" & intCounter & " Update ", , "AZHAR"
'We need to loop through all of the records
'that our query object found
While rs.EOF = False

    strUnifiedNumber = rs![StatFig]
    strSpecialization = rs![Rtba]
    strNumberUpgradeAfter = rs![NumberUpgradeAfter]
    strDateUpgradeAfter = rs![DateUpgradeAfter]

'    StrSQL = "UPDATE tblmastr SET tblmastr.Rtba = '" & strSpecialization & "' WHERE ((tblmastr.StatFig)=" & strUnifiedNumber & ")"
    StrSQL = "UPDATE tblmastr SET tblmastr.Rtba = '" & strSpecialization & "', NumberUpgradeAfter = '" & strNumberUpgradeAfter & "',  DateUpgradeAfter = #" & strDateUpgradeAfter & "# WHERE ((tblmastr.StatFig)=" & strUnifiedNumber & ")"
'    StrSQL = "UPDATE tblmastr SET Rtba = '" & strSpecialization & "', NumberUpgradeAfter = '" & strNumberUpgradeAfter & "',  DateUpgradeAfter = #" & strDateUpgradeAfter & "# WHERE UnifiedNumber=" & strUnifiedNumber

    db.Execute StrSQL, dbFailOnError

rs.MoveNext

Wend
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Update completed successfully"
End Sub

 

[The_Doc_Man]

Gasman is leading up to the idea of having what is called a "split" database.

You have one database with tables, queries, forms, reports, macros, modules, relationships, etc. You get to the Database Tools part of the ribbon and split the database. This yields two files - a front-end that contains everything EXCEPT the tables and the relationships between them, and a back-end that contains the tables and their relationships. (This is a "division of labor" concept.)

You determine a place for this file, perhaps on a file server connected to everyone through a network that uses ordinary Windows File & Printer Sharing - a simple network. This single back-end file is then shared by individual copies of the front-end. Every user gets a private copy of this front-end that points to the back-end tables. You need to read some articles on database splitting, but this is how you prevent having to reconcile multiple databases with identical structure.

When you set up this database, you need to be sure that none of the front-ends are set up for any query or form to use "pessimistic locking." You can use either "optimistic locking" for action queries and forms that will be used for data updating and entry, or "no locks" for reports that can't possibly update the tables.

The one "catch" to this is that for it to work effectively, ALL users must be on the same local-area network (LAN). If everyone is on the LAN then the back-end can be shared properly. If, however, there is a network split such that some users are NOT on the same LAN as others, you still have a problem. Access really was meant for small-office networks.

 

[azhar2006]

Gasman is leading up to the idea of having what is called a "split" database.

You have one database with tables, queries, forms, reports, macros, modules, relationships, etc. You get to the Database Tools part of the ribbon and split the database. This yields two files - a front-end that contains everything EXCEPT the tables and the relationships between them, and a back-end that contains the tables and their relationships. (This is a "division of labor" concept.)

You determine a place for this file, perhaps on a file server connected to everyone through a network that uses ordinary Windows File & Printer Sharing - a simple network. This single back-end file is then shared by individual copies of the front-end. Every user gets a private copy of this front-end that points to the back-end tables. You need to read some articles on database splitting, but this is how you prevent having to reconcile multiple databases with identical structure.

When you set up this database, you need to be sure that none of the front-ends are set up for any query or form to use "pessimistic locking." You can use either "optimistic locking" for action queries and forms that will be used for data updating and entry, or "no locks" for reports that can't possibly update the tables.

The one "catch" to this is that for it to work effectively, ALL users must be on the same local-area network (LAN). If everyone is on the LAN then the back-end can be shared properly. If, however, there is a network split such that some users are NOT on the same LAN as others, you still have a problem. Access really was meant for small-office networks.

Thank you,The_Doc_Man
. I understand what you are saying and dividing the database into front and back. We are talking here about transferring a database from one place to another using flash memory. Because this database is distributed on CDs and we cannot connect it through the local network

 

[The_Doc_Man]

Then the solution is trickier and slower, but far less likely to be fumbled into an intractable mess if you take a "transaction" approach. You have to NEVER directly update the tables in the field. Instead, you have to develop a "transaction" record so that you can edit something in a sequence of steps.

First step is to build this new transaction record. You might have to build a special form and code to build this record.
Second step is to distribute the transaction record via some method (commonly called "sneaker net"). But if you can use FTP, that is even better.
Third is to run the transaction record through some sort of code or query that will apply it to whatever is your master tables.
Fourth is to send back the "master copy" of the tables and replace them in place in all the copies. Again, "sneaker net" or FTP.

Here is the trick, and this might be difficult for your company to accept. There MUST be one AND ONLY ONE "definitive" copy (sometimes also called an "authoritative" copy) of the data tables. The individual copies "out in the field" CANNOT BE AUTHORITATIVE. (Means they can't be trusted to be up to date.) Which is why the field units CANNOT apply the transactions locally. What they have is meaningless until the authoritative copy updates (and therefore validates) everything.

Doing anything less than this is CHAOS and will result in a common phenomenon colloquially called "too many cooks spoil the broth." The correct name for the effect is "destructive interference" and occurs when a non-authoritative change is made to a table after which it is used as though it actually WAS authoritative... followed by which an authoritative change occurs to wipe out the previous non-authoritative change that was used as the basis for a subsequent action. Data provided by a non-authoritative source cannot be trusted to be complete or correct.

When I worked for the U.S. Navy as a contractor, my machine used FTP-style transfers to send transaction files. You say you cannot connect through the local network. But FTP doesn't have the same restrictions as Windows File & Printer Sharing protocols (Server Message Block or SMB), so you can send a whole transaction file to another system pretty quickly even if over a Wide Area Network (WAN). My Navy system participated in FTP exchanges with 18 different partner machines. That method was developed over at least 30 years and worked wonderfully. It just wasn't fast, since it took 24 hours for today's changes to reach other system participants. But the authoritative model for transactional exchanges made it possible for us to manage the personnel issues for the U.S. Navy Reserve forces halfway around the world during Operation Desert Shield and Operation Desert Storm.

 

[The_Doc_Man]

My role in Desert Shield and Desert Storm was merely to stay home and be sure that people's actions were tracked so they could be paid for their efforts. I was strictly a stay-at-home contractor - but my assigned machines were never out of service during Desert Storm. As their system manager, that was MY job and that is what I did. But I greet you, azhar2006, and wish for your life to have peace and prosperity.

 

[azhar2006]

My role in Desert Shield and Desert Storm was merely to stay home and be sure that people's actions were tracked so they could be paid for their efforts. I was strictly a stay-at-home contractor - but my assigned machines were never out of service during Desert Storm. As their system manager, that was MY job and that is what I did. But I greet you, azhar2006, and wish for your life to have peace and prosperity.

Thank youThe_Doc_Man dear. You are really great, kind-hearted and very smart. We have learned a lot from you.

 

[Pat Hartman]

Add a DateChanged to the tables you want to sync. Each time a record is updated, update the DateChanged using Now() in the form's BeforeUpdate event. Then monthly, create a query that selects all the records changed during the previous month and export to excel. Send that Excel file via email to the mothership. Then either manually or automatically, download these files from the email and link to them. Then use them to update the master copy of the database.