Humanipro application

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

 

[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

 

[The_Doc_Man]

@MarlonVisser0408

I posted before as an ordinary member, having had some negative experiences with using Excel as a direct tabular data source. However, I now have to put on my moderator hat.

Pat Hartman has apologized for her harsh response. None of us intend to be harsh with other members, new OR old, in any of the technical forums. (Non-tech forums? No promises.)

You do, however, have to realize that we are all volunteers here and (last time I checked) also all humans. We can get frustrated when we see someone going down a costly or questionably-effective path even after telling them of the potential massive pitfalls. It frustrates us because it seems like we have wasted our time or our many years of experience are being ignored. Have you ever been watching a movie and want to yell to the protagonists "Don't open the door..." after which they open it anyway and the bug-eyed monster immediately jumps out?

I hope this helps you to understand better why you got the response you got. We also hope that you can find help here, short-term OR long-term, to better facilitate your projects. But finally, please DO understand that it is our way to warn you of those pitfalls in your path even if that really isn't what you wanted to hear. Would you want your doctor to hide a dire diagnosis from you on your next wellness check? Or would you prefer blunt honesty?

 

[GPGeorge]

Early on in this discussion, I was fumbling for a term to describe what I understand to be the underlying problem here. There was mention of "random" data and handling it in Excel because of its randomness. Possibly that's non an exact rendering of the idea, but close enough.

The term I was looking for is "Structured Data" vs "Unstructured Data". This article covers the main idea.

The underlying problem, then, is that Excel is good for handling Unstructured data in a more or less manageable way precisely because it lacks the features that are needed to manage Structured Data. It is loosey-goosy; it is unstructured.

Relational Database Management Systems (RSBMS), such as Access, are good for handling Structured data. But to do so, the data must indeed be structured.

Once you get that, the argument for trying to manage Excel-based data with Access loses its potency.

 

[GPGeorge]

RDBMS can also handle unstructured data. You just define the column as text, either long or short and put in whatever you want to. So, Excel has no advantage there. Excel does have manipulation advantages when you are trying to duplicate data or clean it up. But once you commit to an RDBMS, you are essentially committing to mostly structured data and the need for clean up in aisle one no longer exists unless you have a constant stream of bad input data and have no way to control the source of the data to fix the problem before it gets to you. You add validation code to your forms and RI to the schema and you keep out bad data going forward. Bulk updates when necessary are done with queries, not by copying and pasting.

True, but to even begin to handle it, "...you just define the column as text, either long or short..." I would argue that the data is immediately structured, at least at a minimum level, by virtue of being part of a table.

 

[MarlonVisser0408]

@MarlonVisser0408 So far, it seems that you are here to complain about how inferior Access is to Excel, to the point where you actually think it is better to use Excel spreadsheets to hold your data than actual tables because spreadsheets support unique functions and data types for every single cell.

Nope, not even close.

Do you honestly think that having 5 million potentially different functions rather than one is some kind of advantage? Yes, when you are manually manipulating data, some things are easier with Excel than with a relational database (notice I don't say "Access") but that is because Excel doesn't care what is in the cell whereas the RDBMS does care and does enforce rules because in an RDBMS, consistency counts for something. I admit to sometimes resorting to Excel to help me to clean up bad data before I convert an old app to Access. But once the data is in Access, then my forms and tables enforce RI and contain validation rules to prevent bad data from ever being saved again.

I was simply asking if there was a benefit to be had there, simply because I do not have the knowledge of how things in the end turn out. I rather save myself hundreds of hours of learning and testing stuff than not to ask some questions. The only reason that I'm even naming excel is because of my minimalistic background, any overlap I have from excel is about all my experience in access - I hope that this time around, you grasp that I don't have the experience to go too far beyond the small references that I can make; it has nothing to do with either Access or Excel actually; it has to do with my experience.

I'm pretty sure that no expert here agrees with either using Excel as your data store or your preference for 5 million functions rather than one. You are on such a bad track that you needed a wakeup call, so I rocked the boat. Sorry, you're right. I was appalled by this approach being proposed by someone who I would have thought would know better given the level of experience you claim. If you actually want to learn how to use Access, we are here to help. Nothing a novice developer does appalls me. They don't know any better.

I'm here for access; don't worry. If I thought that Excel was the solution here, I wouldn't have came down here to waste my time. The very reason that I'm having a hard time getting to start in Access is actually the same reason that Excel doesn't fit for the data that I'm trying to connect and store. It's data from more then a hundred tables and it's driving me insane.

Since you are unfamiliar with Access, it is probably better to state your problem and ask for a solution to the problem rather than asking how to implement what you think should be the solution based on your strong preference for how Excel works.

I think my biggest issue is with the time frame that my state of mind is in and the pressure of me wanting to do something with the product. It's a personal thing that unfortunately, I cannot turn off. This results in me thinking about too much in too little time. Don't get me wrong about wanting to do something with the product, I very much look forward to learning and making it.

Once you actually make the move in your mind from Excel to a RDBMS, you give up the loosey- goosey no rules way Excel handles data for something structured and firm. Right now you seem to be pining for this flexibility. If I define a field as numeric, a RDBMS will simply not allow me to enter "N/A" as a value. If you don't like that, you should probably stick with Excel rather than asking how to make a RDBMS allow "N/A" in a numeric field (just an example).

RDBMS is the only thing really that fits what I'm trying to do. I person can only learn so much at a time - it's not easy to learn something without having reference to things you've done before. For me it feels like learning Chinese by ear sometimes. If anything, I'm proud of the things that I've already managed to do with my own research; not that it's anywhere close to where it's going to be once I'm done

I posted before as an ordinary member, having had some negative experiences with using Excel as a direct tabular data source. However, I now have to put on my moderator hat.

Pat Hartman has apologized for her harsh response. None of us intend to be harsh with other members, new OR old, in any of the technical forums. (Non-tech forums? No promises.)

I think we both have more information now to make sure that this doesn't happen in the future.

You do, however, have to realize that we are all volunteers here and (last time I checked) also all humans. We can get frustrated when we see someone going down a costly or questionably-effective path even after telling them of the potential massive pitfalls. It frustrates us because it seems like we have wasted our time or our many years of experience are being ignored. Have you ever been watching a movie and want to yell to the protagonists "Don't open the door..." after which they open it anyway and the bug-eyed monster immediately jumps out?

I was just gathering information and opinions. It was about having the information for me to let go off the subject. Not only did the post before showed me having closed the idea of having to do anything with this; the topic was already put to solved.

I get your point about the movie, but do you get how it feels to me as someone who's dipping his toes to something new - that was not a good experience. Don't let the tool be a bigger problem then the problem; it's like you said yourself - we're all human. That'd include the recipient of the message.

I hope this helps you to understand better why you got the response you got. We also hope that you can find help here, short-term OR long-term, to better facilitate your projects. But finally, please DO understand that it is our way to warn you of those pitfalls in your path even if that really isn't what you wanted to hear. Would you want your doctor to hide a dire diagnosis from you on your next wellness check? Or would you prefer blunt honesty?

I understand where the tone comes from, but I'll never respect it. Kicking and bashing are not considered to be tools in a healthy learning environment. If I wanted to be gaslighted about something stupid, I'd not join a forum to learn access; I would've joined Facebook or X. Blunt honesty looks differently from what's over there Docman, you know this as well as I do; so let's not compare appels and bananas here. I'm not here to cause a problem, but I'm highly allergic to being shit on and false assumptions. Either ask or zip it - seems pretty simple.

As you said, we've got a better understanding now. I'm sure that future conversations will be better in tone.

 

[GPGeorge]

I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?

I was focusing more on the fact that the data has to be part of a column, which is part of a table, plus the fact that it has a defined data type that doesn't change depending on the user's mood.