Flat linked Excel file as uptading table into Access (1 Viewer)

Hello Guys,

it is me again

I have Excel flat file with present company names and company numbers (which are actual serviced).

This file is linked into my front-end as dictionary.

I will do creating table query which will be transforming this file into Access database table with primary Key - ID and use it later in relations between tables. It is better in my opinion then create relations between external tables from Excel (don't you think?).

Problem is that this Excel table is living. Every day it is automatically reload and refreshing new data (from QlikView). This can be connected with deleting rows, uptading them, adding etc.

My question is: how to do it properly to get Unique IDs from this always changing table?

I thought about doing uptade query but i do not know how.
I also thought that I can add unique key in Qlikview and add it to the Excel file.

But in my head i have immediately this example:

UniqueID, Company
1, Com1
2, Com2
3, Com3

In system Com2 will be not more serviced so it will be deleted from list above.

What about 2 as Unique ID?

UniqueID, Company
1, Com1
3, Com3

Best Wishes,
Jacek

you would run 2 queries to update.
Q1, make an append query with an OUTER join to the internal tCo table to see what names are NOT in the table. (these are new non existing records)

Q2 is an update query. It updates all fields.

run Q1
then run Q2

All IDs are intact.

Thank you Ranman256.

Problem is with unique ID.
In Excel table i do not have unique ID field --> unique number is company number.

Creating uptade query will check new non-existing records and uptade all fields.
But i want to refer from tbl_Main from UniqueID field to tbl_Company to unique ID field created from Excel Table.

So I have in Excel Table:
[Company_number], [Company_name]
100 , Com1
101 , Com2
102 , Com3

In Access i want to create from this new table (automatically with query) table with:
Unique ID, [Company_number], [Company_name]

And this Unique_ID relate to my tbl_Main.

What if User in Excel table (or system) will delete row with company number 101?
Unique ID created (here will be 2 as second value) will be empty...?

Or maybe I am thinking to much and I should simply use queries to check company numbers and uptade all fields and do not create new table with UniqueID as primary_key?
Simply make company_number as unique field?

The same problem I have with Documents dictionary from Excel (Document Names and obligatory). User can change existing rows or delete documents.
Maybe each of them should have unique ID in Excel already?

Best Wishes,
Jacek

Anyone had the same or similar problem?

Jacek

Nobody has a problem with linking tables from external sources using primary keys?

This external sources for example Excels are tables autimatically refreshed every day (uptading data),

Jacek

Hello Access Gurus,

I would like to add here my sample database where I am linking Dictionary table from Excel (flat file which is automatically updated with QV every day) with Access Front-end.

I created relationships with unique field (Zleceniodawca_nr) between Access table and Excel table like here in the picture:
here is the link

It is good approach to do that?
Or better :
1) make apend query and update query while access is starting and extract this data for Access table?
2) Do not create relationships between tables and leave Excel file as alone stand by dictionary?

Please help,
Best wishes,
Thank you in advance,
Jacek Antek

Cross posted at
http://www.accessforums.net/showthread.php?t=66702

Yes i wrote there is cross post in another forum.
Here was not answers...

Jacek

You wrote it was cross posted on the other forum but not here.
You were given an answer here by Ranman & I've added an extra step at the other forum

Sorry ridders i simply forgot to write it here also...

Jacek