Linking DataBases

[Maurício Paiva]

Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to create
only one form.
Can anybody help me with this?

Thanks a lot.
Maurício

 

[Jeff Boyce]

Mauricio

When you say "merge these 4 tables", do you mean actually put the data from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the data in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maurício Paiva]

Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
Maurício

"Jeff Boyce" escreveu:

 

[Jeff Boyce]

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four linked
tables. Open that query in design view and modify it to be an append query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tedmi]

Do you really need to physically copy the data out of the 4 Excel tables into
one table? A key principle of relational databases is that each piece of data
exists once and once only - in your case, in the Excel tables.

My suggestion would be to use a Union query to combine the data out of the 4
tables, which would give you a dynamic view of the current data in all 4
tables. You could use this query just as you would a table - as the target of
a subsequent query, or a data source for a form or report.

Be aware that Access cannot change any data in linked Excel tables.

 

[Piet Linden]

Do you really need to physically copy the data out of the 4 Excel tables into
one table? A key principle of relational databases is that each piece of data
exists once and once only - in your case, in the Excel tables.

My suggestion would be to use a Union query to combine the data out of the 4
tables, which would give you a dynamic view of the current data in all 4
tables. You could use this query just as you would a table - as the target of
a subsequent query, or a data source for a form or report.

Be aware that Access cannot change any data in linked Excel tables.

but then the fact that it's a union query means that the data it
returns is not editable anyway... has nothing to do with Excel. Union
query results are not editable in Access either.

 

[Maurício Paiva]

HI!
Thank you all for your replies.
In fact I don't to edit nothing in Access... I just want to make sure
whenever an user updates its individual spreadsheet that it will reflect to
DB in access.
Then the final user can make a deep search on the database.

Just to give you an idea... the pricing team will feed the spreadsheet
(several of them) and the main contact in sales team will do the search then
he can check for better quotes and give it when requested.

I am an infrastructure guy in the company and we hv no developers in here...
that's why I am pretty lost.

 

[Maurício Paiva]

Jeff,
the idea with the queries is great... however when I try to append I can see
only the other excel lilnks... it doesn't show me that new table a create
with the fields only.
Is there any trick?

Maurício

"Jeff Boyce" escreveu:

 

[Jeff Boyce]

Did you first create the new table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maurício Paiva]

Hi Jeff.
in fact I had to create the table before creating the excel links.
and You are right... it worked fine.

The problem now is query doesn't allow the table to be updated... I have to
run it everytime to reflect to table.

For example:
I added new fields to the spreadsheet and the link into excel was updated
but not the new table with all info.
How can I do this real-time?

Thanks a lot!

"Jeff Boyce" escreveu:

 

[Jeff Boyce]

Mauricio

You may be running up against the difference between Excel (*a spreadsheet*)
and Access (*a relational database*).

In Excel you might just add another column to get another piece of data in
the spreadsheet.

In Access, doing the same thing (adding another field in a table) would NOT
be the correct way to handle the situation. Among other reasons, you'd have
to modify your queries, your forms, your reports, your code, ... every time!
What a maintenance nightmare!

Perhaps if you post a bit more information about just what it is that you're
importing/linking, folks here could offer more specific suggestions.

For example, if you are working with "monthly" values (and just adding
another month in Excel), you'll need to rethink how you structure your data
in Access if you wish to get full use of the relationally-oriented
features/functions in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maurício Paiva]

Hi Jeff, good morning.
I apologize to give you this trouble.

We have 2 teams (pricing and sales) and they want to make sure
whenever an user updates its individual spreadsheet that it will reflect to
DB in access or any other suggestion.
There are specific fields in the spreadsheet that must not be seen by sales
team.

Just to give you an idea... the pricing team (4 people) will feed their
individual spreadsheet and the sales team will check for prices anytime a
customer asks for.

I am an infrastructure guy in the company and we hv no developers in here...
that's why I am lost with this case.

Was I clear? Could I clarify the idea?

Thank you,
Maurício

"Jeff Boyce" escreveu:

 

[Jeff Boyce]

Maurício

I usually advise folks just starting out with Access that there are three
significant learning curves they'll need to be willing to take on if they
are to be successful using Access to develop an application.

1. relational database design -- if "normalization" and "relational" aren't
familiar terms, become familiar with them!
2. Access tricks and tips -- you'll need to learn the idiosynchracies of
how Access does things.
3. Graphical User Interface design -- what kind of design(s) work well for
users and what kinds work against users.

Oh yes, and you need experience as a developer!

If your situation doesn't allow the time or resources to handle all these
learning curves, you may need to consider hiring someone who has already
learned these.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maurício Paiva]

Hey Jeff.
Thanks a lot for your support.
We do need start thinking change our strategy... I will talk to staff and
see other available options or to hire someone who could help us with
development.

B.Rgds,
Maurício

"Jeff Boyce" escreveu: