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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.


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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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