Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert to alternate DSN (2000)

    Is there a quick way of changing the DSN that has been used to connect an Access database to SQL Server tables? I have been given about 30 critical Access databases that have to work with a new SQL database when it is upgraded next month. Theoretically the database, table and field names have not changed between the two versions of the SQL database. I have a test server with a test version of the new SQL database and I have created a new DSN that points to this. Unfortunately some of the Access databases point to 20 or more tables in the SQL database. Rather than delete then relink them all from the UI is there a collection somewhere that I can For Each ... Next through to change all the linked tables in one step? There are also a number of local tables in each Access database.

    Thanks, as ever

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Convert to alternate DSN (2000)

    The short answer is that the linked table manager is what we have typically used - if all your linked tables are in SQL Server it only takes about 30 seconds to change a database. In fact we insist that all tables are in a single SQL Server database, and we switch from the test version to the live version of the database (so there really are two SQL databases, test and live) when we deploy the changes. Things get more complicated if you have a mixture of Access linked tables and SQL Server ODBC connected tables. In that case you have to carefully select the tables that are SQL Server and only run the Linked Table Manager against those tables - just be sure to check the check-box that says "Always prompt for a new location." It is possible to write a routine that does that for you in VBA if you want to have separate SQL Server databases for some of the tables, but it presumes you know a good deal about the internal structure of TableDefs. Let us know if you want to pursue that.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Convert to alternate DSN (2000)

    I don't work with SQL Server, but if you are using ODBC linked tables here is example of sub used to relink all linked Visual FoxPro tables (ODBC) in current database, using DAO methods:

    Public Sub ResetLinkedODBCTables(ByRef strDSN As String)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim strConn As String
    Dim strMsg As String

    Set db = CurrentDb
    For Each tbl In db.TableDefs
    If tbl.Attributes And dbAttachedODBC Then ' Linked ODBC tables
    strConn = tbl.Connect
    strConn = Left$(strConn, 9) & strDSN & _
    Mid$(strConn, InStr(9 + Len(strDSN), strConn, ";", 0))
    tbl.Connect = strConn
    tbl.RefreshLink
    End If
    Next tbl
    ' Test Message:
    MsgBox "All ODBC tables relinked with new DSN.", vbInformation, "RELINKED"

    Exit_Sub:
    Set db = Nothing
    Set tbl = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "RESET LINKED ODBC TABLES ERROR"
    Resume Exit_Sub
    End Sub

    For linked VFP tables the connection string reads like this:

    ODBC;DSN=VFP App;SourceDB=C:VFPDBFAPP.DBC [and so on...]

    The code replaces the DSN part of string with new DSN, the rest remains as is. You may have to use other variables to construct new string, such as server path, etc. To determine if table is linked ODBC, test TableDef Attributes property using And as a bitwise operator. (This assumes your db has only one type of linked ODBC table; if not, some additional test would have to be devised.) Here is an example of sub that loops thru the tabledefs in current db & prints attributes & connection string for specified tables:

    Public Sub GetTblDefAttributes()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim strMsg As String

    Set db = CurrentDb
    For Each tbl In db.TableDefs
    ' ' Linked non-ODBC:
    ' If tbl.Attributes And dbAttachedTable Then
    ' Debug.Print "Linked non-ODBC: " & tbl.Name & " " & tbl.Attributes
    ' End If
    If tbl.Attributes And dbAttachedODBC Then
    Debug.Print "Linked ODBC: " & tbl.Name & " " & tbl.Attributes
    Debug.Print vbTab & "Connection string: " & tbl.Connect
    End If
    ' If tbl.Attributes And Not dbSystemObject Then
    ' Debug.Print "Non System Table: " & tbl.Name & " " & tbl.Attributes
    ' End If
    Next tbl
    Exit_Sub:
    Set tbl = Nothing
    Set db = Nothing
    Exit Sub
    Err_Handler:
    (etc)
    End Sub

    Note you can use And Not comparison to exclude certain tables, such as system tables (dbSystemObject). See DAO TableDefAttributeEnum in Object Browser for complete list of Attribute constants and their values. Examples require reference set to DAO 3.6 library. Be sure to compact front end db after running code to refresh links, it will be bloated.

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert to alternate DSN (2000)

    Hi Wendell,

    I've tried to use the linked table manager as you suggested but I'm getting some odd results. I've got an opening form that just comes up as a featureless battleship grey screen. When I look at the linked tables in the UI I can open them but the connection strings don't look right. For example

    Previous connection string (before using linked table manager):
    ODBC;DSN=LOLA;Description=MS Access SQL Server;UID=lola;APP=Microsoft

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Convert to alternate DSN (2000)

    It does look a bit strange if indeed there isn't a semicolon between the C046 and DATABASE. I have noted however that the linking method does result in a slightly different connection string than what you get with the linked table manager, but never worried about it as things seemed to work both ways. If we weren't doing connections in code as Mark suggested, we use the linked table manager, often with 100 or more SQL Server tables involved, and it always seems to work. I think some of this has to do with the way that the ODBC connection string is setup on each workstation. I also presume that you are using integrated security with SQL Server - if not, we recommend it so you can track the userID who is making changes to data.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert to alternate DSN (2000)

    I think I have been given a dodgy Server to work with. It's very slow and after it was rebooted my DSN stopped working altogether. I created a new one, exactly the same as the first but with a different name, and that now works. I don't understand that because obviously the DSN is on my workstation, which was not rebooted. However, at the moment things are working. Thanks for everybody'ds help.

    One thing though. When the SQL tables were originally linked the 'remember password' box was checked. Now, after using the Linked Table manager to re-establish links, but to the test server, the password box pops up the first time I try to access the SQL database from any of the Access applications. I set up the SQL Server user that my DSN uses with the same password on the test machine as it had on the live machine. Should I be getting this password pop up?

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Convert to alternate DSN (2000)

    Things don't sound quite right. I don't think you indicated whether you were using the Integrated Security or the Standard Security feature of SQL Server - can you find out which one the admins have set up? One of the things that can go bump is that with a combination of both (which you can do but is a real pain to work with), NT passes one login to SQL Server, and it is expecting a standard login. Once you get it right, you shouldn't have to keep putting in the password, but until it is, you may get prompted each time you connect to the server.
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert to alternate DSN (2000)

    I'll check when I'm in there tomorrow.

Posting Permissions

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