Linking tables with DSN to SQL Server 2

[1DMF]

Hi,

I have an MS Access DB, linked to SQL Server, no problem.

Except remote users can't access it as the are not logged onto the domain.

I decided to write my own File DSN and link the tables using that, so it used hardcoded UserID & Password.

Only no matter what settings I use in the DSN, they are not all being used and it keeps defaulting to "trusted connection" using "Windows Authentication"

How do I make access use the credentials supplied in the DSN....here is the DSN....

[ODBC]
DRIVER=SQL Server
UID=[b]userid[/b]
PWD=[b]password[/b]
Network=DBMSSOCN
DATABASE=[b]database-name[/b]
APP=Microsoft Office 2003
SERVER=[b]I.P. of Server[/b]
Description=[b]database description[/b]
Trusted_Connection=No

bold = Names have been changed to protect the innocent

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

Does anyone know how to get access to use a 'File DSN', or even if this is possible?

If not, what alternatives are there?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[SimonSellick]

Hi,

I don't know abput using a file DSN, but you can refresh the linked tables' links, at the same time supplying your own connection string (in VBA): see

http://www.programmersheaven.com/c/msgboard/read.asp?Board=538&MsgID=244587&Setting=P9999F0001

Hope this helps.

 

[1DMF]

Great, thanks for the link, it looks like a possible start, however, I am not using a separate MDB, the tables are in SQL and the Access front end uses 3 different databases.

how would i check the tabledefs to see which database the link is relative to and ensure I switch to the correct connection string?

If I can resolve this issue, it looks promising this might be the solution I need.

I did edit the DSN on my local machine to hold all the connection info (as show in the earlier post), but when using the link table manager, it seemed to ignore or overwrite some of the settings and go back to "Trusted Connection" and try and use the local machine credentials.

I hope this coding will force the connection string and not be over written by the linked table manager or access.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[SimonSellick]

Hi,

The DB that I am working on at the moment has a local table containing the names of all the linked tables, and a function to re-link them. The table-of-tables contains the connection details for each table to be linked, and when the function is called the links are destroyed then re-made with the appropriate connection data. In this db the function is called on demand, but it could be made part of the start-up routine.

I hadn't come across this approach before working on this db, but it seems quite neat.

Hope this helps, and thanks for the star.

Simon.

 

[1DMF]

Any chance you could supply code example, as you are saying you don't use the internal access db (tabledefs) , I think thats what it is isn't it, a table access uses to store linked table info.

How would i link the tables not looping the linked table defs, as the example link you gave uses.

Or am I missunderstanding how this all works.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[PHV]

You may also have a look here:

http://www.mvps.org/access/tables/tbl0009.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[1DMF]

Thanks PHV, looks like I can use this part of the code

For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left$(.Connect, 4) = "ODBC" Then
                '    collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                Else
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                End If
            End If
        End With
    Next
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing

in conjunction with the above to acheive what I want. I'll have a play now with what I got and post back my results.

cheers for the help guys.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

Ok I've got the code written, which should work, only id doesn't, everytime it hits the .refreshlink command, it opens the dialog box for you to select the DSN.

Any ideas why this is happening and how to stop this and get it to use the connection string provided?

here is the code...

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    
    'On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        ' Set default conntection string data
        Cnct = "ODBC;UID=[b]userid[/b];PWD=[b]password[/b];SERVER=[b]ip of server[/b];Trusted_Connection=No;"
        With tdf
            'split current string to find db data
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = Cnct & "DATABASE=" & sVar(1) & ";"
                                    ' apply connection string
                        .Connect = Cnct
                        .RefreshLink [b]<--- is where DSN dialog box pops up[/b]
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & Err.Description
   Resume SLT_Exit

End Function

Also when I run the code again after selecting the DSN from the dialog box I msgbox the current connection string, which is the new one supplied however the UID & PWD seem to be missing, How do I get to see the full connection string including the UID & PWD.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[SimonSellick]

Hi,

The code that I've adapted actually deletes and re-creates the TableDef - I wonder whether that approach was taken because of this dialog problem? Here's the code, in case it's useful:

Dim db As Database
    Dim rs As Recordset
    Dim td As TableDef

    On Error GoTo LinkedTableManager_Err

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_LinkTable") ' tables to link
    While Not rs.EOF
        On Error Resume Next
        DoCmd.DeleteObject acTable, rs.Fields("Name")
        On Error GoTo LinkedTableManager_Err
        Set td = db.CreateTableDef(rs.Fields("Name"))
        td.Connect = rs.Fields("Connect")
        td.SourceTableName = rs.Fields("SourceName")
        db.TableDefs.Append td
        rs.MoveNext
    Wend
    rs.close
    LinkedTableManager = True
    
LinkedTableManager_End:
    Set td = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function

LinkedTableManager_Err:
    MSGBOX Err.Description, vbCritical, "FAILED TO RE-CONNECT"
    GoTo LinkedTableManager_End

I suspect that the .RefreshLink method is messing up your string.

Good luck.

Simon.

 

[1DMF]

yup i recon, working on a similar approach i found on MS website...

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
End Function

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

woohoo I cracked it, not sure if it will resolve my issue with remote use of database, but i'm about to go home an test it so fingers crossed.

here is the finished code...

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
    
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
            'split current string to find database name
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        'Fix table name issue
                        If Left(.Name, 4) = "dbo_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                        Else
                            sName = .Name
                        End If
                        ' create connection to table
                        Call AttachDSNLessTable(.Name, sName, "servername", Cnct, "userid", "password")
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & Err.Description
   Resume SLT_Exit

End Function

The reason I have done it like this is , while I develop and what not, I switch between test & live DB's using my Local Machine DSN's , but once ready to distribute I need to recreate the links DSN-Less.

So the routine gets the tabledefs and reconfigures current DSN settings to DSN-Less settings. I had to pratt around with the table name for one DB as the name used in access is not the name used in SQL (hence the dbo_ stuff), but appart from that it was fairly straight forward thanks to you guys.

Right i'm off home to test

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

Hey guys, works great, now our remote users can use the DB as if they were in the office (well a bit slower obviously)

Please except my sincere thanks, couldn't have done it without your help, it's much appreciated.

Regards,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[bigoldbulldog]

For DSN connection strings and non-DSN, I've been referencing this site for years now.

http://www.carlprothman.net/Default.aspx?tabid=81

Cheers,
ND

[small]bigoldbulldog AT hotmail[/small]