SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Bundle Login Info to MS Access To Get to SQL Server


How To Bundle Login Info to MS Access To Get to SQL Server

Author
Message
monte.jackson
monte.jackson
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 10
I have an Access 2007 application that uses linked tables to a SQL Server 2005 database on another server. Right now, the first time I try to access a linked table anywhere in the application, it prompts me for my userid/password. Once I put it in, it doesn't request it anymore.

Is there a way to add the userid/password to an ODBC file or system DSN? Whenever I create a DSN in ODBC, it prompts me for the userid/password for the SQL Server. However, it doesn't stay there. Can I force that?

Or, if anyone knows how to embed the information into my MS Access application, then that would help alot. I did this back in Access 2003 by adding a Digital Certificate to the application. However, it seems Access 2007 does things differently (thanks Microsoft for making me relearn everything since 2003...my productivity when in the crapper when I upgraded...)
Dave Schutz
Dave Schutz
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 609
Monte,

It took me several tries to fix this when I switched to Access 2007 but the trick is to check the save password box in the link tables screen. The checkbox is on the right-hand side of the link tables screen that appears at the end of dsn setup routine. You may need to delete your dsn settings and set them up again. After you finish the ODBC setup part of the dsn setup wizard, the sql server login box opens. Put in the correct sql login and password, clock OK. Then the link tables dialog opens and you can check the save password box. Access will give a security warning about not being safe to save passwords but I just ignored their warning.
Access security is getting so strict no one can use it.
monte.jackson
monte.jackson
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 10
THANKS! This concept worked. It took me awhile to figure out how to reset the ODBC, so I will try to clarify how to do this.

1. Deleted all LINKED tables to the backend SQL Server 2005 database.
2. Re-added all my LINKED tables, but during this process, when you get past selecting the ODBC DSN that you want to use and logging into SQL Server via that ODBC, you get a list of tables in the backend SQL Server 2005 database. This is where you select the tables you want to create links to. On the right-hand side of this screen is a little check-box that says "Save Password". Checking this and then confirming the message to save the password is what cleared the problem up.

Thanks Again!

Smile
tfifield
tfifield
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 2890
You can also do this from VBA. I do it all the time. I keep a list of tables to link inside the Access application. If I need to re-link all of them due to schema changes I drop them all first. Then I run a VBA function that reads each table in the local TablesToLink table and I call the function below on each table:
Todd Fifield

Private Function LinkOneTable(TblName as String) As Boolean
Dim Con as String
Dim db As DataBase
Dim TDf As TableDef

On Error GoTo LOT_Err
LinkOneTable = False

' Build the ODBC connect string
Con = "ODBC;driver={SQL Server};Server=YourServer;" & _
"UID=YourSQLUser;PWD=YourSQLPassword;DATABASE=YourDatabase;" & _
"TABLE=dbo." & TblName

Set db = CurrentDb
Set TDf = db.CreateTableDef(TblName)
TDf.Connect = S$
TDf.SourceTableName = Name$

' Save password when table is attached
TDf.Attributes = dbAttachSavePWD

db.TableDefs.Append TDf

Set db = Nothing
LinkOneTable = True
LOT_Exit:
Exit Function
LOT_Err:
MsgBox "There was an error linking to table: " & TblName
Resume LOT_Exit
End Function
monte.jackson
monte.jackson
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 10
Thanks for your reply to this. As you might have seen from my id, I am a bit of a newbie to Access and SQL Server and VBA. However, I have decades of history with databases and programming, so I can usually read and understand what is going on.

However, I am wondering about your Private Function. You said that you keep a table that has a list of table names to reconnect to. Is that table definition in this script, or am I missing something? If not, could you provide that table def? Also, at what point do you run this script? Just ocassionaly or everytime the application is opened?

Thanks again for your help.
tfifield
tfifield
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2479 Visits: 2890
The table with the names of the tables to link are in the Access MDB. I call it TablesToLink. It just has the names of the tables in SQL Server that I need to link to. It looks like this:

TblaName:
Orders
OrderDetails
Customers

Here's kind of what the function to link all tables looks like:
Public Function LinkAllTables()
Dim DB as Database
Dim Rs as RecordSet
Dim Str as String

Call UnLinkSQLTables 'Function that deletes all tables that are linked

Str = "SELECT TblName FROM TablesToLink"

SET DB = CurrentDB()
SET Rs = DB.OpenRecordSet(Str)

Do While Not Rs.EOF
LinkOneTable(Rs!TblName) 'Call the function that does the linking.
Rs.MoveNext
Loop

Rs.Close
SET Rs = Nothing
SET DB - Nothing
END FUNCTION
Martin Goebbels
Martin Goebbels
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 21
Monte,

this solution is OK, works very well, but you must be aware of the following risks.
Although none can retrieve the password you saved in the MDB, anyone who runs the MDB will get access to the linked tables, at least in your corporate environment. So, if you hadn't think about (I'm sure you did, but its important to warn less experienced users), you should protect your MDB with password, doing the same with the VBA code.

Either is good to remember that, for the Server, it will be always the same login connecting to , like an "application user".

Unfortunately, i've tried, with no success, to discover how does Ms-Access saves the connection information, once you used one of the linked tables, when you not save the users information with the linked table. I mean, it would be nice to use some login screen with validation by the SQL-Server and then use this information when opening the linked tables on the application.
monte.jackson
monte.jackson
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 10
Thanks for the input and realizing the security issue you bring up, I created a userid/password that has very limited use in the DB, etc. So, even if they could figure out where everything was located, they couldn't do any more damage than what the application allows for anyway. Plus, I back the data up 3 times a day just incase someone want to delete eveything and try to wipe out the data...I can simply restore.

As far as the log in screen....you get that capability in a round about way. I.e. When you access the first linked table that doesn't have the login information stored, it prompts for the userid/password. After that, it doesn't seem to prompt for the other tables that use the same userid/password for access.

Thanks to everyone for you help. I like the VBA code example above, but still trying to understand it. For now, the method of just re-linking all the tables with the password saved is doing the trick for my basic application.
Reggie.Gambino
Reggie.Gambino
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 218
To see how MSAccess stores the connection :
From the Tools on the menu select Options
On the tab for View click the System Objects check box
Click OK button
From the tables tab Open the mSysObjects table

The connect column shows the connection string for each linked item in the database
monte.jackson
monte.jackson
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 10
The instructions above do not pertain to Access 2007. Thanks to MS, they totally changed the interface and things with all of Office 2007 to bring office productivity to a standstill. So, the follow is the method of Access 2007 to see the System Tables and view the MSysObjects table.

1. Bring up the Navigation Pane on the left.
2. Right-click on the Navigation Bar to bring up the "Navigation Options"
3. At the bottom of the Navigation Options, select "Show System Objects" and Click OK
4. Open up the MSysObjects table and you can then see the security.

There has been a previous comment in the past that saving the passwords like this can cause a security concern. For my application, I am not all that concerned about that. However, if you are, then you can do the following for that Database:

1. Update the Database Options from the Access Options under the Office Button and uncheck the "Display Navigation" pane button. This should prevent them from seeing the Navigation Pane that would allow them to get to the System Tables.
2. Before distribution, save your database as a ACCDE file. This will prevent users from making mods to the database or VBA code.

I think a good Access Hack could get around these measures, but I think this will help in about 90% of the cases and certainly works for me.

Monte
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search