Christopher Hawkins
clh at christopherhawkins.com
Fri Sep 17 17:19:43 CDT 2004
Brett,
I'm using a similar routine currently. I have a metadata table full
of information about the data tables. It contains the localname,
path and sourceobject name of the table I'm linking to in SQL Server.
The actual linking is fine, but as you said remembering the password
is an issue.
Mind you, the password is in the metadata table. But it seems to get
ignored. Here is a sample of the string I'm using to create the link:
[code]
ODBC;DSN=MyDataSource;Description=My SQL Server
Date;UID=MyUser;PASSWORD=MyPassword;APP=Microsoft Office
2003;WSID=MyWorkstation;DATABASE=MyDatabaseName[/code]
Here's my function. How can I ensure that the password that's baked
into the path gets remembered? I'm thinking that there must be a way
to emulate checking the 'Remember Password' checkbox in the Linked
Table dialog.
[code]
Public Sub LinkTables()
' Given a database with no linked tables, this function
' will establish links to all tables enumerated in zTable
Dim db As Database
Dim rsTable As dao.Recordset
Dim sSQL As String
Dim tdAttach As TableDef
On Error GoTo Err_LinkTables
Set db = CurrentDb
sSQL = "SELECT * FROM zTable"
Set rsTable = db.OpenRecordset(sSQL)
rsTable.MoveFirst
Do Until rsTable.EOF = True
Debug.Print "Linking " & rsTable("TableName")
Set tdAttach = db.CreateTableDef(rsTable("TableName"))
' Set the connection info in the tabledef object
With tdAttach
.Connect = rsTable("Path")
.SourceTableName = rsTable("SourceTable")
.Properties("ODBC: Remember Password") = True
End With
' Append the new tabledef
db.TableDefs.Append tdAttach
rsTable.MoveNext
Loop
Exit_LinkTables:
Exit Sub
Err_LinkTables:
If Err.Number = 3012 Then
Resume Next
Else
MsgBox Err.Description
End If
Resume Exit_LinkTables
End Sub
[/code]
-C-
---- Original Message ----
From: BBarabash at tappeconstruction.com
To: accessd at databaseadvisors.com,
Subject: RE: [AccessD] Connecting to SQL from Access w/a single login?
Date: Fri, 17 Sep 2004 14:29:46 -0500
>To all who use NT authentication:
>Do you (or your network admins) have any concerns about end users
>potentially linking to the raw data in other apps?
>
>For example, a savvy power user could easily setup an ODBC
>connection in
>Excel/MS Query and directly query the table data. If the database
>relies on NT authentication, and the table is available to them, they
>can link to the data directly.
>
>I found this to be problemattic, since I don't really want my users
>bypassing the business rules and manually generating their own
>reports
>with sensitive data.
>
>Christopher:
>I had this problem a while back using SQL Server authentication on an
>Access 2000 MDB file. The problem is that the Access attach tables
>dialog doesn't save the password info. I was able to get around it
>by
>writing the following DAO routine (make sure you have a DAO reference
>set in your project). This routine will loop through all of your
>attached tables, and relink them using the specified UserID and
>Password.
>
>Public Sub FixAttachments(ByVal strServer As String, ByVal strUID As
>String, ByVal strPWD As String)
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim tdf As DAO.TableDef
> Dim strSQL As String
>
> Set db = DBEngine.Workspaces(0).Databases(0)
> strSQL = "SELECT MSysObjects.Name, " & _
> "MSysObjects.ForeignName, " & _
> "MSysObjects.Connect " & _
> "FROM MSysObjects " & _
> "WHERE MSysObjects.Type=4 " & _
> "ORDER BY MSysObjects.Name;"
> Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> db.TableDefs.Refresh
>
> Do Until rs.EOF
> Debug.Print rs!Name
> Set tdf = db.CreateTableDef(rs!Name)
>
> tdf.Connect = "ODBC;" & _
> ExtractArg(rs!Connect, "DRIVER") & ";" & _
> "SERVER=" & strServer & ";" & _
> ExtractArg(rs!Connect, "DATABASE") & ";" & _
> "UID=" & strUID & ";" & _
> "PWD=" & strPWD
>
> tdf.Attributes = dbAttachSavePWD
> tdf.SourceTableName = rs!ForeignName
> db.TableDefs.Delete rs!Name
> db.TableDefs.Append tdf
>
> rs.MoveNext
> Loop
>
> db.TableDefs.Refresh
> rs.Close
>
>End Sub
>
>Public Function ExtractArg(ByVal strIn As String, ByVal strName As
>String) As String
>
> Dim lngPos As Long
>
> strIn = strIn & ";"
> lngPos = InStr(strIn, strName & "=")
>
> If lngPos > 0 Then
> ExtractArg = Mid$(strIn, lngPos, InStr(lngPos, strIn, ";") -
>lngPos)
> End If
>
>End Function
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
>Lawrence
>(AccessD)
>Sent: Friday, September 17, 2004 1:49 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] Connecting to SQL from Access w/a single
>login?
>
>
>Hi Christopher:
>
>The SQL server has it's own way of dealing with access rights. They
>are
>through an internal 'sa' type account or it is through 'NT
>authentication'.
>
>Traditionally, I only use the 'sa' accounts as a backdoor for an
>administrator. Your best option would be to go with 'NT
>authentication'.
>
>The setup is fairly easy, if network has a domain. Go to the main
>server, login as an administrator, 'right-click' MyComputer
>(assuming a
>Windows2000 system), select the 'Manage' option, choose 'Local Users
>and
>Groups' then:
>One; create a login group called something like 'sqlusers'.
>Two; create or add individual login to the group.
>
>Once all the appropriate users have been added to this group the
>access
>the SQL Enterprise manager. 'EM'.
>One; Expand the current SQL server, the 'Security' directory and the
>'click'
>on the logins icon.
>Two; 'right mouse click', select 'New login' and, on the General tab,
>from the popdown list select your 'sqlusers' group. Make sure that
>the
>'Authentication' radio button is selected and the current
>domain/server
>is visible.
>Three; Go back to the 'Database' and 'MyDatabase' directory and
>subdirectory and 'right mouse click' the users icon.
>Four; Select 'New database user' and select the new group you have
>just
>added from the popdown menu.
>
>There is some clean up and defining to do but you have basically
>defined
>access rights. Now all you do is attempt a connection at the point
>where
>the user accesses the FE Access DB and if the connection fails, close
>and exit the program. (If you need a sample of code that will
>connect
>to your server just request it.)
>
>You will find this way very clean, secure and not intrusive to the
>users.
>This way is the only way I now ever setup user accounts.
>
>HTH
>Jim
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
>Hawkins
>Sent: Friday, September 17, 2004 11:06 AM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Connecting to SQL from Access w/a single login?
>
>
>This is driving me nuts. I know I did this a few years ago, but
>right
>now I'm totally stumped.
>
>A client of mine wrote himself an Access app for use around his
>office.
>Eventually the back-end swelled up to 500MB and problems ensued, so
>they
>decided to go to SQL Server. He upsized the database himself and
>then
>called me in to help get the Access front-end working against SQL
>Server.
>
>Here's my current issue; I want all the Access clients to use the
>same
>SQL Server login rather than having a unique SQL Server login for
>each
>domain account.
>
>I went ahead and created an AppUser login and gave it the appropriate
>permissions to the application database. I have the AppUser login
>and
>password set in the ODBC DSN. But whenever a user tries to connect,
>an
>error message appears stating that his domain login was not
>recognized
>by the SQL Server. It's not even paying attention to the AppUser
>info.
>
>I'm surfing BOL right now, but haven't yet figured out what I missed.
>And I know I'm missing something.
>
>Any suggestions?
>Respectfully,
>
>Christopher Hawkins
>Software Developer
>(559) 687-7591
>http://www.christopherhawkins.com
>
>
>---------------------------------------------------------------------
>-----------------------------------------------
>The information in this email may contain confidential information
>that
>is legally privileged. The information is only for the use of the
>intended
>recipient(s) named above. If you are not the intended recipient(s),
>you
>are hereby notified that any disclosure, copying, distribution, or
>the taking
>of any action in regard to the content of this email is strictly
>prohibited. If
>transmission is incorrect, unclear, or incomplete, please notify the
>sender
>immediately. The authorized recipient(s) of this information is/are
>prohibited
>from disclosing this information to any other party and is/are
>required to
>destroy the information after its stated need has been fulfilled.
>
>Any views expressed in this message are those of the individual
>sender, except where the sender specifies and with authority,
>states them to be the views of Tappe Construction Co.
>
>This footer also confirms that this email message has been scanned
>for the presence of computer viruses.Scanning of this message and
>addition of this footer is performed by SurfControl E-mail Filter
>software
>in conjunction with virus detection software.
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
Respectfully,
Christopher Hawkins
Software Developer
(559) 687-7591
http://www.christopherhawkins.com