Make table loop?

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi,

I have a very large table that I want to split into
chunks so I an paste it into excel. The table is lookup
table it onlt has two colmns but is massive. The two cols
are code and description. What I would like to do is
create some sql in VBA that will loop round and create a
multiple tables based on a set criteria. The criteria
would be on the codes bit of the table. I know that the
codes start with a letter, from A to Z, all the A's are a
prticulr group, as are all the B's etc.so that wouold be
a good way of breaking it down, does anybody know of a
way to do this, otherwise I will just do lots of make
table queries.

Thanks
 
Daniel,

Try something like:

For i = 65 To 90
strSQL = "SELECT * INTO " & Chr(i) & "_CHUNK"
strSQL = strSQL & " FROM MyTable"
strSQL = strSQL & " WHERE IDField Like '" & Chr(i) & "*'"
CurrentDb.Execute strSQL
Next

HTH,
Nikos
 
Daniel

"Massive" has different meanings to different folks ... have you already
tried exporting the table to Excel?

Have you considered using a query instead of making temporary tables? You
could build a new query that gets all the rows where the code is:

Like A*

When that query returns the rows you want, export THAT query's output to
Excel. Modify the query to find the codes starting with B (Like B*),
export, and so on.

While this would take 26 "cycles", if you are only doing this one time, it
would probably take less time than building/testing/running vb code.
 
Back
Top