create multiple tables at once - HELP please

[Dan]

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;

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

 

[Nikos Yannacopoulos]

Dan,

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

Sub Create_Local_Tables()
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

 

[Dan]

Very cool, thanks so much!!

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

 

[Nikos Yannacopoulos]

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
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