Automated import from other database (1 Viewer)

[fd110]

I have several databases that has similar structure and I need module to browse and update existing records if changed or insert new records to the current database from others!!!
can anyone help?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Gasman]

I have several databases that has similar structure and I need module to browse and update existing records if changed or insert new records to the current database from others!!!
can anyone help?

Likely be able to assist you, but I doubt anyone has one to just hand over?

You would likely use 2 queries, one for Updates and another for Inserts.
There is a such a thing as an Upsert Query (which I have never used) https://www.google.com/search?q=Ups...INTk2NWowajeoAgCwAgA&sourceid=chrome&ie=UTF-8

If you search here, you will find code to address external databases.
However I would expect you would need a form to address mapping and then build the query from thye selections.

Not an easy undertaking.

 

[isladogs]

UpEnd / Upsert Query

A very common database task is to both append new records to a table and update existing records. The standard approach is to use an APPEND query together with an UPDATE query. This article explains how you can combine both the APPEND & UPDATE into one query. The combined query is often known as...

www.isladogs.co.uk

 

[fd110]

my issue is key fields !
one of tables does not have key field
have three field that triple field together makes record uniq
(textcode, bookcode, bookpage)
and i want simple form to get the file and upsert (upend) the all tables in database
Is there any sample file that I can download an use?

 

[mike60smart]

5 database's is not the standard approach.
Do they all have the same table structure?

 

[fd110]

yes
the structure is similar
files are copy

 

[mike60smart]

yes
the structure is similar
files are copy

Can you explain in a bit more detail the purpose of the database and the 5 copies?

 

[fd110]

We have a source book where we find phrases in other books and refer to it
Each of the researchers may have added a reference phrase on it.
Our tables have keys, but it is possible that there is no match in the keys and they are not unique

 

[The_Doc_Man]

my issue is key fields !
one of tables does not have key field
have three field that triple field together makes record uniq
(textcode, bookcode, bookpage)

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.

 

[isladogs]

This article may also be useful for synchronising data where the fields do not involve a primary key

Synchronise Data 2

This is the second part of an article exploring explores some of the methods that can be used to synchronise data with an external source. The advantages and disadvantages of each method are also discussed.

www.isladogs.co.uk

 

[fd110]

Researchers enter all information in the takhrij table and other tables are not changed Of course, I also have other tables where it is not important to be unique about their data
The problem is that each researcher may have changed the information and received a different key
So the key here does not work and we separate it with the text code and the name of the reference book and the page number

 

[fd110]

This article may also be useful for synchronising data where the fields do not involve a primary key

This is especially important if you have any null values in import or destination tables.
This is because a null value is not equal to anything else NOT even another null value.
some field in my tables are empty!!! Is this null?

 

[GPGeorge]

Please elaborate on the environment in which you maintain multiple copies of the database, and therefore have to engage in merge or synchronization of data. This seems to me to be almost a classic scenario for which the proper solution would be a remotely hosted shared database with individually deployed interfaces for each user.

But before we go off on that track: Why does each user have a totally separate version of the database?

 

[GPGeorge]

This is especially important if you have any null values in import or destination tables.
This is because a null value is not equal to anything else NOT even another null value.
some field in my tables are empty!!! Is this null?

Null is the absence of a value. a field can appear to be "empty" in one of two similar, but different situations.

1. It can be truly null, i.e. no value is available.
2. It can be a string with no characters. Access, and other databases, allow this. A Zero Length String (ZLS), for example, occurs when you type a string of characters into a field, and then delete them. That leaves behind a ZLS, not a null. The null did exist prior to typing anything, but deleting the characters does not restore that null, it replaces the null with a ZLS.

This concept can be a bit of a challenge initially, but with experience, the difference will make sense. And we'll eventually stop thinking in terms of "blanks" or "empty" fields, and begin to think of Nulls and ZLS's.

 

[isladogs]

This is especially important if you have any null values in import or destination tables.
This is because a null value is not equal to anything else NOT even another null value.
some field in my tables are empty!!! Is this null?

Probably ...but they could also be zero length strings. Either way, the approaches in the article may help.
But the issues raised by others are very important and need to be addressed.

EDIT ... As George said.

 

[fd110]

Update query merges old and new data in fields
Some empty fields of new table are filled with old data

 

[fd110]

I want the new record to completely replace the old record.
Some fields are deleted in the new record but
After the update, they are still there!

 

[fd110]

pictu

We have a source book where we find phrases in other books and refer to it
Each of the researchers may have added a reference phrase on it.
Our tables have keys, but it is possible that there is no match in the keys and they are not uniqueView attachment 111363

loaded!!

 

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