Linking Excel File and Editable

[CJ_London]

if a value contains commas, then you either need to use a different delimiter such as a semi colon or pipe or you should be specifying text delimiters for the text fields when the file is created. But it may be your source app does not have that ability.

Either way it is also not clear whether the data you are importing is all new data to replace all 'old' data. Or a mixture of both - i.e. records already imported plus new records. And as far as the already imported records are concerned, whether in a subsequent import they may have changed in some way which implies the need for an update rather than a delete/insert. Or perhaps a record already imported does not exist in the new import, implying the requirement is for it to be deleted.

At the moment you are causing 'big bloat' by deleting all and reimporting. Updating a record will cause a small amount of 'recoverable' bloat on C&R as will deleting a record. Whilst inserting new records will cause 'bloat' in the sense that they require space, but of course that space will not be recovered on a C&R.

Don't know the numbers but say you are importing around 10,000 records at a time. If the scenarios above apply, perhaps a 100 or so records require updating and maybe 100 deleting and 1000 are new. So very simplistically, at the moment your db doubles in size as a result of the import process. But by managing the import the bloat is reduced to around 1.2% of which 0.2% is recoverable through C&R.

Edit:

The only input is from a single Excel file that is generated from another online program.
I want a way to import the the new Excel file into the Access db and be able to replace it when a new Excel file is generated.

If that is your only input then nothing to stop you creating a new empty database rather than deleting in an existing database and importing to that and then replacing the original file.

 

[The_Doc_Man]

I'm just surprised that Excel and Access don't have a better solution for this.

This problem has existed in the world of computers from a time before the existence of the PC. Any system that uses the scratchpad memory method of dynamically allocated work structures (or data structures) has the same effect. Windows has gotten better about this, but there were times when a Windows system would crash or lock up after no more than a week of continuous activity due to exhaustion of scratchpad space. MS-DOS and DR-DOS were almost the same. Early UNIX also had the problem. The issue is dynamic memory garbage collection. My first computer system work was done in the late 1960s, over 55 years ago, and the problem existed then... LONG before the first PC.

The issue is that you can allocate memory for working space in random chunks but when you are done, the odds are pretty high that your release of that memory isn't in the order in which it was allocated. In other words, it is NOT like a formal stack structure that is self-cleaning. That random release leaves a pattern like a shotgun leaving many small holes in memory surrounded by things untouched by the release process. So you can't totally regenerate free space due to the random lumps left behind. In fifty years, no one has come up with a better idea than to wipe it all out and start from a fresh copy. The old three-finger salute, leftover from the DOS days.

Even now, the "big boys" require the occasional compacting operation. We had an ORACLE installation for the Navy Enterprise Data Center New Orleans and it had something like 20+ disks dedicated to U.S. Navy Reserve personnel records. About once every two months we would have a huge maintenance period during which disks would go through a complete multi-drive re-optimization that was the ORACLE equivalent of the Access Compact & Repair process. There was no avoiding the fact that cleanup became necessary. Too much leftover clutter cramped the system's ability to allocate chunks of memory.

You might also remember that on DOS and early Windows systems, you had to do a disk defragmentation because the disk would get full of little bitty file fragments that would block creation of large, contiguous files. That is another manifestation of the same problem, really. It is a side effect of the Law of Thermodynamics that says "there is no such things as a perfect process that has no friction losses" - but in a data-centric form of that law.

I sometimes think back to my chemistry days and the idea that within that scratchpad area, entropy is maximizing until we reach the point that the free space, though present, is too badly scattered (randomized) for further usable work and we have to reboot the system - like another Big Bang - to clear it. So sometimes, particularly if I had been drinking a little, when I would reboot my computer, I would say "Let there be light." Of course, I cannot deny a touch of megalomania from back in those days. But system administrators ARE like that sometimes.

 

[Weekleyba]

Running a delete query is the direct cause of the bloat. SQL does not garbage-collect its memory space because to do so, it would have to run the equivalent of a C&R to find all of those deleted records. That "dynamic" C&R operation requires exclusive access to the DB. It is usually NOT the import operation that causes bloat - it is the preceding step to get old obsolete data out of the way while the DB is still running. If you had instead chosen to update your table from the new data by building VBA to read the Excel file line-by-line, you would ALSO get serious bloat.

Notice that earlier I didn't suggest your table structure was bad. I said you had a bad OPERATIONAL structure. The way you are dealing with that external data source, the way you have to operate on the data, is what I meant by that. By replacing the whole table, you make the entire prior contents of that table obsolete. The "straight-through" method of deleting what you don't want then importing what you do want is your problem. You need the subtlety of the "template" approach to not cause bloat. Since your discussion suggests that you are the only person who would do this, just doing it by hand would work. However, if you program it you would have more reliability.

Here is a starting point. Look up and carefully explore this topic: FileSystemObject.

FileSystemObject object

VBA language reference

docs.microsoft.com

That link gets you to the Microsoft "Docs" site. Using the tree-like structure on the left of that page, you can drill down to explore topics including how to do file copy operations and file delete operations programmatically. VBA and the "FSO" work together very well and in fact, for what you need, VERY simply.



Actually I understand that, and have been known to be a "bull in a china shop" looking for a better way - and breaking a bunch of things along that path to the better way. Pat and I (as well as many others in the forum) have LOTS of experience with bloat. The problem with bloat isn't fixing it. It is fixing it in a way that doesn't disrupt others and/or in a way that doesn't tie up the database for a very long time trying to unravel everything and/or in a way that doesn't leave a mess behind you.

If you are a student of ancient history, you will have heard of Alexander the Great and his solution for untying the Gordian Knot. His solution was to take out his sword and cut it apart. Well, the C&R method is analogous. You don't untangle the blocks of deleted data. Instead, you make a new empty file and then extract everything you wanted to keep into that new file, then delete the old file in its entirety. (That IS the way a Compact & Repair operates!) The "Template" method is simply a way to selectively delete the old pile of obsolete data, create a new file, and re-tie the knot.

Doc,
For this small BE database, why not just set the database to Compact On Close? Or maybe that's too much, since I'm the one importing the new file, just manually C&R after I import. I suppose since it's on the network so that would be a little risking, but I do back up the database BE at least twice a week. What are you thoughts on that? It would be easier than having to use a template.

 

[Weekleyba]

if a value contains commas, then you either need to use a different delimiter such as a semi colon or pipe or you should be specifying text delimiters for the text fields when the file is created. But it may be your source app does not have that ability.

Either way it is also not clear whether the data you are importing is all new data to replace all 'old' data. Or a mixture of both - i.e. records already imported plus new records. And as far as the already imported records are concerned, whether in a subsequent import they may have changed in some way which implies the need for an update rather than a delete/insert. Or perhaps a record already imported does not exist in the new import, implying the requirement is for it to be deleted.

At the moment you are causing 'big bloat' by deleting all and reimporting. Updating a record will cause a small amount of 'recoverable' bloat on C&R as will deleting a record. Whilst inserting new records will cause 'bloat' in the sense that they require space, but of course that space will not be recovered on a C&R.

Don't know the numbers but say you are importing around 10,000 records at a time. If the scenarios above apply, perhaps a 100 or so records require updating and maybe 100 deleting and 1000 are new. So very simplistically, at the moment your db doubles in size as a result of the import process. But by managing the import the bloat is reduced to around 1.2% of which 0.2% is recoverable through C&R.

Edit:


If that is your only input then nothing to stop you creating a new empty database rather than deleting in an existing database and importing to that and then replacing the original file.

CJ,
I am importing roughly 80K-100K records from the single Excel file. (80-100K of rows and only 22 columns)
Some records are new and some no longer exist. Thus, the reason to delete and import the new.
I'm basically asking Doc the same question. Why not C&R the BE database after importing? It seems to correct the bloating issue.
I'm sure there's a reason but, I'm not understanding it.

 

[CJ_London]

no reason not to compact repair after import - or as I suggested just create a new db and import to that. No need to delete, no need to compact repair.

 

[Weekleyba]

no reason not to compact repair after import - or as I suggested just create a new db and import to that. No need to delete, no need to compact repair.

True, if I have a new empty database that I import and then replace the existing BE database with it.
But if I simply import again to the existing BE, it starts to bloat.
My question is, why create a new empty database or a template, if you can just C&R the existing BE database after import?
This seems much easier and brings the size of db back to it's original. But I suppose the answer is trusting the C&R wouldn't mess something up...??
For either method, I need to ensure no one is working in their FE.
So what's the downside to C&R on close or manually doing it?

 

[CJ_London]

My question is, why create a new empty database or a template, if you can just C&R the existing BE database after import?

quicker and less prone to failure for some reason. Basically you are using a hammer to crack a nut. Better to avoid the bloat in the first place. Either by using insert/update/delete queries for the appropriate records or by creating a new db/template.

So what's the downside to C&R

not a huge amount. just a small increase for potential error.

Personally I don't recommend using on close because that it not within your control. Google to find out more e.g. this link

Access - VBA - Compact a Database | DEVelopers HUT

A brief discussion about compacting and sample illustrating how to accomplish it through VBA coding.

www.devhut.net

Might as well ask why bother with a database? why not just have your users reference the excel file directly - or just have a linked table, then all you do is swap the excel file over.

You don't have much data, so why bother compacting after each import? At circa 24Mb an import you can import around 80 times before hitting the 2Gb limit. So compacting once a week or month should be sufficient.

But it is up to you, it is your app. Your approach will work

 

[Weekleyba]

quicker and less prone to failure for some reason. Basically you are using a hammer to crack a nut. Better to avoid the bloat in the first place. Either by using insert/update/delete queries for the appropriate records or by creating a new db/template.

not a huge amount. just a small increase for potential error.

Personally I don't recommend using on close because that it not within your control. Google to find out more e.g. this link

Access - VBA - Compact a Database | DEVelopers HUT

A brief discussion about compacting and sample illustrating how to accomplish it through VBA coding.

www.devhut.net

Might as well ask why bother with a database? why not just have your users reference the excel file directly - or just have a linked table, then all you do is swap the excel file over.

You don't have much data, so why bother compacting after each import? At circa 24Mb an import you can import around 80 times before hitting the 2Gb limit. So compacting once a week or month should be sufficient.

But it is up to you, it is your app. Your approach will work

Thanks CJ. I feel better now. Ha.
I will not use the Compact on Close and I'll just compact it manually every month.
Thanks for your help!!