Splitting a large table into many smaller ones

[AlexD]

Hi,

To avoid the mind-numbing tedium of have to use make-table queries loads of times, is there a quick (probably VBA-related) way to split a large Access table, of about 350000 records, down into 93 smaller tables, based on a key code field that identifies each group of records e.g. GBW102, GBE999, etc?

Any help much appreciated.

thanks,

Alex

 

[llkhoutx]

Assuming that you having a method in mind to query you "big table," that method can be easily automated to create your "93 tables.' I further assume that you've or can create a table with that criteria and a one string field table. I've assumed that the left 3 characters of each BigTable record "Key" indicates what table the record goes into.

dim db as dao.database
dim rsCriteria as dao.recordset
dim stbName as string
dim sSQL as string
set db=currentdb
set rsCriteria = db.openrecordset("tb93Tablename",dbopensnapshot)
rsCriteria.movefirst
do until rsCriteria.eof
stbname="tb" & rscriteria.fields(0)
sSQL = "SELECT * FROM BIGTABLE WHERE LEFT(Key,3)= '" & rscriteria.fields(0) & "'"
rscriteria.movenext
loop
rsCriteria.close
db.close
set rsCriteria=nothing
set db=Nothing

Not that these tables are not indexed. You haven't said how you want the records indexed, so I haven't included that solution.

 

[RV]

based on a key code field that identifies each group of records e.g. GBW102, GBE999, etc?

Your phrase and more particularly, the use of the word "group" seems to indicate that you've got a flat table = a not adequate normalized database structure.
Instead of splitting up your table, I suggest you review your original design.

RV

 

[llkhoutx]

350,000 records are too many for Access to be relatively fast.

 

[AlexD]

Guys - thanks for your responses.

It is a flat table, unfortunately this was a neccessity for processing requirements. The data is basic names/address/tel nos with each record having a URN ID. There are 93 key codes identifying the 93 different different groups or records.

llkhoutx - not quite sure what you mean by 'one string field table'. Are you assuming that the character part of the keycode is unique because this isn't the case. Sorry, should have included more examples:

GBE816
GBE823
GBW800
GBW6L2
GBC630
GBC703 + GBW618
GBN520
GBN5A2
etc

cheers,
Alex

 

[RV]

Are you assuming that the character part of the keycode is unique because this isn't the case.

So how would you be able to split the table in a smart and sensible way then?
It's either normalising your datastructure are structuring the data in your tabel.
The choice is yours

RV

 

[The_Doc_Man]

The data is basic names/address/tel nos with each record having a URN ID

If this is really ALL that is in the table, it is not suitable for splitting. I.e. it might already be normalized.

If your issue is performance, look to indexing methods. Be sure that the fields you query most often are equipped with indexes, whether the field is unique or can be duplicated.

 

[Pat Hartman]

Why are you physically splitting the table? You should use a query to select whatever section of data you want to process. Add a non-unique index for the field that groups the data. This will speed up your queries.