excel to table (1 Viewer)

hello all, can you help please, i have a datbase with a link to an excel sheet,(linked so any updates on the excel sheet is shown on the link in the database).

question can i make a table from this ? if so will the table auto update from the excel sheet,

the reason i want to do this is that on the excel sheet there is no primary id, so if i can put it in a table i can add one

to create New Table from your Excel linked table:
when the "New_Table" is created, you can add new record to it:
unfortunately the New_table is not auto update, you update it manually.

thank you arnelgp, can i get htis to auto update as well?, if so how

Once you import the spreadsheet, it is no longer "live". Every time you want an updated set of data, you will need to delete the rows from the table and then run the append query again.

What exactly are you trying to do? You said you wanted to add an ID, The process I described could end up assigning a different ID each time the spreadsheet is appended depending on how the Excel sheet is updated.

Here is your problem.

1. Linking to Excel as an external table, you can see changes to the table when they are made. I.e. manual updates to the table are visible within moments after the person updating the table exits/saves or manually saves the changes. But you cannot update the Excel worksheet as a linked table from Access because that particular kind of table is linked Read-Only.

2. You can make a copy of the linked table to a local table in your database, but that is then a point-in-time copy, a snapshot. The linkage would be updated but that table took on an independent life of its own the moment the copy was complete.

3. There is no auto-update function in Access or Excel that would allow synchronization of the two entities (worksheet vs. table). If you want to know about changes to the Excel worksheet, you must examine that sheet and compare it to the Access snapshot table to find differences. This will be a piecemeal operation rather than a wholesale operation. There will be no simple single query to do this. A sequence of queries MIGHT do this, but you might be looking at some complex VBA code to assure proper results.

4. You COULD wipe out the table and re-import the worksheet, but you lose whatever data was in the Access table beforehand. This might not be so bad depending on what you were doing, but if ANYTHING in the Access table has gained dependencies (like a records in a child table) then to wipe out the table and import a new version runs the risk of creating orphan records or of violating relational integrity on such records.

To know the best course of action here we would need to know more about the data flow of this particular piece of the business to which it relates.

hello The_Doc_Man,
what i am trying to acheive is this, in an existing datbasethaty we are using in work, one of our team is using an excel spreadsheet, he likes the way we do searches in the database and has asked can we link his sheet to it (this i have done) but i need place a primary id to it , i thought if i make a teble out of it and then add a primary key then i can do better searches on a form with it, or dont i need this ?

not essential to have a unique ID - just depends on what you want to do. Excel data is seriously denormalised so depends what you want your search to return.

Never under any conditions modify a set of data from two perspectives. You can't have Excel updating data in the spreadsheet and your Access app updating data that came from the imported spreadsheet. Reconciliation will be completely manual since you will have no way of knowing which set of data is the master.

If you want a uniqueID, put it in the spreadsheet and write a macro to generate the id each time a row is added. Although, we still don't think you need an ID. However, if it turns out that you want to keep extra data in the database, having a unique ID in the spreadsheet will allow you to keep an Access table that you can link to the spreadsheet via the uniqueID.

Best solution - get the data out of Excel and put it in Access. Make forms and reports and queries so that the user doesn't even know he is working with Access since he is seeing a finished product rather than tables, queries, and code which he won't understand and that is probably why he is reluctant to give up his Excel. He is afraid he won't be able to figure out how to do what he wants if he has to do it in Access.

thank you all for your advice, I will try it without the unique id first

Putting the unique ID in the spreadsheet is good practice if you are going to keep that as your "real" database.

At some point in time, you will need to wean your Excel users off their spreadsheet and get them to do their data maintenance in a database. You can still create exports for them that they can use to slice and dice and make reports that you haven't yet implemented.

Concur with Pat. As usual, she's right on point.

You must eventually establish a definitive data source that is your "authority" for anything that relates to this project. Having distributed data in multiple spreadsheets is OK if and ONLY if the copies are known to NOT be definitive or authoritative.

This is an important concept in databases - authoritative sources. When you have two sources and one says X but the other says Y, you have just incurred extra expense in the ugly process of data reconciliation. Therefore, you save your company time (=money) by saving them from the need to stop what they are doing in the future so they can decide whether X or Y is correct. "An ounce of prevention is worth a pound of cure" as my old Mississippi aunt Hattie used to say.