create multiple tables at once - HELP please

Hello -
I have a db which contains multiple linked tables; named dbo_211,
dbo_212 dbo_13.

I know how to create individual Make Table Queries to replicate each of
these locally.

My individual SQL statements are:

SELECT dbo_211.* INTO [dbo_211 - MT] FROM dbo_211;

SELECT dbo_212.* INTO [dbo_212 - MT] FROM dbo_212;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13590-create-multiple-tables-at-once-help.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590

SELECT dbo_213.* INTO [dbo_213 - MT] FROM dbo_213;

Is there a way to create multiple tables at one time instead of running
each of these seperately? (not using a macro). Something like one
query/code that does it all at once? If so, where would I do this at
in Access?

Thanks!
Dan

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Dan,

Paste the following code in a (new or existing) general module, and run it.

Sub Create_Local_Tables()
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strTable As String
Dim strSQL As String
strSQL = "SELECT Name FROM MSysObjects WHERE Type = 4"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
If Left(rst.Fields(0), 4) = "dbo_" Then
strTable = rst.Fields(0)
strSQL = "SELECT " & strTable & ".* INTO [" & _
strTable & " - MT] FROM " & strTable & ";"
db.Execute strSQL, dbFailOnError
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

HTH,
Nikos

Very cool, thanks so much!!

Can you tell me what the part "Type = 4" actually is?

Nikos Yannacopoulos wrote:
> Dan,
>
> Paste the following code in a (new or existing) general module, and run it.
>
> Sub Create_Local_Tables()
> Dim db As DAO.Database
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
> Dim rst As DAO.Recordset
> Dim strTable As String
> Dim strSQL As String
> strSQL = "SELECT Name FROM MSysObjects WHERE Type = 4"
> Set db = CurrentDb
> Set rst = db.OpenRecordset(strSQL)
> rst.MoveFirst
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
> Do Until rst.EOF
> If Left(rst.Fields(0), 4) = "dbo_" Then
> strTable = rst.Fields(0)
> strSQL = "SELECT " & strTable & ".* INTO [" & _
> strTable & " - MT] FROM " & strTable & ";"
> db.Execute strSQL, dbFailOnError
> End If
> rst.MoveNext
> Loop
> rst.Close
> Set rst = Nothing
> Set db = Nothing
> End Sub
>
> HTH,
> Nikos

Dan,

Type 4 is linked SQL server tables (I guessed from the dbo_ prefix).
MSysObjects is a system table which holds information on all database
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13590
objects; Type is the field which identifies object type (e.g. native vs.
linked tables, queries, forms, reports etc.). If you are interested in
having a look at the table, just go Tools > Options > View and check the
Hidden Objects option. The table is read-only, so you can't accidentally
damage it.

HTH,
Nikos