Welcome to SPN

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

Sign Up Now!

Linking from code

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

Tags:
  1. Domac

    Domac
    Expand Collapse
    Guest

    Hi,

    I am currently linking using method below:

    while rst.eof=false

    Set tdef = CurrentDb.CreateTableDef(rst!TableName)

    tdef.Connect = rst!LinkToBase
    tdef.SourceTableName = rst!LinkTableName

    CurrentDb.TableDefs.Append tdef


    wend

    Problem is that I am linking about 80 tables each time user log in! It takes
    about 1minute for linking to finish!

    Can I append those tableDefs to some Collection object and then append whole
    collection to CurrentDb.TableDefs???

    Would it be faster???

    Users are 'killing' me ! (30 of them)!

    Please help me to make linking faster!


    Thanks,
    Domagoj
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    One trick is to open a database variable on the back end before your link.
    Although the code does not actually use the variable, it holds the file
    open, and speeds up the process considerably. This kind of thing:
    Dim dbData As DAO.Database
    Set dbData = OpenDatabase("C:\MyPath\MyFile.mdb")
    'your linking code here
    dbData.Close
    Set dbData = Nothing

    A more usual approach is to only link the tables that need linking, i.e.
    don't link them all again unless there are specific new tables that have to
    be added, linked tables that have to be dropped, of the path is different
    (in which place you can just change the Connect string of the TableDef.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Domac" <dd@dd.cc> wrote in message
    news:O2QOGuyqGHA.4508@TK2MSFTNGP04.phx.gbl...
    >
    > I am currently linking using method below:
    >
    > while rst.eof=false
    >
    > Set tdef = CurrentDb.CreateTableDef(rst!TableName)
    >
    > tdef.Connect = rst!LinkToBase
    > tdef.SourceTableName = rst!LinkTableName
    >
    > CurrentDb.TableDefs.Append tdef
    >
    >
    > wend
    >
    > Problem is that I am linking about 80 tables each time user log in! It
    > takes about 1minute for linking to finish!
    >
    > Can I append those tableDefs to some Collection object and then append
    > whole collection to CurrentDb.TableDefs???
    >
    > Would it be faster???
    >
    > Users are 'killing' me ! (30 of them)!
    >
    > Please help me to make linking faster!
     
  4. Domac

    Domac
    Expand Collapse
    Guest

    Solution you have suggested to me works!!

    It is about 8x faster.

    I have tried before to change only the "Connect" property of tabledef object
    in currentdb.tabledefs collection, but it didn't linked to it.
    Old table data was shown instead of "new" data from another database!

    Is there usable solution for changing only the "Connect" property??


    You have helped me a lot , my users are now satisfied!!!



    Thanks!
    Domagoj




    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%2303KLAzqGHA.5012@TK2MSFTNGP03.phx.gbl...
    > One trick is to open a database variable on the back end before your link.
    > Although the code does not actually use the variable, it holds the file
    > open, and speeds up the process considerably. This kind of thing:
    > Dim dbData As DAO.Database
    > Set dbData = OpenDatabase("C:\MyPath\MyFile.mdb")
    > 'your linking code here
    > dbData.Close
    > Set dbData = Nothing
    >
    > A more usual approach is to only link the tables that need linking, i.e.
    > don't link them all again unless there are specific new tables that have
    > to be added, linked tables that have to be dropped, of the path is
    > different (in which place you can just change the Connect string of the
    > TableDef.)
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Domac" <dd@dd.cc> wrote in message
    > news:O2QOGuyqGHA.4508@TK2MSFTNGP04.phx.gbl...
    >>
    >> I am currently linking using method below:
    >>
    >> while rst.eof=false
    >>
    >> Set tdef = CurrentDb.CreateTableDef(rst!TableName)
    >>
    >> tdef.Connect = rst!LinkToBase
    >> tdef.SourceTableName = rst!LinkTableName
    >>
    >> CurrentDb.TableDefs.Append tdef
    >>
    >>
    >> wend
    >>
    >> Problem is that I am linking about 80 tables each time user log in! It
    >> takes about 1minute for linking to finish!
    >>
    >> Can I append those tableDefs to some Collection object and then append
    >> whole collection to CurrentDb.TableDefs???
    >>
    >> Would it be faster???
    >>
    >> Users are 'killing' me ! (30 of them)!
    >>
    >> Please help me to make linking faster!

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Good news!

    Here's an example of re-linking by changing the Connect property:
    http://allenbrowne.com/ser-13.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Domac" <dd@dd.cc> wrote in message
    news:%23qeHsYArGHA.3256@TK2MSFTNGP04.phx.gbl...
    > Solution you have suggested to me works!!
    >
    > It is about 8x faster.
    >
    > I have tried before to change only the "Connect" property of tabledef
    > object in currentdb.tabledefs collection, but it didn't linked to it.
    > Old table data was shown instead of "new" data from another database!
    >
    > Is there usable solution for changing only the "Connect" property??
    >
    >
    > You have helped me a lot , my users are now satisfied!!!
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:%2303KLAzqGHA.5012@TK2MSFTNGP03.phx.gbl...
    >> One trick is to open a database variable on the back end before your
    >> link. Although the code does not actually use the variable, it holds the
    >> file open, and speeds up the process considerably. This kind of thing:
    >> Dim dbData As DAO.Database
    >> Set dbData = OpenDatabase("C:\MyPath\MyFile.mdb")
    >> 'your linking code here
    >> dbData.Close
    >> Set dbData = Nothing
    >>
    >> A more usual approach is to only link the tables that need linking, i.e.
    >> don't link them all again unless there are specific new tables that have
    >> to be added, linked tables that have to be dropped, of the path is
    >> different (in which place you can just change the Connect string of the
    >> TableDef.)
    >>
    >> "Domac" <dd@dd.cc> wrote in message
    >> news:O2QOGuyqGHA.4508@TK2MSFTNGP04.phx.gbl...
    >>>
    >>> I am currently linking using method below:
    >>>
    >>> while rst.eof=false
    >>>
    >>> Set tdef = CurrentDb.CreateTableDef(rst!TableName)
    >>>
    >>> tdef.Connect = rst!LinkToBase
    >>> tdef.SourceTableName = rst!LinkTableName
    >>>
    >>> CurrentDb.TableDefs.Append tdef
    >>>
    >>>
    >>> wend
    >>>
    >>> Problem is that I am linking about 80 tables each time user log in! It
    >>> takes about 1minute for linking to finish!
    >>>
    >>> Can I append those tableDefs to some Collection object and then append
    >>> whole collection to CurrentDb.TableDefs???
    >>>
    >>> Would it be faster???
    >>>
    >>> Users are 'killing' me ! (30 of them)!
    >>>
    >>> Please help me to make linking faster!
     

Share This Page