Automated import from other database

[fd110]

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

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

 

[fd110]

I'll have to work on the Isladogs file a bit, maybe I'll find a way

 

[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.

 

[Pat Hartman]

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

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

Sounds like your researchers are duplicating each others work. They shouldn't be modifying links created by others.

I don't look at internet links and neither do many others. Just post the picture of your relationships here. Make sure that the keys and all fields are visible. Otherwise, post the BE. We don't need to see the FE at the moment. Including the data from the main BE as well as one of the satellite BEs will give us a sense of what you are up against. If the data is sensitive then it will be much harder to see your problem.

If each user is working on different sources, then all their references should be independent and so you can periodically import their new data and send them an updated consolidated BE. With the users all disconnected, this consolidation is difficult. If they send you their BE for import, they MUST stop working with it and wait for the replacement to come back or you will lose data.

I am told that Azure is a possibility as a shared BE in this environment. I cannot confirm it though. Perhaps someone with experience using third-party hosting will join in. Most of the usage I see reported is with Azure in a private cloud. In that setup, your IT people can solve your problems for you but you don't even have a LAN so you don't have the support you need to solve problems.

If you can find confirmation that you can make Azure work, it would be best to hire someone to convert the db and set up the BE in Azure. But you will need to learn how to manage it. If you don't have a lot of data (10,000 rows per table or less), SharePoint may be the path of least resistance. I hate it and would never build an application that uses it but for some types of projects it makes sense and this seems like one.

 

[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

 

[Pat Hartman]

but my data will have some kind of composite key that has three fields

then you need to use the three fields in each join. Instead of PK = FK, you have three fields to match.

Don't forget - you need a composite unique index on the THREE fields. You have to make this by using the Indexes dialog. You cannot do it using the table interface. It allows you to make a multi-column PK but not multi-column indexes. This picture shows three indexes. Two of them have two fields. To add a third, just add a row below the second field and leave the index name blank.

Actually, there is no reason for an autonumber as the PK so just pick the three fields that constitute uniqueness and make those the PK. All three will end up with the key icon. They don't need to be adjacent but the diagram is neater if they are adjacent and always in the same order.

 

[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?