Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How To Bundle Login Info to MS Access To Get to SQL Server Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2008 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 9:22 AM
Points: 5, 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...)
Post #531203
Posted Friday, July 11, 2008 12:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:08 AM
Points: 1,820, Visits: 595
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.
Post #532756
Posted Thursday, July 24, 2008 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 9:22 AM
Points: 5, 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!

:)
Post #540275
Posted Sunday, July 27, 2008 9:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #541540
Posted Monday, July 28, 2008 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 9:22 AM
Points: 5, 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.
Post #541902
Posted Monday, July 28, 2008 11:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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

Post #542090
Posted Monday, July 28, 2008 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 7:51 AM
Points: 6, 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.
Post #542281
Posted Tuesday, July 29, 2008 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 9:22 AM
Points: 5, 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.
Post #542658
Posted Wednesday, July 30, 2008 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 29, 2011 9:11 AM
Points: 10, 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
Post #543340
Posted Wednesday, July 30, 2008 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 9:22 AM
Points: 5, 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
Post #543497
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse