Need to link an SQL table to Access using SQL authentication without requiring users enter a password.
Hello,
What is the preferred way to link to an SQL table with Access using SQL Server authentication that does not require the user to enter a password?
I can connect using a system dsn, but don't know how to store the password in Access.
Thanks, my office opens in two hours and I'm trying to get this thing working before the staff gets in! :-)
What is the preferred way to link to an SQL table with Access using SQL Server authentication that does not require the user to enter a password?
I can connect using a system dsn, but don't know how to store the password in Access.
Thanks, my office opens in two hours and I'm trying to get this thing working before the staff gets in! :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Lee
Thanks. The link you provided was for creating a pass-through query.. is that what you meant?
@Victor
I can't get NT authentication to work because of the problems previously discussed.
I have created an SQL account and would like all my users to authenticate with this single account.
The question becomes how do I add the connection string to access, or edit the string created by the LTM?
@Scott
Thanks. I was able to get the connection string used by the the corrupt table with the help of another expert.
Now I know exactly what the string needs to be, but I don't know how to add it.
The LTM doesn't give me that option and I searched the code for the string with no luck.
@Gustav
Thanks for the code. I don't completely understand it yet, but it seems more complicated than necessary in my situation. Everyone who used the application can authenticate as the same user, I just need to know how to add the connection string. At this point, I know what string to add, just where and how to put it into the code.
Thanks
Thanks. The link you provided was for creating a pass-through query.. is that what you meant?
@Victor
I can't get NT authentication to work because of the problems previously discussed.
I have created an SQL account and would like all my users to authenticate with this single account.
The question becomes how do I add the connection string to access, or edit the string created by the LTM?
@Scott
Thanks. I was able to get the connection string used by the the corrupt table with the help of another expert.
Now I know exactly what the string needs to be, but I don't know how to add it.
The LTM doesn't give me that option and I searched the code for the string with no luck.
@Gustav
Thanks for the code. I don't completely understand it yet, but it seems more complicated than necessary in my situation. Everyone who used the application can authenticate as the same user, I just need to know how to add the connection string. At this point, I know what string to add, just where and how to put it into the code.
Thanks
Now I know exactly what the string needs to be, but I don't know how to add it. The LTM doesn't give me that option and I searched the code for the string with no luck.The article I pointed you to shows how to create a linked table, and when doing that you can define the connection string. This is known as a "DSNless" connection, and essentially it removes the need for you to create or use a DSN. You only have to have a valid Provider/Driver installed on the machine, and use that valid PRovider/Driver in that connection string.
ASKER
Problem solved.
I solved this one myself, and like many problems, the solution is so simply once known.
I will equally split the points to everyone who tried to help. Thanks!
The solution to embed the password for MS SQL Authentication was almost too easy.
Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay. When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box. CHECK IT!
Done. You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password. No code in autoexec or onOpen events to cause development issues.
NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do. That was the distraction that kept me up all night!
I solved this one myself, and like many problems, the solution is so simply once known.
I will equally split the points to everyone who tried to help. Thanks!
The solution to embed the password for MS SQL Authentication was almost too easy.
Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay. When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box. CHECK IT!
Done. You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password. No code in autoexec or onOpen events to cause development issues.
NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do. That was the distraction that kept me up all night!
ASKER
None of the solutions offered worked, but they all kept me going until I found the solution.
Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay. When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box. CHECK IT!
Done. You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password. No code in autoexec or onOpen events to cause development issues.
NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do. That was the distraction that kept me up all night!
Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay. When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box. CHECK IT!
Done. You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password. No code in autoexec or onOpen events to cause development issues.
NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do. That was the distraction that kept me up all night!
I believe we touched on this in one of your other questions along these same lines.
The only way to store a password (outside of using VBA to create your links) is to manually delete and recreate the links. You don't necessarily have to recreate the DSN, but you must log in and select the tables, as you mention.
The only way to store a password (outside of using VBA to create your links) is to manually delete and recreate the links. You don't necessarily have to recreate the DSN, but you must log in and select the tables, as you mention.
ASKER
To add the table I used:
Get External Data
Import