Welcome to SPN

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

Sign Up Now!

Coding to create linked tables

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

  1. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Please consider this VBA code:

    Dim MyDatabase As Database, i As Integer

    Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    For i = 0 To MyDatabase.TableDefs.Count - 1
    If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
    Debug.Print MyDatabase.TableDefs(i).Name
    MyDatabase.TableDefs(i).Name = "NK" &
    MyDatabase.TableDefs(i).Name
    CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
    End If
    Next i

    There is a surprising result to this code. The table names in the NWNKBE
    database are actually being changed! I had expected the local copy of it to
    be changed, not the original.

    What I'd like to do is to create a set of uniquely named linked tables from
    a set of 3 databases, all verisons of Northwind. So, they all have the same
    set of tables, names and all. I just want to have uniquely named links to
    them. I can do that manually quite easily.

    Thanks for your advice on this. I'll be looking at all the books for
    information, too.

    Tom Ellison
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Kids "using coding skills to hack" Friends on Games Breaking News Feb 10, 2013
    Opinion Mayan tablet decoding rules out 2012 apocalypse Breaking News Dec 1, 2011
    Sikh News Turban Tying Event Creates Buzz At University Of Alberta - CBC.ca Breaking News Sep 28, 2016
    Sikh News Mohali Firm Creates Portal To Help Kids Learn About Sikh And Punjabi Culture, Approaches Breaking News Sep 17, 2016
    Looking For A Device To Create Digital Paintings Or Art Information Technology Feb 10, 2016

  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Tom.

    Tom Ellison wrote:
    > Please consider this VBA code:
    >
    > Dim MyDatabase As Database, i As Integer
    >
    > Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    > For i = 0 To MyDatabase.TableDefs.Count - 1
    > If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
    > Debug.Print MyDatabase.TableDefs(i).Name
    > MyDatabase.TableDefs(i).Name = "NK" &
    > MyDatabase.TableDefs(i).Name
    > CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
    > End If
    > Next i
    >
    > There is a surprising result to this code. The table names in the NWNKBE
    > database are actually being changed! I had expected the local copy of it
    > to be changed, not the original.
    >
    > What I'd like to do is to create a set of uniquely named linked tables
    > from a set of 3 databases, all verisons of Northwind. So, they all have
    > the same set of tables, names and all. I just want to have uniquely named
    > links to them. I can do that manually quite easily.
    >
    > Thanks for your advice on this. I'll be looking at all the books for
    > information, too.


    You can't append TableDef objects that hat already been appended.
    Try something like this:

    Dim MyDatabase As Database, dbs As Database
    Dim tdfSource As TableDef, tdf As TableDef
    Dim strName As String

    Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    Set dbs = CurrentDb
    For Each tdfSource In MyDatabase.TableDefs
    strName = tdfSource.Name
    If Not strName Like "MSys*" Then
    Debug.Print strName
    Set tdf = dbs.CreateTableDef("NK" & strName)
    tdf.Connect = ";DATABASE=" & MyDatabase.Name
    tdf.SourceTableName = strName
    dbs.TableDefs.Append tdf
    Set tdf = Nothing
    End If
    Next
    MyDatabase.Close
    Set MyDatabase = Nothing
    Set dbs = Nothing

    --
    Regards,
    Wolfgang
     
  4. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Thank you! I'll be giving this a try later.

    Tom Ellison


    "Wolfgang Kais" <firstoffirstname.lastname@gmx.de> wrote in message
    news:unrE34AeGHA.2188@TK2MSFTNGP04.phx.gbl...
    > Hello Tom.
    >
    > Tom Ellison wrote:
    >> Please consider this VBA code:
    >>
    >> Dim MyDatabase As Database, i As Integer
    >>
    >> Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    >> For i = 0 To MyDatabase.TableDefs.Count - 1
    >> If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
    >> Debug.Print MyDatabase.TableDefs(i).Name
    >> MyDatabase.TableDefs(i).Name = "NK" &
    >> MyDatabase.TableDefs(i).Name
    >> CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
    >> End If
    >> Next i
    >>
    >> There is a surprising result to this code. The table names in the NWNKBE
    >> database are actually being changed! I had expected the local copy of it
    >> to be changed, not the original.
    >>
    >> What I'd like to do is to create a set of uniquely named linked tables
    >> from a set of 3 databases, all verisons of Northwind. So, they all have
    >> the same set of tables, names and all. I just want to have uniquely
    >> named links to them. I can do that manually quite easily.
    >>
    >> Thanks for your advice on this. I'll be looking at all the books for
    >> information, too.

    >
    > You can't append TableDef objects that hat already been appended.
    > Try something like this:
    >
    > Dim MyDatabase As Database, dbs As Database
    > Dim tdfSource As TableDef, tdf As TableDef
    > Dim strName As String
    >
    > Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    > Set dbs = CurrentDb
    > For Each tdfSource In MyDatabase.TableDefs
    > strName = tdfSource.Name
    > If Not strName Like "MSys*" Then
    > Debug.Print strName
    > Set tdf = dbs.CreateTableDef("NK" & strName)
    > tdf.Connect = ";DATABASE=" & MyDatabase.Name
    > tdf.SourceTableName = strName
    > dbs.TableDefs.Append tdf
    > Set tdf = Nothing
    > End If
    > Next
    > MyDatabase.Close
    > Set MyDatabase = Nothing
    > Set dbs = Nothing
    >
    > --
    > Regards,
    > Wolfgang
    >
     
  5. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    Tom,

    MyDatabase in MyDatabase.TableDefs(i).Name = refers to D:\NWNKBE.mdb so your
    code is renaming the tables in D:\NWNKBE.mdb.

    Consider using TransferDataBase to link all the tables in D:\NWNKBE.mdb to
    your local database. You can assign the names you want for the linked tables
    directly in the TransferDatabase method.

    DoCmd.TransferDatabase [transfertype], databasetype, databasename[,
    objecttype], source, destination[, structureonly][, saveloginid]

    Assign the names you want for the linked tables in the destination
    parameter.


    --
    PC Datasheet
    Your Resource For Help With Access, Excel And Word Applications
    Over 1175 users have come to me from the newsgroups requesting help
    resource@pcdatasheet.com




    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uK7NSd$dGHA.3888@TK2MSFTNGP02.phx.gbl...
    > Please consider this VBA code:
    >
    > Dim MyDatabase As Database, i As Integer
    >
    > Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    > For i = 0 To MyDatabase.TableDefs.Count - 1
    > If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
    > Debug.Print MyDatabase.TableDefs(i).Name
    > MyDatabase.TableDefs(i).Name = "NK" &
    > MyDatabase.TableDefs(i).Name
    > CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
    > End If
    > Next i
    >
    > There is a surprising result to this code. The table names in the NWNKBE
    > database are actually being changed! I had expected the local copy of it
    > to be changed, not the original.
    >
    > What I'd like to do is to create a set of uniquely named linked tables
    > from a set of 3 databases, all verisons of Northwind. So, they all have
    > the same set of tables, names and all. I just want to have uniquely named
    > links to them. I can do that manually quite easily.
    >
    > Thanks for your advice on this. I'll be looking at all the books for
    > information, too.
    >
    > Tom Ellison
    >
    >
     
  6. StopThisAdvertising

    StopThisAdvertising
    Expand Collapse
    Guest

    "PC Datasheet" <NoSpam@Spam.Com> schreef in bericht news:yOZ9g.4736$u4.425@newsread1.news.pas.earthlink.net...

    --
    > PC Datasheet
    > Your Resource For Help With Access, Excel And Word Applications 'Resource ????
    > Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
    > resource@pcdatasheet.com


    --
    To Steve:
    You still don't get it? No-one wants your advertising/job hunting here!
    Over 850 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

    To the original poster:
    Most people here have a common belief that the newsgroups are for *free exchange of information*.
    But Steve is a notorious job hunter in these groups, always trying to sell his services.
    And he is known here as a shameless liar with no ethics at all.

    Before you intend to do business with him look at:
    http://home.tiscali.nl/arracom/whoissteve.html

    Arno R
     
  7. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Wolfgang:

    I have a version of what you provided working now.

    Tom Ellison


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:uxsEL$AeGHA.3888@TK2MSFTNGP04.phx.gbl...
    > Thank you! I'll be giving this a try later.
    >
    > Tom Ellison
    >
    >
    > "Wolfgang Kais" <firstoffirstname.lastname@gmx.de> wrote in message
    > news:unrE34AeGHA.2188@TK2MSFTNGP04.phx.gbl...
    >> Hello Tom.
    >>
    >> Tom Ellison wrote:
    >>> Please consider this VBA code:
    >>>
    >>> Dim MyDatabase As Database, i As Integer
    >>>
    >>> Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    >>> For i = 0 To MyDatabase.TableDefs.Count - 1
    >>> If Left(MyDatabase.TableDefs(i).Properties(0), 4) <> "MSys" Then
    >>> Debug.Print MyDatabase.TableDefs(i).Name
    >>> MyDatabase.TableDefs(i).Name = "NK" &
    >>> MyDatabase.TableDefs(i).Name
    >>> CurrentDb.TableDefs.Append MyDatabase.TableDefs(i)
    >>> End If
    >>> Next i
    >>>
    >>> There is a surprising result to this code. The table names in the
    >>> NWNKBE database are actually being changed! I had expected the local
    >>> copy of it to be changed, not the original.
    >>>
    >>> What I'd like to do is to create a set of uniquely named linked tables
    >>> from a set of 3 databases, all verisons of Northwind. So, they all have
    >>> the same set of tables, names and all. I just want to have uniquely
    >>> named links to them. I can do that manually quite easily.
    >>>
    >>> Thanks for your advice on this. I'll be looking at all the books for
    >>> information, too.

    >>
    >> You can't append TableDef objects that hat already been appended.
    >> Try something like this:
    >>
    >> Dim MyDatabase As Database, dbs As Database
    >> Dim tdfSource As TableDef, tdf As TableDef
    >> Dim strName As String
    >>
    >> Set MyDatabase = OpenDatabase("D:\NWNKBE.mdb")
    >> Set dbs = CurrentDb
    >> For Each tdfSource In MyDatabase.TableDefs
    >> strName = tdfSource.Name
    >> If Not strName Like "MSys*" Then
    >> Debug.Print strName
    >> Set tdf = dbs.CreateTableDef("NK" & strName)
    >> tdf.Connect = ";DATABASE=" & MyDatabase.Name
    >> tdf.SourceTableName = strName
    >> dbs.TableDefs.Append tdf
    >> Set tdf = Nothing
    >> End If
    >> Next
    >> MyDatabase.Close
    >> Set MyDatabase = Nothing
    >> Set dbs = Nothing
    >>
    >> --
    >> Regards,
    >> Wolfgang
    >>

    >
    >
     

Share This Page