dsn-less connection

  • Thread starter Thread starter Noone
  • Start date Start date
N

Noone

Hello. I was wondering if anyone would know of how to modify the below code
that I found on the web if possible which converts existing dsn link tables
from ms access to sql server to dsn-less but have it prompt the user to
supply their individual sql login id and password when they go in and open a
table? When I run this code I notice it saves the converted table link as a
trusted connection which instead I want each user who would be accessing it
to supply their own sql login credentials.

Thanks in advance.

J


Sub FixConnections(ServerName As String, DatabaseName As String)
' Looks for any TableDef objects that have a connection string, and
' changes it to DSN-less


Dim dbCurrent As Database
Dim intLoop As Integer
Dim intToChange As Integer
Dim lngAttributes() As Long
Dim strSourceTableName() As String
Dim strTableName() As String
Dim tdfCurrent As TableDef


intToChange = 0


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then


' This is a linked table: save its characteristics.
' If you have linked tables other than to SQL Server, you'd put
' logic in here to ignore those tables not linked to SQL Server.


ReDim Preserve lngAttributes(intToChange)
ReDim Preserve strSourceTableName(intToChange)
ReDim Preserve strTableName(intToChange)
lngAttributes(intToChange) = tdfCurrent.Attributes
strTableName(intToChange) = tdfCurrent.Name
strSourceTableName(intToChange) = tdfCurrent.SourceTableName
intToChange = intToChange + 1
End If
Next


' Go through the array of linked table characteristics.
' Delete the table, then relink, using a DSN-less Connection.


For intLoop = 0 To intToChange - 1
dbCurrent.TableDefs.Delete strTableName(intLoop)
Set tdfCurrent = dbCurrent.CreateTableDef(strTableName(intLoop))
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" &
DatabaseName & ";SERVER=" & ServerName & ";UID=;PWD=;"
tdfCurrent.SourceTableName = strSourceTableName(intLoop)
dbCurrent.TableDefs.Append tdfCurrent
Next


Set tdfCurrent = Nothing
Set dbCurrent = Nothing


End Sub
 
Prompt the user for their user name and password, store them in variables
myUsername and myPassword and replace the line:

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" &
DatabaseName & ";SERVER=" & ServerName & ";UID=;PWD=;"

with

tdfCurrent.Connect = "ODBC;DRIVER={SQL Server};" & _
"DATABASE=" & DatabaseName & ";SERVER=" & _
ServerName & ";UID=" & myUsername & ";PWD=" & _
myPassword & ";"
 
Cool. It worked pretty much as I was hoping in prompting the user for a SQL
Server login and was able to remove the specific dsn that's in the odbc
control panel :-)

Please forgive me, but if I may be a little bit more nit picky...would it be
possible to have it default to not Use Trusted Connection so it would go
straight to the SQL Server login prompt? Since this appears to be the
default after I ran the new code it initally comes up with a connection
failed message 'Login failed for user '(null)'. Reason: Not associated with
a trusted SQL Server connection.' which then when you click ok it comes up
with the login prompt but the Use Trusted Connection is checked on. Was
just wondering if this was possible since I know it's a simple extra step
that some lazy egotistical users might get alarmed in seeing the first
connection failed message and complain about having to uncheck the Use
Trusted Connection checkbox. If not, it's ok. Thanks a bunch for your
helpful reply anyways Doug :-)

Have a good weekend.

J
 
Sorry: we always use Trusted Connection, so I don't have an opportunity to
test what would or wouldn't work.
 
Back
Top