Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    13

    Unanswered: Refreshing Linked SQL Server tables via ADO?

    Ok, here's the scenario:


    1) Database is opened for the first time.

    2) Dates are grabbed from about 20 SQL servers

    3) If the tables aren't linked yet, about 20 SQL server links are created using DoCmd.TransferDatabase

    4) When I close the database or want to refresh the links on those tables, I get a login error when I try to connect. Maybe my connection string is bad or something, but I'm not sure how to refresh them.


    How do I refresh these links with a username and password? The L/P for all these servers are the same, so I am looking to loop thru a list once and refresh all of them.


    I've tried using the SQL Server connection, but my SELECT statement makes use of Mid and some other functions in the module in my DB, and it won't allow me to use this if I use the SQL Server connection in my Recordset.Open function.

    Any ideas? I can provide more info if need be... thank you.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Linked tables

    Here's a piece of code which may or may not help you. It basically creates DSNs that don't exist or refreshes DSNs that do exist. You may be able to modify it to fit your needs.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Here's a piece of code I created for refreshing links for SQL Server and Oracle.

    It's in DAO as appose to ADO, but might give you some ideas.

    To use it link some tables, then go into the code for 'frmRefreshLinks' and change your connection settings in there, then run the form simple as that.
    Attached Files Attached Files

Posting Permissions

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