Welcome to SPN

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

Sign Up Now!

DSN auto update?

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

Tags:
  1. Vern Rabe

    Vern Rabe
    Expand Collapse
    Guest

    We have several hundred mdb databases, created with Access 2000, 2002, and
    2003. They all use system DSNs to gain access to a central SQL Server, via
    both linked tables and pass through queries. The system DSNs use SQL Server
    authentication. The database that they access is being moved to another SQL
    Server, and once there, we want all authentication to be via Windows
    Authentication. We are trying to make the transition as seamless as possible.
    So far, this is (was?) our plan:

    1. Create a file DSN on a network share, specifying trusted connection.
    2. Convert all mdb's to use this instead of the local system DSN.
    3. When the SQL Server database gets moved, modify the file DSN to reflect
    the new servername.

    This doesn't work because Access caches the DSN information, and apparently
    only through the process of re-linking (very manual process) will the new DSN
    information be used.

    How can we make this move more seamless?

    Thanks
    Vern Rabe
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Is Autocomplete Evil? Some Women Take A Hard Look at Google ! Breaking News Nov 7, 2013
    Sikh News AutoZone sued for firing Everett Sikh Breaking News Sep 29, 2010
    India Singh: Centre Ready To Give Kashmir Autonomy Breaking News Aug 11, 2010
    India Paste tariff cards behind driver’s seat: Transport dept to automen Breaking News Jul 21, 2010
    Gursharan Kaur: India's First Lady's Autobiography Sikh Personalities Jun 1, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Why not try DSN-less? See
    http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    news:A2EC0A07-A629-4F9F-ADF7-5393B8DFCF5C@microsoft.com...
    > We have several hundred mdb databases, created with Access 2000, 2002, and
    > 2003. They all use system DSNs to gain access to a central SQL Server, via
    > both linked tables and pass through queries. The system DSNs use SQL
    > Server
    > authentication. The database that they access is being moved to another
    > SQL
    > Server, and once there, we want all authentication to be via Windows
    > Authentication. We are trying to make the transition as seamless as
    > possible.
    > So far, this is (was?) our plan:
    >
    > 1. Create a file DSN on a network share, specifying trusted connection.
    > 2. Convert all mdb's to use this instead of the local system DSN.
    > 3. When the SQL Server database gets moved, modify the file DSN to reflect
    > the new servername.
    >
    > This doesn't work because Access caches the DSN information, and
    > apparently
    > only through the process of re-linking (very manual process) will the new
    > DSN
    > information be used.
    >
    > How can we make this move more seamless?
    >
    > Thanks
    > Vern Rabe
     
  4. Vern Rabe

    Vern Rabe
    Expand Collapse
    Guest

    I don't see how going DSN-less would help make the transition more seamless.
    In the referenced document it states "The code below needs to be run any time
    you want to change the server and/or database to which the tables are
    linked". I'd have to run the code for each of the several hundred apps
    coincident with the server change. I was hoping for some way to automatically
    purge the cached DSN information, and automatically relink existing tables
    using the new DSN. Oh, and I didn't mention in the original post that
    probably 10-20% of the applications were written by developers (capable of
    creating DSN-less VB code), and the remainder were created by MS Office users
    who know how to link tables using their DSN definition, but not how to create
    code.

    Maybe a more succinct question is: What is the easiest way (fewest
    keystrokes) to force an mdb appliction to purge the cached DSN connection
    information and relink all existing linked tables?

    Thanks
    Vern Rabe

    "Douglas J. Steele" wrote:

    > Why not try DSN-less? See
    > http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > news:A2EC0A07-A629-4F9F-ADF7-5393B8DFCF5C@microsoft.com...
    > > We have several hundred mdb databases, created with Access 2000, 2002, and
    > > 2003. They all use system DSNs to gain access to a central SQL Server, via
    > > both linked tables and pass through queries. The system DSNs use SQL
    > > Server
    > > authentication. The database that they access is being moved to another
    > > SQL
    > > Server, and once there, we want all authentication to be via Windows
    > > Authentication. We are trying to make the transition as seamless as
    > > possible.
    > > So far, this is (was?) our plan:
    > >
    > > 1. Create a file DSN on a network share, specifying trusted connection.
    > > 2. Convert all mdb's to use this instead of the local system DSN.
    > > 3. When the SQL Server database gets moved, modify the file DSN to reflect
    > > the new servername.
    > >
    > > This doesn't work because Access caches the DSN information, and
    > > apparently
    > > only through the process of re-linking (very manual process) will the new
    > > DSN
    > > information be used.
    > >
    > > How can we make this move more seamless?
    > >
    > > Thanks
    > > Vern Rabe

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You talked that you needed to touch every application in order to change it
    to use the DSN on the server. That's why I thought that if you had to change
    the applications in any case, removing the dependency on the DSN might be an
    idea.

    Since I typically don't use DSNs, I'm afraid I don't have an answer for your
    specific question.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    news:311B1D26-533B-4ACA-ABFF-0E34CDACF091@microsoft.com...
    > I don't see how going DSN-less would help make the transition more

    seamless.
    > In the referenced document it states "The code below needs to be run any

    time
    > you want to change the server and/or database to which the tables are
    > linked". I'd have to run the code for each of the several hundred apps
    > coincident with the server change. I was hoping for some way to

    automatically
    > purge the cached DSN information, and automatically relink existing tables
    > using the new DSN. Oh, and I didn't mention in the original post that
    > probably 10-20% of the applications were written by developers (capable of
    > creating DSN-less VB code), and the remainder were created by MS Office

    users
    > who know how to link tables using their DSN definition, but not how to

    create
    > code.
    >
    > Maybe a more succinct question is: What is the easiest way (fewest
    > keystrokes) to force an mdb appliction to purge the cached DSN connection
    > information and relink all existing linked tables?
    >
    > Thanks
    > Vern Rabe
    >
    > "Douglas J. Steele" wrote:
    >
    > > Why not try DSN-less? See
    > > http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > > news:A2EC0A07-A629-4F9F-ADF7-5393B8DFCF5C@microsoft.com...
    > > > We have several hundred mdb databases, created with Access 2000, 2002,

    and
    > > > 2003. They all use system DSNs to gain access to a central SQL Server,

    via
    > > > both linked tables and pass through queries. The system DSNs use SQL
    > > > Server
    > > > authentication. The database that they access is being moved to

    another
    > > > SQL
    > > > Server, and once there, we want all authentication to be via Windows
    > > > Authentication. We are trying to make the transition as seamless as
    > > > possible.
    > > > So far, this is (was?) our plan:
    > > >
    > > > 1. Create a file DSN on a network share, specifying trusted

    connection.
    > > > 2. Convert all mdb's to use this instead of the local system DSN.
    > > > 3. When the SQL Server database gets moved, modify the file DSN to

    reflect
    > > > the new servername.
    > > >
    > > > This doesn't work because Access caches the DSN information, and
    > > > apparently
    > > > only through the process of re-linking (very manual process) will the

    new
    > > > DSN
    > > > information be used.
    > > >
    > > > How can we make this move more seamless?
    > > >
    > > > Thanks
    > > > Vern Rabe

    > >
    > >
    > >
     
  6. Vern Rabe

    Vern Rabe
    Expand Collapse
    Guest

    Yes, I have to touch them all, but we've got a few weeks (months?) before we
    change servers, and I can more leisurely change the current DSN definition to
    use the new file DSN. Then, on some weekend, we'll move the SQL Server
    database, and on Monday morning, all mdb's should be up and running, using
    the new SQL Server.

    But after looking you your code more, I'm wondering if I couldn't modify it
    to loop through all linked tables, and simply force a relink. If so, I could
    create a module with said code in each mdb, run the code, then drop the
    module. Not great, but better than manually relinking every table. Am I
    wasting my time? Or maybe leave the module in and have it run on
    initialization?

    Thanks again for you ideas & help
    Vern Rabe

    "Douglas J Steele" wrote:

    > You talked that you needed to touch every application in order to change it
    > to use the DSN on the server. That's why I thought that if you had to change
    > the applications in any case, removing the dependency on the DSN might be an
    > idea.
    >
    > Since I typically don't use DSNs, I'm afraid I don't have an answer for your
    > specific question.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > news:311B1D26-533B-4ACA-ABFF-0E34CDACF091@microsoft.com...
    > > I don't see how going DSN-less would help make the transition more

    > seamless.
    > > In the referenced document it states "The code below needs to be run any

    > time
    > > you want to change the server and/or database to which the tables are
    > > linked". I'd have to run the code for each of the several hundred apps
    > > coincident with the server change. I was hoping for some way to

    > automatically
    > > purge the cached DSN information, and automatically relink existing tables
    > > using the new DSN. Oh, and I didn't mention in the original post that
    > > probably 10-20% of the applications were written by developers (capable of
    > > creating DSN-less VB code), and the remainder were created by MS Office

    > users
    > > who know how to link tables using their DSN definition, but not how to

    > create
    > > code.
    > >
    > > Maybe a more succinct question is: What is the easiest way (fewest
    > > keystrokes) to force an mdb appliction to purge the cached DSN connection
    > > information and relink all existing linked tables?
    > >
    > > Thanks
    > > Vern Rabe
    > >
    > > "Douglas J. Steele" wrote:
    > >
    > > > Why not try DSN-less? See
    > > > http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no private e-mails, please)
    > > >
    > > >
    > > > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > > > news:A2EC0A07-A629-4F9F-ADF7-5393B8DFCF5C@microsoft.com...
    > > > > We have several hundred mdb databases, created with Access 2000, 2002,

    > and
    > > > > 2003. They all use system DSNs to gain access to a central SQL Server,

    > via
    > > > > both linked tables and pass through queries. The system DSNs use SQL
    > > > > Server
    > > > > authentication. The database that they access is being moved to

    > another
    > > > > SQL
    > > > > Server, and once there, we want all authentication to be via Windows
    > > > > Authentication. We are trying to make the transition as seamless as
    > > > > possible.
    > > > > So far, this is (was?) our plan:
    > > > >
    > > > > 1. Create a file DSN on a network share, specifying trusted

    > connection.
    > > > > 2. Convert all mdb's to use this instead of the local system DSN.
    > > > > 3. When the SQL Server database gets moved, modify the file DSN to

    > reflect
    > > > > the new servername.
    > > > >
    > > > > This doesn't work because Access caches the DSN information, and
    > > > > apparently
    > > > > only through the process of re-linking (very manual process) will the

    > new
    > > > > DSN
    > > > > information be used.
    > > > >
    > > > > How can we make this move more seamless?
    > > > >
    > > > > Thanks
    > > > > Vern Rabe
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You can leave the module in, just in case you ever need to relink in the
    future.

    I typically have an INI file that accompanies the MDB file. In there, I
    store the details for the current data source. Everytime the database opens,
    it reads the INI file and compares what's there to the current linkage. If
    they're different, it relinks the tables.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    news:3AC88227-E5C1-40D9-83DC-AF9FFDA4CFAD@microsoft.com...
    > Yes, I have to touch them all, but we've got a few weeks (months?) before

    we
    > change servers, and I can more leisurely change the current DSN definition

    to
    > use the new file DSN. Then, on some weekend, we'll move the SQL Server
    > database, and on Monday morning, all mdb's should be up and running, using
    > the new SQL Server.
    >
    > But after looking you your code more, I'm wondering if I couldn't modify

    it
    > to loop through all linked tables, and simply force a relink. If so, I

    could
    > create a module with said code in each mdb, run the code, then drop the
    > module. Not great, but better than manually relinking every table. Am I
    > wasting my time? Or maybe leave the module in and have it run on
    > initialization?
    >
    > Thanks again for you ideas & help
    > Vern Rabe
    >
    > "Douglas J Steele" wrote:
    >
    > > You talked that you needed to touch every application in order to change

    it
    > > to use the DSN on the server. That's why I thought that if you had to

    change
    > > the applications in any case, removing the dependency on the DSN might

    be an
    > > idea.
    > >
    > > Since I typically don't use DSNs, I'm afraid I don't have an answer for

    your
    > > specific question.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > > news:311B1D26-533B-4ACA-ABFF-0E34CDACF091@microsoft.com...
    > > > I don't see how going DSN-less would help make the transition more

    > > seamless.
    > > > In the referenced document it states "The code below needs to be run

    any
    > > time
    > > > you want to change the server and/or database to which the tables are
    > > > linked". I'd have to run the code for each of the several hundred apps
    > > > coincident with the server change. I was hoping for some way to

    > > automatically
    > > > purge the cached DSN information, and automatically relink existing

    tables
    > > > using the new DSN. Oh, and I didn't mention in the original post that
    > > > probably 10-20% of the applications were written by developers

    (capable of
    > > > creating DSN-less VB code), and the remainder were created by MS

    Office
    > > users
    > > > who know how to link tables using their DSN definition, but not how to

    > > create
    > > > code.
    > > >
    > > > Maybe a more succinct question is: What is the easiest way (fewest
    > > > keystrokes) to force an mdb appliction to purge the cached DSN

    connection
    > > > information and relink all existing linked tables?
    > > >
    > > > Thanks
    > > > Vern Rabe
    > > >
    > > > "Douglas J. Steele" wrote:
    > > >
    > > > > Why not try DSN-less? See
    > > > > http://www.accessmvp.com/djsteele/DSNLessLinks.html for one

    approach.
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no private e-mails, please)
    > > > >
    > > > >
    > > > > "Vern Rabe" <VernRabe@discussions.microsoft.com> wrote in message
    > > > > news:A2EC0A07-A629-4F9F-ADF7-5393B8DFCF5C@microsoft.com...
    > > > > > We have several hundred mdb databases, created with Access 2000,

    2002,
    > > and
    > > > > > 2003. They all use system DSNs to gain access to a central SQL

    Server,
    > > via
    > > > > > both linked tables and pass through queries. The system DSNs use

    SQL
    > > > > > Server
    > > > > > authentication. The database that they access is being moved to

    > > another
    > > > > > SQL
    > > > > > Server, and once there, we want all authentication to be via

    Windows
    > > > > > Authentication. We are trying to make the transition as seamless

    as
    > > > > > possible.
    > > > > > So far, this is (was?) our plan:
    > > > > >
    > > > > > 1. Create a file DSN on a network share, specifying trusted

    > > connection.
    > > > > > 2. Convert all mdb's to use this instead of the local system DSN.
    > > > > > 3. When the SQL Server database gets moved, modify the file DSN to

    > > reflect
    > > > > > the new servername.
    > > > > >
    > > > > > This doesn't work because Access caches the DSN information, and
    > > > > > apparently
    > > > > > only through the process of re-linking (very manual process) will

    the
    > > new
    > > > > > DSN
    > > > > > information be used.
    > > > > >
    > > > > > How can we make this move more seamless?
    > > > > >
    > > > > > Thanks
    > > > > > Vern Rabe
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  8. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    Vern Rabe <VernRabe@discussions.microsoft.com> wrote:

    >But after looking you your code more, I'm wondering if I couldn't modify it
    >to loop through all linked tables, and simply force a relink. If so, I could
    >create a module with said code in each mdb, run the code, then drop the
    >module. Not great, but better than manually relinking every table. Am I
    >wasting my time? Or maybe leave the module in and have it run on
    >initialization?


    Absolutely. I'd definitely agree with this approach. And I've done
    exactly this in the past. And I agree with Doug's approach of
    storing this connection data in an INI file. (Or files as required.)
    Preferably read only except to you and the IT group.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     

Share This Page