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

    KML
    Expand Collapse
    Guest

    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")
    strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
    SECURITY INFO=FALSE;INITIAL CATALOG=KrisTest;DATA
    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,
    dbAttachSavePWD)
    objAccessTableDef.Connect = objExternalDB.Connect
    objAccessTableDef.SourceTableName =
    objExternalDB.TableDefs(strTable).Name
    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...


Share This Page