How to dispatch data from big access files to smaller ones

[nicoboss]

Hi guys,

I had an access application which was very useful, but I lost it when my PC crashed. And unfortunately I don't know coding, so I am stuck :-(
Here is the principle :

I have many access files. Including big ones, bigger than 2GB.
Each one of these access files contain only 1 table.
These files are used for storing data, but they are too big.
So I want to dispatch the data into many small files instead of 1 big one.
All the data should be kept, but stored in 10 small files instead of 1 big one.
If there are 50.000.000 lines in the original file, these 50.000.000 lines should be dispatched into the small files following the table of correspondance.
The structure of the table contained in the small files is the same as the original file structure.

So the access application should :



1. create 10 smaller files with the same name as the original one and "-1", "-2", "-3", "-4", ... "-10" at the end. If one of these small files already exist in the folder, the data should be added into this already existing file.
2. depending on the 8 first characters (generally composed of 2 letters, 1 space, 2 letters, 1 space, 2 letters) of the 1st field, each line should be dispatch into 1 of these 10 smaller files. I provide the table of correspondance which relates every sequence of 8 characters with the small file number. One specific line whose ID field contain "Flop_Tur" should be added to all 10 small files, except if this line is already in the small file in order to avoid duplicates.
3. in every small file, the "ID" field should be the primary key. If that's a problem, we can skip this step as I already have an application which does that.

I have attached an example of original mdb file, and the table of correspondance.dispatch correspondance.zip

As far as I can remember, the code was not too complex. I hope someone could help me to code this.

Thank you

Nico

[ranman256]

you dont need code,
just run 10 append queries
1 for each batch of data to the target table

(or upgrade to SQL server to remove the limit)

[nicoboss]

Hi ranman,

I have hundreds of mdb files I need to split, so I can't do it manually.
This is why I need to use a VBA macro.

[davegri]

I have hundreds of mdb files I need to split, so I can't do it manually.

So hundreds of mdb files multiplied by 10 equals thousands of files. If you succeed in splitting them out, how can you possibly manage that kind of result?

[isladogs]

I have many access files. Including big ones, bigger than 2GB.

If any of your Access databases are larger than 2GB, it is very unlikely you will be able to open them without corrupting the data and may not be able to open them at all.
I would suggest you make backup copies then try to compact the files from another database to bring them below 2GB

As already suggested, upsizing to SQL Server may be a better solution - the free Express version has a 10GB limit

[nicoboss]

So hundreds of mdb files multiplied by 10 equals thousands of files. If you succeed in splitting them out, how can you possibly manage that kind of result?

I get the data I need from these mdb files into excel, using a macro.

[nicoboss]

If any of your Access databases are larger than 2GB, it is very unlikely you will be able to open them without corrupting the data and may not be able to open them at all.
I would suggest you make backup copies then try to compact the files from another database to bring them below 2GB

As already suggested, upsizing to SQL Server may be a better solution - the free Express version has a 10GB limit

If I use a SQL database, can I get some of these data into excel ?
How to write such a query in excel? For example, if I need the value corresponding to an ID XXX, in a table YYY, how can I write a formula in excel which gets the value I need?
How long would such a query need?

[nicoboss]

Has anyone some ideas to write the code described in first post?

[davegri]

Has anyone some ideas to write the code described in first post?

Not enough info to yet to estimate effort, so just a few questions.
When you say "files" do you mean mdb files, actually databases?
What's the folder structure containing the hundreds of mdb files?
Are there more than one mdb file in a given folder? Are the already existing "dash suffix" files in the same folder as the original db? Are they named properly according to your specs?
What's the folder structure of your expected output? How are the old and new databases to be distributed within these folders?
Do you envision one new Converter database with one button click to manage all of this, or just have code that you can drop into an mdb that will split out that db into 10 new ones?
Is it OK for all the new databases to be accdb?

[nicoboss]

Hello davegri,

Thank you for helping me !

When you say "files" do you mean mdb files, actually databases?

I have mdb files, with one table only inside. This table is named "Table1"

What's the folder structure containing the hundreds of mdb files?

Currently they are all located in the same folder, but I am not sure it matters, as I can change their location to split them.

Are there more than one mdb file in a given folder?

yes

Are the already existing "dash suffix" files in the same folder as the original db?

There might be. If the "dash suffix" file already exists, then it doesn't need to be created and the data can be added to the table of this already existing file, except if this is additional ID is already in the small file in order to avoid duplicates..

Are they named properly according to your specs?

yes, i think so

What's the folder structure of your expected output?

All the small files we get can be stored in one same folder

How are the old and new databases to be distributed within these folders?

I thought the original mdb files could remain in their original folder and the small files we get could be sotred in a "results" folder for example.

Do you envision one new Converter database with one button click to manage all of this, or just have code that you can drop into an mdb that will split out that db into 10 new ones?

The application I used to have had a "Convert" button and once I pressed it it started splitting all the mdb files which were in the specific target folder. It was processing the bulk of files. This was perfect.

Is it OK for all the new databases to be accdb?

no, it needs to be .mdb files

[davegri]

To test this you will also need the files from the OPs post #1. You can copy example.mdb with different names to get more source MDBs.
This was an interesting exercise to code. It involved linking to outside MDB tables and creating new MDBs and transfering tables into them,
all the while creating folders to organize the output MDBs into.
A report is included with record counts for reconciliation.

I had to PM nicoboss to get his email address to send him the DB as it was originally too large to upload here. Then I discovered a LOT of data in temp tables, deleted that
to get the size down enough to put here.

I have core I7 with SSD, and it runs reasonably fast (less than a minute) creating 30 MDBs with about 450,000 records altogether. The OP apparently has MANY more than that.