Re: dsn-less connection

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for your info anyways Doug. I really appreciate it :-)

Take care,

J


"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:O5l4$33aGHA.4424@xxxxxxxxxxxxxxxxxxxxxxx
Sorry: we always use Trusted Connection, so I don't have an opportunity to
test what would or wouldn't work.

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


"J" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:%23jNww6xaGHA.2368@xxxxxxxxxxxxxxxxxxxxxxx
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


"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:%238W56zqaGHA.1352@xxxxxxxxxxxxxxxxxxxxxxx
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 & ";"


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


"Noone" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:eJFY3BoaGHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
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










.

Relevant Pages