Automated import from other database

[GPGeorge]

Yes, there are ways to share the data over the internet. That's what I've been aiming at all along.

And, no, there is no readily available method to merge or synchronize data manually. Like a lot of things, it can be done, as others have already posted. It has been done, but almost always by very experienced people with good knowledge databases. A new developer is not likely to find it a viable approach right away.
========
One method of sharing the data over the internet is to export the tables to SharePoint lists. Then each user's FE can link to those shared lists.

If you all have M365 accounts, this might be the most direct approach.

Another method is to export the data in the tables to a remotely hosted SQL Server, SQL Azure, or other database "in the cloud". Again, each user's FE links to those tables.

What is available depends on your environment, your resources, and your requirements.

There is even one additional approach, using PowerApps for remote data collection, but I think that might be a bridge too far at this early stage of the development for you.

Thank you very much for your time.
So there is no way we can merge these files together?
Because manual integration is both time-consuming and the possibility of mistakes in it is high!!

Sharing and synchronizing users may also take time.
Unfortunately, not all our users have permanent access to the Internet!

what should we do?

It would be great if there was a way to automatically merge

Yes, you can continue to merge the files manually, as you are now doing.

I explained how to deploy the Back End in a way that avoids that problem by deploying it on a network or by exporting the data to a hosted database server.

If your users do not have permanent access to the internet, you are in a tough situation with no easy answers.

I can't tell you what you should do. I don't know what other resources are available to you. It took repeated prompts to get basic information about where users are located. I don't feel up to a series of additional rounds of questions about other factors.

I think you need to give up the idea of "automatically merging" data unless you are willing to write a significant amount of VBA to do that. It's not a beginner task. It can be done, but it's not for the first application you create.

Do all of your users have some way to temporarily connect to the internet? Come to think of it, how do you now gather their individual acdbs for merging/synching? How do they get them to you now?

Access is a wonderful tool in those situations for which it was created. It's not a good tool, though, for this kind of distributed locations with intermittent internet connections. In fact, depending on how much money you have available for other alternatives, I'd probably suggest that is something you need to consider as well.

 

[fd110]

Thank you very much.

I am waiting, maybe other friends in this forum can help.

 

[fd110]

SharePoint isn't included in basic plans and you need a sharepoint license.

.....

Thank you
But for now, I want to see if there is a way to merge the databases
There are some examples on the internet, like the Isladogs method, but each of them has problems and is not exactly what I want.
They are key focused, but my data will have some kind of composite key that has three fields

 

[fd110]

If I have some sample files or code to merge, I might be able to find something out of all of them that solves the problem.
If you have files for merging databases, please share
Thank you

 

[isladogs]

There are some examples on the internet, like the Isladogs method, but each of them has problems and is not exactly what I want.
They are key focused, but my data will have some kind of composite key that has three fields

My article has two pages. As stated previously, the code on the second page that I linked to earlier was written specifically to handle cases where no primary key field exists

Please do tell me what problems the code in that article has.
It has been available online for several years and nobody has ever told me of any problems with it

 

[Gasman]

Thank you
But for now, I want to see if there is a way to merge the databases
There are some examples on the internet, like the Isladogs method, but each of them has problems and is not exactly what I want.
They are key focused, but my data will have some kind of composite key that has three fields

Like I said in post #3, unlikely you will find anything you can just use as is. You would need to adapt any code supplied.

 

[mike60smart]

If I have some sample files or code to merge, I might be able to find something out of all of them that solves the problem.
If you have files for merging databases, please share
Thank you

The problem we have is that all the content of the tables are in Arabic.
Also your table structures are wrong.

Your Main Form based on the table "aslithzib" has a PK of "IDaslithzib" with a datatype of "Number"
and it should be "LongInteger"

Your Main Form is linked to a Subform based on the table "takhrij" using the FK "IDaslithzib" with a Datatype of
"Double" and this should also be a "LongInteger"

This is wrong - tables should have a PK using an Autonumber field and this Autonumber should be linked
using a LongInteger field in the related table with Referential Integrity Enforced.

 

[The_Doc_Man]

OK, I'm going to step back a bit and talk about another elephant in the room.

Access does not work directly over the Internet. PERIOD. It is because the general Internet doesn't work correctly when using the preferred Windows protocol for file and printer sharing. This protocol, Server Message Block (SMB), PRESUMES a permanent and persistent connection over a local area network (LAN) - which is NOT the Internet. SMB is the backbone of Access and, since we don't have OpenSource Access, we cannot change the SMB protocol for something else more suited to Internet communications. Though there are newer versions of SMB, they still have the Internet incompatibility issue. Therefore, the simplest answer is "No LAN? No Access!"

So-called cloud solutions will depend on a lot of factors, but most of the common commercial "cloud" setups do not honor SMB protocol either. For that reason, direct cloud-based solutions so rarely work correctly as to say "No" again. Though there are a few situations where something like a cloud solution actually CAN be made to work within limits.

It's not TOTALLY impossible, however. You can do some things with Azure and a few other file storage systems. We have people here who have looked into storing tables under control of Azure and using Access. I'll have to step away and just let them comment as they see fit because I've never used Azure myself.

You can also look into using RDP (Remote Datagram Protocol) or CITRIX as ways to use the web to open a channel to a central system, after which you can do some things via RDP or CITRIX. You need a system to act as a server for either option, and CITRIX is not cheap. The license to run Access also could become an issue since remote-hosting setups usually run Access on the host system, which therefore needs an Access mutli-user license. Our member Pat Hartman has considerable experience with CITRIX. You also need a decent IT support staff if you go that way, since it is quite common for an IT person to incorrectly configure RDP or CITRIX if they don't understand Access requirements.

If you don't have an appropriate server, then the only other possible solution is to pick some kind of transaction file that you have each user export to become a separate file. Then you can use something like FTP to allow the transaction file to be transmitted to your central data repository, whatever that is. Then you read and work with the transaction files to apply them appropriately. If transactions have to go back down to the individual users, you create another transaction file, FTP it to the end users, and have a facility in these separate databases to import the downloads. If you go this way, users CANNOT be allowed to 'fiddle' with the format. Otherwise, the whole scheme goes down the toilet.

Sounds ugly? Yep, totally. The U.S. Navy's transactions with several of the financial offices were in, essentially, big .CSV files sent up or down via FTP on a daily schedule. We talked with 18 different offices from the Reserve Headquarters Support machine, and ALL of them used more or less the same concept - a delimited file with many lines, each long line in that file representing one record. A light day was when the transaction file only had thousands of lines rather than tens of thousands of lines. (Wasn't an Access DB in this case.)

 

[fd110]

OK, I'm going to step back a bit and talk about another elephant in the room.

Access does not work directly over the Internet.....

OK
For this reason, I am currently looking for a way to sync and merge these files and make sure of the health and correctness of the data transfer.
Or to modify the key in the takhrij table in such a way that it does not cause problems in the merge.
For example, the Autonumber key should not be alone, and for example, it should have a user code or something similar, so that the key becomes unique in any case and we do not have duplicate keys.
Data sync without key takes too long!!

 

[CJ_London]

I don't want to get involved with this thread as I do not have the time, but I would say to @fd110 you need to be clear about your setup - OK we understand you have people around the city who only have intermittent access to the internet. So:

1. are you requiring that just a master db somewhere is updated? Or do all users need to be updated?
2. What is the desired mechanism for transferring data to one place so it can be updated? occasionally via the internet? sending an email with attachments? popping in to a central location with laptop? posting a disk or usb stick? something else?
3. what is the desired medium to be used? their database file or data downloaded to excel or csv?

 

[isladogs]

My article has two pages. As stated previously, the code on the second page that I linked to earlier was written specifically to handle cases where no primary key field exists

Please do tell me what problems the code in that article has.
It has been available online for several years and nobody has ever told me of any problems with it

@fd110
You didn't bother to reply to my post quoted above

 

[GPGeorge]

OK, I'm going to step back a bit and talk about another elephant in the room.

Access does not work directly over the Internet. PERIOD. It is because the general Internet doesn't work correctly when using the preferred Windows protocol for file and printer sharing. This protocol, Server Message Block (SMB), PRESUMES a permanent and persistent connection over a local area network (LAN) - which is NOT the Internet. SMB is the backbone of Access and, since we don't have OpenSource Access, we cannot change the SMB protocol for something else more suited to Internet communications. Though there are newer versions of SMB, they still have the Internet incompatibility issue. Therefore, the simplest answer is "No LAN? No Access!"

So-called cloud solutions will depend on a lot of factors, but most of the common commercial "cloud" setups do not honor SMB protocol either. For that reason, direct cloud-based solutions so rarely work correctly as to say "No" again. Though there are a few situations where something like a cloud solution actually CAN be made to work within limits.

It's not TOTALLY impossible, however. You can do some things with Azure and a few other file storage systems. We have people here who have looked into storing tables under control of Azure and using Access. I'll have to step away and just let them comment as they see fit because I've never used Azure myself.

You can also look into using RDP (Remote Datagram Protocol) or CITRIX as ways to use the web to open a channel to a central system, after which you can do some things via RDP or CITRIX. You need a system to act as a server for either option, and CITRIX is not cheap. The license to run Access also could become an issue since remote-hosting setups usually run Access on the host system, which therefore needs an Access mutli-user license. Our member Pat Hartman has considerable experience with CITRIX. You also need a decent IT support staff if you go that way, since it is quite common for an IT person to incorrectly configure RDP or CITRIX if they don't understand Access requirements.

If you don't have an appropriate server, then the only other possible solution is to pick some kind of transaction file that you have each user export to become a separate file. Then you can use something like FTP to allow the transaction file to be transmitted to your central data repository, whatever that is. Then you read and work with the transaction files to apply them appropriately. If transactions have to go back down to the individual users, you create another transaction file, FTP it to the end users, and have a facility in these separate databases to import the downloads. If you go this way, users CANNOT be allowed to 'fiddle' with the format. Otherwise, the whole scheme goes down the toilet.

Sounds ugly? Yep, totally. The U.S. Navy's transactions with several of the financial offices were in, essentially, big .CSV files sent up or down via FTP on a daily schedule. We talked with 18 different offices from the Reserve Headquarters Support machine, and ALL of them used more or less the same concept - a delimited file with many lines, each long line in that file representing one record. A light day was when the transaction file only had thousands of lines rather than tens of thousands of lines. (Wasn't an Access DB in this case.)

I have deployed very successful Access/SQL Azure and Access/hosted SQL Server applications for years. I think you may have a misconception about Azure. "We have people here who have looked into storing tables under control of Azure and using Access". That's not what SQL Azure means. SQL Azure is a version of SQL Server. It runs on the Azure platform rather than under a Windows server, but most assuredly those tables are in a SQL Server database, not directly in Azure.

One has to design the Front End appropriately to work with a remotely hosted SQL Server or SQL Azure, of course. I am not sure I understand the argument about SMB with regard to SQL Server in any event. We're not talking about trying to write to a file on a file server. The data is passed through an ODBC connection to the remote server (whether Windows or Azure) where the database is installed. However, the actual data is handled within the SQL Server Database, not in a local Windows file, which would be the case with an accdb.

Perhaps it's my lack of familiarity with SMB, but that argument just doesn't seem to me to be relevant.

There are other complications, due to the need to pass the data over that "long wire" from the local accdb to the remote server. Still, It seems to me that manipulating a host of small csv files can incur a larger cost.

 

[fd110]

I don't want to get involved with this thread as I do not have the time, but I would say to @fd110 you need to be clear about your setup - OK we understand you have people around the city who only have intermittent access to the internet. So:

1. are you requiring that just a master db somewhere is updated? Or do all users need to be updated?
2. What is the desired mechanism for transferring data to one place so it can be updated? occasionally via the internet? sending an email with attachments? popping in to a central location with laptop? posting a disk or usb stick? something else?
3. what is the desired medium to be used? their database file or data downloaded to excel or csv?

Hello
Thank you for your attention.
No, I just collect the files and other users do not need to update.

They send me the files by telegram.
I may refer the file to them for correction after I review it.

They send me the Access database which is exactly like my file structure.
Only information may have been added or updated.

Each part of the text of the main book has a unique code. IDaslithzib

Most of the changes and additions are on the takhrij table, which collects the references to the main book
The information of each record of the takhrij table becomes unique in such a way that the name of the book, the volume number and the page number are unique, and every research that has the same book name, volume number and page number must be merged together. become a unique record.

The information of the books that refer to the main book is also stored in the kotob table.

 

[fd110]

You didn't bother to reply to my post quoted above

Without the key it takes too long and my device hangs
With the key, since there are duplicate keys, the information gets mixed up and the old and new information is merged into one record
Even in uniqe keys, both old and new information are merged together in one record.
For example, a field has been deleted in the new record, but after merging the old information inserted to that field that was empty!

 

[isladogs]

Of course, it will take longer without having a unique key.
Indexes including primary key fields exist to speed up all aspects of data retrieval & editing.

However, you can & indeed should create a composite index of several fields to make synchronisation more straightforward
The information will not get mixed up or removed if you approach the problem systematically.
Ther reason why the process would be almost impossible to automate is because of the likely issues that will arise

Do bear in mind that your problems are because you didn't design the tables properly in the first place
The issues are not with the solutions for dealing with the situation you are now facing

 

[fd110]

Of course, it will take longer without having a unique key.
Indexes including primary key fields exist to speed up all aspects of data retrieval & editing.

However, you can & indeed should create a composite index of several fields to make synchronisation more straightforward
The information will not get mixed up or removed if you approach the problem systematically.
Ther reason why the process would be almost impossible to automate is because of the likely issues that will arise

Do bear in mind that your problems are because you didn't design the tables properly in the first place
The issues are not with the solutions for dealing with the situation you are now facing

So, how to join two tables with multiple keys?

In my opinion, you said in the training that you should not do three joins.

Or did you mean three joins without key?

 

[isladogs]

You appear to be confusing your terminology. You don't join tables with multiple keys.
You join tables using fields. The fields may have indexes or be primary / secondary keys (ideally one or both for better performance).

I've checked and there is nothing in either of my two data synchronisation articles that even approximately matches either of the comments in your last two sentences. I didn't say nor did I mean either of those.

Good luck. I don't believe I can assist you any further with your goal.

 

[ebs17]

Enclosed is some code for an automatic import (only one table!) from several databases one after the other. This is a basis for discussion and is far from being suitable for all conceivable designs.

Sub ImportFromExternDB()
    Dim db As DAO.Database
    Dim sPath As String
    Dim sFile As String
    Dim sSQL As String

    Set db = CurrentDb
    sPath = "X:\Anywhere\Importfiles\"

    sFile = Dir(sPath & "*.accdb")
    Do While sFile > vbNullString
        'Debug.Print sFile
        sSQL = "UPDATE TargetTable AS T INNER JOIN [" & sPath & sFile & "].SourceTable AS S" & _
            " ON T.Key1 = S.Key1 AND T.Key2 = S.Key2" & _
            " SET T.Value1 = IIf(T.Value1 IS NULL, S.Value1, T.Value1)," & _
            " T.Value2 = IIf(T.Value2 IS NULL, S.Value2, T.Value2)," & _
            " T.Timestampfield = S.Timestampfield" & _
            " WHERE T.Timestampfield < S.Timestampfield"
        db.Execute sSQL, dbFailOnError

        sSQL = "INSERT INTO TargetTable(Key1, Key2, Value1, Value2, Timestampfield)" & _
            " SELECT S.Key1, S.Key2, S.Value1, S.Value2, Now()" & _
            " FROM [" & sPath & sFile & "].SourceTable AS S LEFT JOIN TargetTable AS T" & _
            " ON S.Key1 = T.Key1 AND S.Key2 = T.Key2 WHERE T.Key1 Is Null"
        db.Execute sSQL, dbFailOnError
  
        sFile = Dir
    Loop
    Set db = Nothing
End Sub

- An update takes place in one direction, it has nothing to do with synchronization (updating in both directions to produce identical data).
- The external databases are copied to the declared folder for updating.
- In the example queries, a key from the combination of two fields is used (Key1, Key2). This key must be unique, none of these fields must be empty, since you cannot compare to True with NULL content.
I didn't understand your explanations about keys and didn't take them into account.
- A timestamp field is used in the tables, which is updated when new or changed. This allows you to reduce the amount of data for the actual update to what is necessary. Following this logic, all external databases would have to be included in an update process at the same time.

A number of problems remain open, also because they depend on the specific way of working.

SET T.Value1 = IIf(T.Value1 IS NULL, S.Value1, T.Value1)

This means that only an empty field is overwritten here, the first DB with content for this field is taken into account, further and perhaps more correct entries from other DBs or later updates are not taken into account. No existing content will be overwritten by NULL content, so it will not be deleted.
Your specific requirement could be different. Then it would have to be formulated very precisely and without contradictions.

 

[The_Doc_Man]

I am not sure I understand the argument about SMB with regard to SQL Server in any event.

From the discussion, I somehow became convinced that they are trying a "native" Access across the Internet, both FE and BE as .MDB or .ACCDB files. That is where SMB protocol becomes a problem. Thank you for clarifying the meaning of Azure. As I mentioned, I have no experience at all with it, but now I understand better. If it is an ODBC connection, SMB doesn't enter into the picture - at least I don't think it does.

So, how to join two tables with multiple keys?

For the sake of example only, let's say you have table A with fields called K1 and K2, and that they are part of a compound key. You want to join this to table B which has two fields Kappa1 and Kappa2 that correspond to K1 and K2. The JOIN clause might resemble:

SELECT <bunch of fields>
FROM A INNER JOIN B ON ( A.K1 = B.Kappa1) AND ( A.K2 = B.Kappa2 ) 
WHERE <some criteria>

Doing this requires that indexes exist on both tables for the fields that will participate in the JOIN clause.

It is also possible to write this without a JOIN:

SELECT <bunch of fields>
FROM A, B
WHERE ( A.K1 = B.Kappa1 ) AND ( A.K2 = B.Kappa2 ) AND <some criteria>

but the latter method is less efficient. However, it might be the only way to implement this if you have no indexes on the key fields.

 

[ebs17]

but the latter method is less efficient. However, it might be the only way to implement this if you have no indexes on the key fields

I would like to contradict that. Both variants work and should produce the same execution plan.
The presence of indexing does not play a role in functionality, but it does play a role in query execution performance (in both cases).