Using excel over tables (1 Viewer)

[MarlonVisser0408]

I've experimented with using linked Excel spreadsheets instead of tables in my Access database to improve mass-editing efficiency. However, the biggest issue I’ve encountered is the lack of an AutoNumber field in linked tables.

I know Access isn't designed to support AutoNumber in linked tables, and while there are workarounds, they often lead to data integrity issues or conflicts when linking related records. Over time, this approach has caused instability in my setup.

Despite this, I still see potential advantages, such as easier data migration, added security through OneDrive permissions, and better integration with external systems. But I’m starting to question whether these benefits outweigh the drawbacks.

So, I have two key questions:

1. Are these perceived benefits valid, or am I overlooking critical downsides?
2. Is there a reliable way to have Access generate unique IDs for linked Excel records without causing conflicts?

Thanks!

Greetings
Marlon Visser

 

[The_Doc_Man]

One would presume that your reason is either (a) all your data resides in Excel workbooks and/or (b) you have enough data that you can't keep the lists in a native access back-end, probably because of the 2 GB storage issues.

The USUAL solution we suggest is to look into a low-cost SQL engine such as MySQL, which has something like 10 GB storage limits. (Don't quote me on that one.)

Before we go too far off the deep end here, tell us WHY you don't want to convert your Excel files to Access tables. I believe that if the linked table is native Access, it IS possible to have an autonumber on a linked table. The whole concept of a split database depends on that kind of ability.

 

[GPGeorge]

I've experimented with using linked Excel spreadsheets instead of tables in my Access database to improve mass-editing efficiency. However, the biggest issue I’ve encountered is the lack of an AutoNumber field in linked tables.

I know Access isn't designed to support AutoNumber in linked tables, and while there are workarounds, they often lead to data integrity issues or conflicts when linking related records. Over time, this approach has caused instability in my setup.

Despite this, I still see potential advantages, such as easier data migration, added security through OneDrive permissions, and better integration with external systems. But I’m starting to question whether these benefits outweigh the drawbacks.

So, I have two key questions:

1. Are these perceived benefits valid, or am I overlooking critical downsides?
2. Is there a reliable way to have Access generate unique IDs for linked Excel records without causing conflicts?

Thanks!

Greetings
Marlon Visser

Please help us understand why you see Excel as potentially offering easier data migration or better integration with external systems.

 

[MarlonVisser0408]

One would presume that your reason is either (a) all your data resides in Excel workbooks and/or (b) you have enough data that you can't keep the lists in a native access back-end, probably because of the 2 GB storage issues.

The USUAL solution we suggest is to look into a low-cost SQL engine such as MySQL, which has something like 10 GB storage limits. (Don't quote me on that one.)

Before we go too far off the deep end here, tell us WHY you don't want to convert your Excel files to Access tables. I believe that if the linked table is native Access, it IS possible to have an autonumber on a linked table. The whole concept of a split database depends on that kind of ability.

It's not as much that I don't want to.

The idea is more in the fact that if you can get the linked table (IMEX=0) working correctly with an automatically filled ID field, you can use it from through both ways flawlessly and without any extra effort. The reason it's been haunting me is the availability for a lot more and faster solutions to adding, editing and repairing records.

Please help us understand why you see Excel as potentially offering easier data migration or better integration with external systems.

Because I'm using this in a humanitarian setting, there's a lot of random data from everywhere that doesn't really fit in an already existing app. This is why it's interesting for me to connect through excel to have a fast way of editing existing records with excel tools. This makes it so that I don't have to connect with too many services to being with and from where I stand; I can do most of it through excel and IFTTT (if this, then that) function. If I use the native way of doing this, it would require some VBA here an extra macro there (if i'm not mistaken)? While if I get the IMEX=0 trick, there's no extra work.

I should've been more careful with my words, it's not as much that the integration into other tools in- or decreases. It's that I already know all the ways of how to automate the data in excel and this seems like a very easy workaround that would allow me to learn less and be done earlier.

 

[GPGeorge]

Ah, random data is a different kind of challenge for relational databases. I thinks there's a name for that kind of data although it escapes me at the moment.

I have a hard time seeing how that aligns with the desire to create the equivalent of AutoNumbers for IDs, though. I guess I would have to know more about your data to understand how it would work.

 

[The_Doc_Man]

First comment: Linking to an Excel spreadsheet is good if you are the only user of the spreadsheet and of the Access app that links to it. Due to differences in the file locking mechanism, the first user of an Excel spreadsheet locks that sheet so that all other users are read-only (until that first user exits). It is a quirk in the file locking strategy that the two are different in their behavior... a quirk but also a hard fact.

The reason it's been haunting me is the availability for a lot more and faster solutions to adding, editing and repairing records.

I can see "editing and repairing" because Excel has no cell data type. Each cell can be formatted in various ways, interpreted in various ways, changed in various ways. But technically, each cell holds a totally independent value - independent unless you implement a value derived from a function that references another cell or range. You can repair a cell because Excel doesn't care what is in it. Only you care. Access, on the other hand, can be a little bit insistent about data types such that you could build a form that TRAPS invalid data and allows (INSISTS) that a valid option is entered.

You are worried about the cost of making an app that does its own checking so that you can't enter bad data in the first place. However, you have to balance that cost against the continuing cost (i.e. time = money) of repairing data. It's a "pay me now or pay me later" situation and you have to evaluate both the NOW and LATER parts of that equation to see which one costs you more in the long run.

if you have a unique identifier / prime key, adding becomes an issue in consistency. You can easily use an Access-style autonumber. Or you can take the Access idea of finding the MAX value in the identifier column and add 1 to it, regardless of where you find it. When you are dealing with a few dozen or a few hundred records, no biggie. When it gets into the thousands of records, Excel - having to interpret each cell in turn - can be slower than Access, which KNOWS the data type and can use stored key/index data to determine a new identifier key quickly by computation, or even quicker when dealing with autonumber, which keeps the correct next number in the table definition.