Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     

Share This Page