Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Unanswered: Code to create linked table?

    I need to be able to create a new ODBC linked table in Access 2002 Runtime.
    Is there a way of invoking the Linked Tables dialog box, as if you had hit
    Get External Data > Link Tables... in the full version of Access?

    Alternatively, how would I go about creating an ODBC LINKED table with code?

    Thanks

    Nick

  2. #2
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: Code to create linked table?

    Originally posted by nickholmes
    I need to be able to create a new ODBC linked table in Access 2002 Runtime.
    Is there a way of invoking the Linked Tables dialog box, as if you had hit
    Get External Data > Link Tables... in the full version of Access?

    Alternatively, how would I go about creating an ODBC LINKED table with code?

    Thanks

    Nick
    With code:
    set dbLocal=currentdb
    set tdfLinked=dbLocal.CreateTableDef("New ODBC Linked Table")
    set tdfLinked.connect="ODBC;DSN=SOME_DEFINED_DSN;DATAB ASE=;"
    set tdfLinked.SourceTableName="REMOTE_TABLE_NAME"
    dbLocal.Tabledefs.Append tdfLinked
    dbLocal.Tabledefs.Refresh


    You'll have to have your DSN defined prior to executing this code

  3. #3
    Join Date
    Mar 2003
    Posts
    11
    Forgive me for my poor programming knowledge, but I have used the code you gave me, but cannot get it to work:

    =================================
    Public Function CREATEODBCTABLE()
    Dim tdfLinked As DAO.TableDef
    Dim db As DAO.Database

    Set dblocal = CurrentDb()
    Set tdfLinked = dblocal.CreateTableDef("STOCK")
    Set tdfLinked.Connect "ODBC;DSN=SAGELINE50; _ UID=user;PWD=password;DATABASE=;"
    Set tdfLinked.SourceTableName = "STOCK"

    db.TableDefs.Append tdfLinked
    db.TableDefs.Refresh

    End Function
    =================================

    At the Set tdfLinked.Connect line, I get an error message " Invalid Use of Property"

    Can you, or anyone else see what am I doing wrong?

    Thank You

    Nick

  4. #4
    Join Date
    Mar 2003
    Location
    London
    Posts
    40
    Originally posted by Nick Holmes

    At the Set tdfLinked.Connect line, I get an error message " Invalid Use of Property"

    Can you, or anyone else see what am I doing wrong?

    Thank You

    Nick
    Sorry Nick, my bad

    Public Function CREATEODBCTABLE()
    Dim tdfLinked As DAO.TableDef
    Dim db As DAO.Database

    Set dblocal = CurrentDb()
    Set tdfLinked = dblocal.CreateTableDef("STOCK")
    tdfLinked.Connect "ODBC;DSN=SAGELINE50; _ UID=user;PWD=password;DATABASE=;"
    tdfLinked.SourceTableName = "STOCK"

    db.TableDefs.Append tdfLinked
    db.TableDefs.Refresh

    End Function

  5. #5
    Join Date
    Mar 2003
    Posts
    11
    Thank you, that works perfectly.

    The other problem is that I may not know the name of the foreign table. What I need to do is list all tables within a data source...

    So the user picks a DSN, enters his/her username & password for it, and then gets a listbox or something with all available tables from the DSN...

    Any Ideas?

    Thanks

    Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •