Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Bypass SQL Login

Posted on 2012-04-04
Medium Priority
Last Modified: 2012-04-05
I have an Access database, all the tables are links to a SQL 2000 database. There are permissions set up for this SQL database. As long as the users are set up in the SQL database they can open the reports or forms in Access without having to login to SQL.
My problem arises from a new table I created. I created a table in Access, I then exported it to my SQL database and created a link in Access to this new SQL table. I used this new table in some of the existing Queries. Now whenever I open anything in Access that is using this new table, the SQL login window appears. I do not want the SQL login window to appear every time a report is run. There is no option to remember the password on the SQL login window, the "OPTION" button is greyed out.

I think this is a permission issue in SQL, not an Access issue, but I am not sure.

Why is this one table requiring the user to login to SQL every time it is run? What can I do to stop the SQL login window from appearing every time the query is run?
Question by:CJSilver
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37808874
Did you save the password when you created the linked table?

Expert Comment

by:Patrick Tallarico
ID: 37808952
If you re-link the table, you should have a save password option in the window where you choose the table(s) from the listing from the datasource.  If you do this, Access will save the password, but it should warn you that it will store it in an unencrypted form.

Author Comment

ID: 37812267
I do not see an option to save the password. This is Access 2007. When I go to the linked table manager, I select the table and click okay, and it tells me all tables have been successfully refreshed, it does not ask for a password. There is no place I can tell it to remember the password.
Linked Table Manager In fact, I can open the table in Access and it does not ask for a password, only when I run the query does it ask for the password.  But in the SQL password window there is no place for me to tell it to remember the password.
SQL Login
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Expert Comment

by:Patrick Tallarico
ID: 37812412
Try re adding the table from scratch. By re adding it from scratch, you should have a save password option.

Author Comment

ID: 37812631
I tried, it does not give me the option to save the password. It gives the exact same login window as the one I showed in the last post.

I am going to call this problem table, TABLE_1, just to make it clearer.

This is getting more confusing. As I mentioned earlier, I can open TABLE_1 in Access and it does NOT ask for a password, it is only when I run a query that it asks for the password.
This query is made up of many different tables, ALL of the tables are from the same SQL database. If I take out TABLE_1 from this query, the  query no longer asks for a password. If I put TABLE_1 back, it asks for a password. What makes this more confusing is that if I create a new query that only contains TABLE_1, I can run this query and it does NOT ask for a password. It only asks for the password when the query with multiple tables is run, but as I mentioned above, it only asks for the password if TABLE_1 is in the query.

I am not sure if this is an Access issue or a SQL issue.
This SQL database is the database for my manufacturing and accounting software, it has hundreds of tables. If I add any of these tables, It asks me to login when I add the table, I do not have to save the password, and from that point on, I never have to log in to it again. I assume it is because the users are set up with permissions in the SQL database so as long as they have permission they do not have to log in every time a report is run.

But this table is different, it is not one that existed when the users were set up in SQL, it is a table that I created and exported from Access to SQL. Do I need to do something in SQL so this table behaves like the tables that were already in the SQL database?

Accepted Solution

Patrick Tallarico earned 2000 total points
ID: 37812715
I apologize, i don't think i was clear when i said to re add the table.  I meant that you should re link the table from the datasource.  Remove the table from your access project and then re add the table through the import wizard, making sure to link to the table rather than a straight import. This is the process where you should have the option to save the password.  The check box should appear to the right of the table listing from which you can select the table s.

Author Closing Comment

ID: 37813713
I understood what you meant, I had been looking for the save password check box in the window where the password is typed, I did not notice the "save password" check box in the link table window.

Thank you, you have solved my problem!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question