Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

create multiple tables at once - HELP please

Discussion in 'Information Technology' started by Dan, Jul 28, 2006.

  1. Dan

    Dan
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  4. Dan

    Dan
    Expand Collapse
    Guest

    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
    > 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
     
  5. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     

Share This Page