July 9, 2008 at 1:54 pm
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...)
July 11, 2008 at 12:50 pm
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.
July 24, 2008 at 9:21 am
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!
🙂
July 27, 2008 at 9:43 am
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
July 28, 2008 at 7:41 am
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.
July 28, 2008 at 11:40 am
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
July 28, 2008 at 3:36 pm
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.
July 29, 2008 at 7:53 am
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.
July 30, 2008 at 5:27 am
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
July 30, 2008 at 7:57 am
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
January 21, 2014 at 8:45 am
In your LinkOneTable code you have:
TDf.Connect = S$
TDf.SourceTableName = Name$
Those variables aren't declared - S$ and Name$...
???
January 22, 2014 at 5:31 am
You could use DSN-less connections. The first code will attach one table:
Sub AttachOne(TableName As String)
Const my_connection = "ODBC;DRIVER=SQL Native Client;SERVER=my_server;UID=my_username;PWD=my_password;DATABASE=my_database;"
'
Dim db As DAO.Database, tdf As DAO.TableDef
'
Set db = CurrentDb
Set tdf = db.CreateTableDef
With tdf
.Name = TableName
.SourceTableName = TableName
.Connect = my_connection
.Attributes = .Attributes Or dbAttachSavePWD
End With
db.TableDefs.Append tdf
End Sub
...and this code will refresh all of the linked tables:
Sub RelinkAll()
Const my_connection = "ODBC;DRIVER=SQL Native Client;SERVER=my_server;UID=my_username;PWD=my_password;DATABASE=my_database;"
'
Dim db As DAO.Database, tdf As DAO.TableDef
'
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Connect > "" Then
tdf.Connect = my_connection
tdf.RefreshLink
End If
Next tdf
End Sub
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply