Brett Barabash
BBarabash at TappeConstruction.com
Sat Sep 18 14:27:14 CDT 2004
Try setting tdAttach.Attributes = dbAttachSavePWD
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher
Hawkins
Sent: Friday, September 17, 2004 5:20 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Connecting to SQL from Access w/a single login?
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.