Welcome to SPN

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

Sign Up Now!

Programmitcally Linking SQL Server Tables From Within Access

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

  1. KML

    Expand Collapse

    Hello all,

    I'm using the following code to attempt to link a table from a form
    within Access (the link only needs to be established once as a
    workaround for another issue, which I won't go into).

    Dim strDatabase As String
    Dim objCurrentDB As Database
    Dim objAccessTableDef As TableDef
    Dim objExternalDB As Database
    Dim strConnect As String
    Dim strTable As String

    strTable = InputBox("Please enter the table name to link to",
    "Enter Table Name")
    SOURCE=KrisTest;Use Procedure for Prepare=1;Auto Translate=True;Packet
    Size=4096;Workstation ID=KRISLPC"

    Set objCurrentDB = CurrentDb
    Set objExternalDB = OpenDatabase("", False, False, strConnect)

    Set objAccessTableDef = objCurrentDB.CreateTableDef(strTable,
    objAccessTableDef.Connect = objExternalDB.Connect
    objAccessTableDef.SourceTableName =
    objCurrentDB.TableDefs.Append objAccessTableDef

    Set objAccessTableDef = Nothing
    Set objExternalDB = Nothing
    Set objCurrentDB = Nothing

    The code does work, but it's not fully automated. I am prompted to
    "Select Data Source", and the code will establish the link if I select
    it, but I thought my connection string was already specifying the Data
    Source so I'm kinda confused. I suspect the code doesn't like my
    connection string for some reason. Really all I need is a connection
    string that works for the following criteria:

    System DSN is already set up and working - Name: "KrisTest"
    SQL Server 8.0 Database, database name is also "KrisTest"
    Database is using Windows integrated security
    I'm using Access 2002

    Thanks a lot!
  2. Loading...

Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     

Share This Page