Make table with variable name

[lisaharris]

I have a table in Access 2003 that is compiled from several linked SQL tables. The access table contains records that are identified by different batch numbers.

For example, the batches could be named 1201, 1202, 1203, 1204, 1205. There could be anywhere from 1 to hundreds of records for each batch number in the Access table, that need to be split into separate tables for exporting. (The target system can't handle multiple batches in one file.)

What I need to do is pull the table apart and create up to five tables (one for each batch) and include only the records that have that batch number. This will need to be done in an automated fashion.

In kicking this around in my head, what I'm planning on is vba that uses a "for each" increment, but is it possible to use the contents of the batch field to name the table?

Any suggestions / ideas greatly appreciated!!

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[PWise]

Dim mydb As Database
Dim rst As Recordset
Set mydb = CurrentDb
Set rst = mydb.OpenRecordset("Select Distinct BatchNum From tablename")
Do While Not rst.EOF

mydb.Execute "SELECT tablename.* INTO " & rst!BatchNum & " FROM tablename WHERE tablename.BatchNu)=" & rst!BatchNum
rst.MoveNext
Loop

 

[sxschech]

Create a Select Distinct query as a recordset on the batch number and then use that as the source for your loop (while not end of file rs.movenext), the where clause (batchno=rs!batchno) and the output table name ("tbl" & rs!batchno).