Re: dsn-less connection

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

  • Re: Permission question - another one
    ... If I add an Sql Login it does add the TRAVAC\ in front of the names, ... seems to be users that were setup to use SQL Server Authentication. ... RAPTOR is the Server that has SQL Server running on it. ... > " I could think I am taking permissions away from someone, ...
    (microsoft.public.sqlserver.programming)
  • Re: Renamed Windows login not found in SQL Server 2000
    ... It's almost like SQL tuck some knowledge away in an area ... of memory that only gets released on Windows stop. ... > I am running SQL Server 2000 SP2 with Windows ... > login gets corrupted) I am unable to add the new login to ...
    (microsoft.public.sqlserver.security)
  • Re: Logging in irrespective of database access
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Logging in irrespective of the database access settings
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.setup)
  • Logging in irrespective of the database access settings
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)