Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2012
    Posts
    7

    Unanswered: Linking the tables using DSN file through VBA

    Hi,

    We have a access file in a shared folder where busines users open it through citrix site. The backend for this app is SQL server and used to link the tables with DSN file through 'table link manager'.
    The DSN file looks as below:
    [ODBC]
    DRIVER=SQL Server
    UID=user1
    PWD=password1
    DATABASE=MyDB
    APP=Microsoft Office 2005
    SERVER=111.111.111.111,53314
    I want to write VBA code to read this DSN file from shared location and link the tables automatically whenver the user tries to open the access file. VBA code should read server details and login details for linking the tables.

    Can you please help me out as I am new to VBA. Thanks in advance.

  2. #2
    Join Date
    Dec 2012
    Posts
    7
    Hi All,

    Can anyone please reply on my quesry as it is very urgent. Thanks.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If your front end is an ADP file, it shouldn't need code to update the tables as it will point directly to them.

    If your front end is an MDB file, how is it currently refreshed?

    (Just as an aside, posting a request for urgent help on New Year's Eve on a free forum is probably not going to get results as fast as you need them!)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Dec 2012
    Posts
    7
    Hi Weejas,

    My front is MDB file and we manually link the tables using 'table link manager'. But now onwards we would like to do that programmatically using VBA in a way that all the tables should re-linked with backend tables whenever the MDB file is opened by the user.
    My VBA code should read all the details including UID and PWD from the DSN file from shared pat and re-link the tables.
    Can you please provide me the complete VBA code which satisfies my requirement as I am not so good in VBA. Thanks

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Linked tables within an MDB file are just that - linked. They should not need refreshing, unless the back end has been moved.

    I have no idea how to extract DSN settings from a PC, as I have never needed to.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is some code in the Access Developers handbook to do that. if you are a serious developer I'd strongly reccomend both those books.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm not a serious developer! I just pretend to be one at work...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Dec 2012
    Posts
    7
    Hi Weejas,

    I think you got confused about my requirement.
    Front-end is MDB and backend is SQL Server 2008. I need to link the MDB tables with SQL tables by reading the server details from DNS file using VBA code. Please can anyone provide me the code for the same. Thanks.

  9. #9
    Join Date
    Dec 2012
    Posts
    7
    Hi,

    Please provide me the VBA code for my requirement. Thanks.

  10. #10
    Join Date
    Dec 2012
    Posts
    7
    Finally I got the code to link the tables, I have successfully refreshed the table links but facing new issue now.
    Table name in the acces db is ABC MERGE 1
    Table name in the sql server is ABC_MERGE_1

    When I am running any query after linking the tables, I am getting the error message as "ODBC Connection failes, Invalid object name ABC MERGE 1. #208"

    It is running fine when I give the table name as ABC_MERGE_1. Can anyone please let me know the reson for this issue? What needs to be changed?

Posting Permissions

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