excel to table

[bigmac]

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

 

[arnelgp]

to create New Table from your Excel linked table:

currentdb.Execute "select sheetName.* into New_Table from sheetName;

when the "New_Table" is created, you can add new record to it:

currentdb.Execute "insert into New_table select sheetName.* from sheetName;"

unfortunately the New_table is not auto update, you update it manually.

 

[bigmac]

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

 

[The_Doc_Man]

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.

 

[bigmac]

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 ?

 

[CJ_London]

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.

 

[bigmac]

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

 

[The_Doc_Man]

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.