My Access Database is linked to an Excel Table. The Excel Worksheet File gets overwritten with data each time I perform a, save-as, from a web datascrape. I have arranged my access table with the fields from left-to-right. This field arrangement precisely matches the Excel worksheet, verbatim. Every time the linked Excel file changes, the data is read by Access perfectly. No Problems there.
Note: The Excel File is devoid of any primary-key column. There is no column that contains any sequence of numbers to count the records. I do not believe there to be a column that denotes uniqueness of the record either. The Excel file simply contains data, from the left-most column, all the way to the very last column to the right.
Here is the problem. Just like the Excel file, I have no column established as a primary-key in my Access Table. I am simply afraid to add a primary-key column because I fear that the data from the Excel file will start populating the Access table, beginning from the left-most column (exactly where the Primary-Key Column in Access would typically exist).
Alternately, I have thought about adding the primary-key to the Access Table, as the very-last column to the extreme right, but again, I fear that data, other than an AutoNumber, will overwrite the Primary-Key Column, no matter where the Primary-Key column is positioned within the table.
Qa.) What is the best way to get Access to add in the Primary Key as an AutoNumber field, and have that column remain isolated from the data in the rest of the table. I want the Primary Key field to behave as a read-only field as a rule during the data-link-process. However, I need the AutoNumber sequencing function to retain the exclusive write-privilege upon the, proposed, primary-key field.
Qb.) Do I need to, instead, have a relationship to another table that exclusively stores the primary-key, then bring the two tables together in a query? Or is that the wrong path to solve this?
Kevin Yip 4 months ago
Since this is a linked table, Access cannot add a primary key field to it or make any design changes. Any change to the table design has to be done on the Excel end. Any linked table in Access can only have its design modified at the source application.
You can't have a field on one end but not the other. They wouldn't be linked anymore if that was the case. "Linked" means what you see in Access must be identical to what you see on Excel. So you can't add a PK on Excel and not have it shown on Access.
Therefore, if you need your Access linked table to be normalized and have a PK, the source table in Excel must be the same way too. No way around that.
A better way would be to do the opposite thing to what you are doing: have Excel link to Access data instead of the other way around. Being a database program, Access should serve as your data depository, not Excel. If other programs need the data, they should grab them from Access instead of the other way around. In Excel, you can easily link to Access data from the ribbon: Data -> Get Data -> From Database.
Import, not link, the data from Excel into a table in Access, fix the PK, then have Excel link to the Access table instead.
Mark Niefer 4 months ago
Kevin, excellent explanation. I very much appreciate your great comments on this. It really gives me a much better insight, and now I won't pursue a dead-end troubleshooting process of trial and error.
Unfortunately, your suggested workarounds won't work for my scenario, because I scape the data to excel with very few mouse clicks. My whole workflow is deigned to take me from raw data to an Access report in an almost automatic fashion. I right-click the table from the web page, I export it to excel, which is my sole option from the menu at the webpage. The dynamic link I use between excel and access eliminates the labor of importing the data from excel to access. At one time, I was successfully importing the data to Access. These days, the link from excel as the origin, to access as the destination, is very seamless. The link for me is paramount to an efficient medium to telegraph the data to access. The primary key is of lesser importance, and I could live without the primary key, however, the lack of the primary key has the downside, where my access report has hundreds of records, and each record lacks an easy record number to reference. (i.e. record number 129) There is, indeed, a 10-digit serial number for each record, but some records are duplicated (for good reason) because, occasionally, some of these records can have multiple statuses that get reported more than once (a pseudo-one to many setup, that originates within the origin table at the webpage). I suppose I can just insert a left-most column within excel, and enter sequential numbers as a copy sequence or a formula within the first column, but I am trying to keep the manual processes to a bare minimum, as I produce my reports daily. The data at the webpage table changes every 24 hours.
Your explanation is very intriguing, and gives me a much better understanding of how the dynamic link functions, and the underlying rules of how it is engineered to work.
I have gained much from your comment here. Manny thanks to you, Kevin. This will, indeed, serve me well for my design approaches in the future.
Why don't you just import the data every time it's updated in Excel? You could probably assign a composite key based on a combination of fields. It's very hard to say without seeing a sample of the data.
Mark Niefer 4 months ago
Richard, What about a one-to-one relationship in Access? I can leave the excel-linked-table in access alone, so that the access table still matches the excel file field to field, identically. Then I can make a second table that only contains 2 fields, an autonumber field, and a foreign key field. Then I could relate the two tables in a query. I can then base my report on that query instead. This should give me the original data, plus a primary key, without disturbing the table that is linked back to excel.
Kevin Yip 4 months ago
Hi Mark, you can only link the two tables if they both have keys that can be joined. If your Excel table has no keys and "simply contains data" as you said earlier, then you can't join it to anything.
You said earlier that you have a way to "scrape" data to Excel. Maybe you can scrape them to Access instead. Access and Excel both use VBA, and maybe you can modify your Excel code to suit Access with not too many changes to the code.