Automated import from other database (1 Viewer)

[fd110]

Your description is a bit thin. Do you have control over all of the databases? I.e. can you make design changes such as adding an index if you don't have one?

A 3-component compound key is perfectly possible in Access. You just need to specify the correct values for the WHERE clause. Usually (for simplicity of design) you would prefer a way to JOIN a table using one field from each table for the linkage. With three participants in the key field combination, that becomes trickier, though not impossible. However, as long as the record can be uniquely specified, you should be OK.

Since we don't know much about your data, we have to offer more general advice. It would improve performance tremendously if that three-part combination has individually indexed fields, but using a WHERE clause, you can even manage non-indexed compound key fields. You just might wish they were indexed later since part of your problem will be identifying whether a matching record exists (prior to inserting a copy). For that, indexes will be your friend and will keep you from going slowly insane, as there is nothing slower than a sweep through non-indexed records as you do what we call a "relation scan" - translation: picking your way through unindexed records to find something.

how can i use compond join in query?

 

[GPGeorge]

pictu

loaded!!

Would you mind answering my question as to why you feel the need to give each user an isolated copy of the accdb, rather than providing a shared accdb for the data and giving them each an accdb, linked to that shared data, for their own use in adding data to it? That's a standard approach which completely eliminates the need to synchronize data.

Where and how is this database deployed?

Thank you.

 

[fd110]

is it?

 

[fd110]

Would you mind answering my question as to why you feel the need to give each user an isolated copy of the accdb, rather than providing a shared accdb for the data and giving them each an accdb, linked to that shared data, for their own use in adding data to it? That's a standard approach which completely eliminates the need to synchronize data.

Where and how is this database deployed?

Thank you.

Users do not have permanent access to the Internet
Of course, this is a good suggestion
But our current problem remains

 

[fd110]

Would you mind answering my question as to why you feel the need to give each user an isolated copy of the accdb, rather than providing a shared accdb for the data and giving them each an accdb, linked to that shared data, for their own use in adding data to it? That's a standard approach which completely eliminates the need to synchronize data.

Where and how is this database deployed?

Thank you.

I made it myself
and bigginer!

 

[GPGeorge]

I made it myself
and bigginer!

We get that part.

What I am asking is this. Why did you not deploy this using the standard Front End/Back End design?

You have to merge data manually because each user has an isolated copy of the accdb. This would normally not be required.

Where are users located? Are they all in the same office? In the same building? In the same city or town?

 

[fd110]

that is my database
and my ...

 

[fd110]

We get that part.

What I am asking is this. Why did you not deploy this using the standard Front End/Back End design?

You have to merge data manually because each user has an isolated copy of the accdb. This would normally not be required.

Where are users located? Are they all in the same office? In the same building? In the same city or town?

users in the same city

 

[fd110]

You have to merge data manually because each user has an isolated copy of the accdb. This would normally not be required.

 

[fd110]

If the key problem is solved, do you have a simple file to do this? I mean automatic import from other files that are owned by other users.

 

[fd110]

What I am asking is this. Why did you not deploy this using the standard Front End/Back End design?

Well, the problem is the same in the front end, and if the database is not on the same server, there will be different versions!

 

[Pat Hartman]

If you want people to view the attachment, upload it to the site where it can be scanned for virus'

 

[GPGeorge]

users in the same city

Thank you. That's better. Is it safe to assume that they are in different buildings in the same city?

Really, it's hard to help when you only grudgingly give up details after being asked repeatedly.

 

[fd110]

Well, I answered all the questions.
What do you recommend?
For example, how to share the database so that there are no different versions?
All users are in the same city.

with a long distance

In the design of the front end, if the database is not shared, this is the same problem.
The information in our reference books is also constantly changing and has not yet been finalized.

 

[GPGeorge]

If the key problem is solved, do you have a simple file to do this? I mean automatic import from other files that are owned by other users.

I think there could be a different method that doesn't require import at all, but without a clear picture of the scenario, it's guesswork. You seem to be focused on "import" and unable to look at alternatives. The alternatives may well be a better solution, but, again, you control the details and until you share them, it's a bit of a guessing game.

I think you could put a single back end database on a shared network location and give each user a copy of the interface (i.e. the forms through which they use the data). That way no synchronization or merging is needed because it all happens in that single back end database by default.

 

[fd110]

Our work is in a way finding the origin of a book. In the image I sent earlier, our main tables are our side tables, our almost constant information, and the middle table is our reference information that users add.

 

[fd110]

I think there could be a different method

Thank you very much for your suggestion.
In what environment should I share the file on the internet for it to work?
Does Microsoft have a server for this purpose?

If we can't share the file, isn't there a way to import data from multiple files?

 

[GPGeorge]

Well, I answered all the questions.
What do you recommend?
For example, how to share the database so that there are no different versions?
All users are in the same city.

with a long distance

In the design of the front end, if the database is not shared, this is the same problem.
The information in our reference books is also constantly changing and has not yet been finalized.

The users are in different buildings in the same city. You have no central location on which you can install a shared back end.

Perhaps I assume too much about what you already understand about Access databases. I apologize.

The standard approach used by the great majority of experienced Access developers is called a "Split Database".

What that means is you have two accdbs, not one.

One of those two accdbs has only the tables in it. It's usually referred to as the Back End, or BE for short.

The other accdb has the forms, the reports, the queries, and the code (VBA or macros) in it. It is usually referred to as the Front End, or FE for short.

There is one production copy of the BE, and you, the developer keep another copy for development, testing etc. It is on your computer where only you can open it.

The production BE is placed in a network folder on a server. All users have permissions to connect to that BE. I will come back to that point later.

Each user has a copy of the FE on their own computer. The FE has NO data in it. It has only the forms, the queries, etc.

That's how you avoid the import and synchronization problem. All data is always and only in the BE and the BE manages that shared data.

If you don't have physical resources for a shared network server and BE, there are alternatives.

Do you have that available?

 

[fd110]

Do you have that available?

No, we don't have a server and we have to put it on the Internet!
Yes, I know little about the front end

 

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