Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page