Successfully adding a linked Excel Datasource - Providing it's data to secondary tables

[Boost]

Hi there, I have the following issue.



I have the following database in development, here it is broken down into stages.

1. Data source is pulled from an Excel sheet, I have added this to my database as a linked table (IE. update on Excel = update in Access).

2. I have secondary tables solely held within the access database with other variables (IE. Qualifications, Nationality etc).

3. I have master forms that pull information from the multiple tables to create the 'end user' experience.

The issue I have is as follows.

In order for the linked table to provide its information to fellow tables in the database I understand i require a primary key. However, the raw data as pulled from Excel needs to remain unchanged (IE. I can't add a Primary Key column as this data is pulled as an excel sheet from a different application).

I want to provide certain columns of information from the excel, linked table to the other tables that reside in the access database. Here are some questions I have. The reason for this is to have automatic 'refreshing' of the source data for certain columns in which the variables change (ie. rank, location, etc).

1. Can I do this without establishing a primary key column in the original excel imported datasheet? If so, how?

2. The imported, linked, excel datasheet does have a primary identifier which takes the appearance as an individual ID# - can I pretend that this is my 'primary key' and link it somehow to other tables in the access database? If so how?

Any assistance would be largely appreciated.

[CJ_London]

Can I do this without establishing a primary key column in the original excel imported datasheet?

if the file does not have a primary key, you need to work out which columns make any particular row unique for your purposes.

takes the appearance as an individual ID#

that might be it but really cannot comment without a full understanding of your application, the data, the business rules applied, the process etc.

For example if an employee leaves and then returns, they will have a different payroll number - this may matter to you or it might not - you need to decide.

Assuming it is does meet you needs, if you have an employee table or whatever, include this in a new field and index it with duplicates not allowed.

You can then lookup this value and find the primary key for the individual.

[Micron]

takes the appearance as an individual ID#

Individual's ID# (as in a person) or as in unique (takes the appearance as a unique ID#)? Even if it is for a person, and that person retires/returns for a time (as I did) I'd say from the standpoint of having a unique identifier, it doesn't matter if the ID is re-assigned or newly generated - as long as all ID's in that field are unique. Obviously a 2nd ID for the same person can make relating historical records for that person a more difficult task.

I want to provide certain columns of information from the excel, linked table to the other tables

Not understanding why you want to duplicate linked info in other tables, unless maybe linked table data will be a foreign key (FK) in other tables or you need to edit it, in which case it's no longer in sync.

There is also the option to transfer data from a linked spreadsheet (or just from the workbook without linking) to db tables so that you can normalize it, "correct" any data type issues (such as numbers being formatted as text) or do any updates (linked spreadsheets cannot be modified by Access IIRC). If practical, you would have to decide on how often to update those tables, based on the volatility of the source data and the degree of synching you need. It could be as little as once per day, or as often as each time a query was run against any of the related data.

[ArviLaanemets]

Create a linked Excel table in Access DB as read-only (the info is refreshed whenever you open the Access DB). Create a Access table for same information (with autonumeric PK, or with PK read from Excel table). Create an OnOpen event for your Access DB which updates your Access table with info from linked Excel table. Additionally you can create a procedure, which can be called by user at any time moment (a button on form), and which refreshes Access table with info in Excel table at this moment.

Unlike with read-writhe links, this design don't lock the Excel file, and allows you get linked data when someone else uses Excel file when you open your Access DB.

NB! You can get only the info from linked Excel table what was saved last time. When someone uses Excel file, he/she works with virtual copy of Excel file, which exists only in user's computer's memory.

[Boost]

Hi there Micron, thanks for your feedback!

as long as all ID's in that field are unique.

Yes, all the ID's for personnel are unique, and should someone depart for a time and then return - their ID will be reassigned (you pretty much hold it till you die).

Not understanding why you want to duplicate linked info in other tables

After doing some more research, you're right - I don't necessarily need to duplicate linked info in other tables, but I do want to ensure that the personnel ID is attached to the data held in each table. Do you know how I would accomplish this?

There is also the option to transfer data from a linked spreadsheet (or just from the workbook without linking) to db tables

Do you think you could elaborate on this option? Ideally I am aiming to run the synchronisation of the data once a week.

Many thanks!

[Boost]

Hi there Ajax, cheers for the response.

you need to work out which columns make any particular row unique for your purposes.

I have managed to identify the variable that makes each row unique and that is indeed the personnel ID number, that is hypothetically issued for life.

Assuming it is does meet you needs, if you have an employee table or whatever, include this in a new field and index it with duplicates not allowed.

The ID is already included within the linked excel table, the issue I have is creating a second table/s of data that is linked to the personnel information located within the linked database. As in, I would like to utilise the personnel ID as the primary key that relates the data back to the personnel for searching later on.

Thanks again for all your help!

[Boost]

Hi there AvriLaanemets, thanks for offering your opinion on the issue!

Create a linked Excel table in Access DB as read-only

Are linked tables not read-only already?

Create a Access table for same information

Is that a complete duplication of the data, but just held within Access, rather than being a linked table to a source outside of the database? Could you provide some more information behind how I would construct such an event that would replicate the matching fields of data from the linked-uneditable, excel table, to the table copy held in Access?

Many thanks!

[ArviLaanemets]

Are linked tables not read-only already?

It depends on your design. This was advise sa "in case ...". But I advice to use read-only design, as this don't block others from using Excel workbook at same time and vice versa. In case you really need to get some info back to Excel, use an ODBC query in Excel workbook instead with query table as separate one.

Is that a complete duplication of the data, but just held within Access, rather than being a linked table to a source outside of the database? Could you provide some more information behind how I would construct such an event that would replicate the matching fields of data from the linked-uneditable, excel table, to the table copy held in Access?

Yes, it is. Or it almost is. You can have exact copy. You can add additional information when updating table. You can have additional fields which are updated by some form(s) in your database. You can preserve rows deleted from linked table and mark corresponding rows as obsolete. Etc.

Create saved queries to append new data - something like (on fly):

YourAddQuery = INSERT INTO YourAccessTable ... FROM YourLinkedTable WHERE IDField NOT IN (SELECT IDField FROM YourAccessTable)

YourUpdateQuery = UPDATE YourAccessTable SET ... FROM YourLinkedTable ylt INNER JOIN YourAccessTable yat ON yat.IDField = ylt.IDField

YourFeleteQuery = DELETE FROM YourAccessTable WHERE IDField NOT IN (SELECT IDField FROM YourLinkedTable) // in case you want delete the info from YourAccessTable

And now you can use VBA to run those queries whenever you want to refresh the linked data (when database is opened, or when some button is clicked)

Edit: when you have exact copy, then you can have only 2 queries - one deletes all records from Access table, and second inserts all info from linked Excel table.

[CJ_London]

alternative addquery

YourAddQuery = INSERT INTO YourAccessTable ... FROM YourLinkedTable LEFT JOIN YourAccessTable ON YourLinkedTable.IDField=YourAccessTable.IDField WHERE YourAccessTable.IDField is null