Exporting from1 Super Table into 42 sub tables

M

Matt_Fairfield

hey all
I am not having any luck with this and am hoping someone already has
something build similar.
I have a unique ID field in the Super table and want to export this into the
subtables. (42 differentsubtables)
the common fields in the super table and subtables are
GPS_Super.mslink = Tablename.mslink (subtables) and GPS_Super.tablename =
(the acutally table name in the DB).
These 2 fields combined is a unique identifier. there will be no duplicates.
If someone can help me ASAP that would be great
Thanks
Authored by: PtboGiser
 
D

Dale Fye

Why would even you want to do this? It makes querying your database so much
more difficult. Given that you are set on doing this, the following ought to
handle it.

Private Sub SplitData

Dim strSQL as string
Dim rs as dao.recordset

strSQL = "SELECT DISTINCT TableName FROM GPS_Super"
set rs = currentdb.openrecordset(strsql)

While not rs.eof

strSQL = "SELECT * INTO " & rs("TableName") _
& "FROM GPS_Super " _
& "WHERE [TableName] = " & chr$(34) & rs("TableName") &
chr$(34)
currentdb.execute strSQL
rs.movenext
Wend
rs.close
set rs = nothing

End Sub

HTH
Dale
 
S

Steve

You can do this in code by cycling through the tables collection to get the
42 tables and at each table run an SQL that appends your unique ID field to
the table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
Top