Solved Using excel over tables

[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.

 

[MarlonVisser0408]

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.

I think the biggest reason for me to want it to do it like this is because it's more modular in my mind. I'm still learning a lot of the things I need to know to complete this database and sometimes it gets a little to me. Doing it like this, somehow connects more to me in how I've always used to treat data - something that's changing slowly.

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.

Yeah, it's one of my worries. I was tinkering about some solutions in here that are workarounds, even though these will be time-consuming and will defeat the early benefits in the later stages when I'm making the front-ends to distribute.

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.

My experience in Access is growing rapidly, but it won't defeat the muscle memory that I've build up with Excel and Spreadsheets for the past 15. I will not lie. Adding records en masse makes me feel sad inside at times. Of course, they're different programs with different purposes, but where they overlap: I still really, really prefer excel edits.

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.

For me it's not as much about the money, it's more about the fact that the forms and data goes all over the place and I don't feel like using 10 different apps and having different sources of data going places.

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.

I figured that this would be the case long term and I would cry my eyes out, the very reason for me asking it.

Thank you for taking the time to help me out on this. I feel like I can let it go now and be a normal person haha.

 

[isladogs]

As already mentioned. although the appearance of Excel spreadsheets and Access tables may seem similar at first sight, their structure is very different. This goes beyond lack of autonumbers and data type issues already discussed.

By default, linked Excel spreadsheets are read only. You've mentioned the 'IMEX=0 trick. For the benefit of others, setting IMEX=0 in the connection string (instead of the default IMEX=2) will allow you to edit existing data and add new rows in a linked Excel file. However, it will not allow you to delete rows. See my article

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

isladogs.co.uk

NOTE: linked text files including CSV are still read only even when using IMEX = 0

There are other issues. For example. you cannot
a) set referential integrity between linked Excel spreadsheets
b) add indexes to fields making searching and sorting much slower
c) the data in Excel is unlikely to be normalized making it much harder to create effective queries on your data

In general, I would say that if you want to use the many Excel tools available for handling data, work in Excel.
However, if you want to make use of the many benefits of a relational database, import the Excel data into Access

 

[RogerCooper]

My experience with linking Excel spreadsheets with formulas to Access is bad. Links would stop working, even without changes to the spreadsheet.

Simple spreadsheets are stable, but you still need to be careful with dates, zipcodes and long strings of digits.

 

[Pat Hartman]

My experience with linking Excel spreadsheets with formulas to Access is bad. Links would stop working, even without changes to the spreadsheet.

Most of what you are saying is absolutely terrifying. I hate to think of the mess you are creating by trying to make Access operate like Excel.

Think about it this way. In Excel if you have a 50 X 100,000 matrix, you have 5 million versions of essentially the same formula. With a relational database application, you have EXACTLY ONE version of that formula once your table is properly normalized and potentially some VBA to execute the formula for a form or report if you can't put it in a query. Please tell me that you've never run into an error in a spreadsheet because someone was sloppy with copying a formula and parts are hardcoded when they shouldn't be or perhaps maybe some cells don't even have a formula. They just contain left over data. If the sheet is large enough, this is very easy to occur.

You have convinced yourself that since you are an Excel Expert you can create an Access application without bothering to understand anything about how Access works or even what makes the two products so different once you get past the similar look of a table in datasheet view. Talk about hubris.

I strongly suggest that you just leave Access alone and stick with Excel. Your methods will leave you with unvalidated data which you will almost certainly blame on Access and I'm sure you hate Access enough already because it doesn't work like Excel. Why are you even bothering with Access at all since you don't want to use it the way it is intended to be used?

 

[MarlonVisser0408]

Most of what you are saying is absolutely terrifying. I hate to think of the mess you are creating by trying to make Access operate like Excel.

Think about it this way. In Excel if you have a 50 X 100,000 matrix, you have 5 million versions of essentially the same formula. With a relational database application, you have EXACTLY ONE version of that formula once your table is properly normalized and potentially some VBA to execute the formula for a form or report if you can't put it in a query. Please tell me that you've never run into an error in a spreadsheet because someone was sloppy with copying a formula and parts are hardcoded when they shouldn't be or perhaps maybe some cells don't even have a formula. They just contain left over data. If the sheet is large enough, this is very easy to occur.

You have convinced yourself that since you are an Excel Expert you can create an Access application without bothering to understand anything about how Access works or even what makes the two products so different once you get past the similar look of a table in datasheet view. Talk about hubris.

I strongly suggest that you just leave Access alone and stick with Excel. Your methods will leave you with unvalidated data which you will almost certainly blame on Access and I'm sure you hate Access enough already because it doesn't work like Excel. Why are you even bothering with Access at all since you don't want to use it the way it is intended to be used?

That's quite the emotional rollercoaster for an post. The tone in this is to the point that I'm not even going to answer this or do anything other then this with it. It's a shame that this comes from a staff member, what did anyone ever say here to get this kind of reaction out of you?

 

[GPGeorge]

That's quite the emotional rollercoaster for an post. The tone in this is to the point that I'm not even going to answer this or do anything other then this with it. It's a shame that this comes from a staff member, what did anyone ever say here to get this kind of reaction out of you?

To be honest, Pat has expressed bluntly what a lot of other people are probably thinking. Perhaps it could have been said more diplomatically, but not more honestly.

 

[MarlonVisser0408]

So here I am, 3 months fresh to access and still earning the basics of it. Having chosen access after my experience with excel just because I see what it can do differently. Now that I am here trying to learn, I'm getting this kind of shit of my 5th post on this forum? That'd include the welcome post. If anything, my worst experience with access has been Pat. I'm actually really keen on looking what Access can do for me and how it can improve what I'm doing.

Having that part out of the way, who exactly are you to tell me to go away from here and run back to excel for simply asking some questions? I have not seen in any welcoming message so far that I was supposed to be a professional with a big background of coding?

So some honesty back; maybe you should not let the past 70 years of your life get you down to the point that you need to assume between the lines that you know me, my background or what I'm trying to achieve. If you cannot simply answer something simple or feel the need to bash me or others for trying to learn; maybe it's you that need to go places - not me.

 

[Mark_]

When linking to excel tables, Access will attempt to identify what type of data you are using in a given column based on the values of the first 20ish rows. Unfortunately this may not be true for all values in that column. This causes major issues with Access.

Unless you are using a lot of data validation to ensure consistency, you will run into problems. This can be as simple as 0 not being equal to "0 ". As your data grows, you end up with the question of "Why use Excel" if you are only entering data through Access OR "Why use Access" if you are not using Access to enter data.

Rather than link to excel tables, you may want to look at how to import data from Excel, then use the native Access tables for their better performance. For myself, when I need to do this I have a small application that links to both the Excel spreadsheets and the Access tables, then add from the spreadsheets.

You will find this allows you to put data where you really need it without having to worry about issues in Excel that can confuse the issue. Likewise the same app that is used for Import can be used to Export when you have something you find easier to do in Excel.

 

[MarlonVisser0408]

When linking to excel tables, Access will attempt to identify what type of data you are using in a given column based on the values of the first 20ish rows. Unfortunately this may not be true for all values in that column. This causes major issues with Access.

I was wondering how that works. Well, this alone would take be a consideration enough to leave Excel alone.

Unless you are using a lot of data validation to ensure consistency, you will run into problems. This can be as simple as 0 not being equal to "0 ". As your data grows, you end up with the question of "Why use Excel" if you are only entering data through Access OR "Why use Access" if you are not using Access to enter data.

Rather than link to excel tables, you may want to look at how to import data from Excel, then use the native Access tables for their better performance. For myself, when I need to do this I have a small application that links to both the Excel spreadsheets and the Access tables, then add from the spreadsheets.

You will find this allows you to put data where you really need it without having to worry about issues in Excel that can confuse the issue. Likewise the same app that is used for Import can be used to Export when you have something you find easier to do in Excel.

Yeah, I figured by some issues that on their own would be enough to encourage me to not go for it. Even on a more minimalistic approach, this would mean that everything becomes consistently inconsistent.

The biggest reason for me asking was because the idea was bugging me mostly if there was a world to explore down this road - but I see nothing than cliffs with iffy bridges.

Thanks

 

[isladogs]

For info, Access determines the data type based on the first 8 rows (not 20), though you can set rules for how this is done.

The usual approach is to link the Excel data then process it as necessary into local tables in Access