Solved

Bypass SQL Login

Posted on 2012-04-04
7
305 Views
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?
0
Comment
Question by:CJSilver
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

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

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.
0
 
LVL 1

Author Comment

by:CJSilver
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

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.
0
 
LVL 1

Author Comment

by:CJSilver
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?
0
 
LVL 6

Accepted Solution

by:
Patrick Tallarico earned 500 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.
0
 
LVL 1

Author Closing Comment

by:CJSilver
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!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now