Solved

Remove Saved Password for Linked Table

Posted on 2008-10-24
4
471 Views
Last Modified: 2008-10-25
I just used the SQL Server Migration Assistant for Access to upsize my Access database back end to SQL Server.  The password to the linked tables was saved in Access, so that when you open the database, no password is required.  However, I would like a password to be required (a SQL Server user-based password, not an Access database password).  How do I remove the saved password from the database so that it asks for a user and password when the database is opened?

I know I could disconnect and reconnect the tables, opting to not save the password, but that would require me to create a DSN, and I prefer to  have this be DSN-less.

Thanks!
0
Comment
Question by:Orcadian
[X]
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
  • 2
4 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22801723
Hello Orcadian,

What type of authentication is on the database, trusted, sql or mixed mode?


Regards,

Jim
0
 

Author Comment

by:Orcadian
ID: 22801729
SQL Authentication
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22801760
I'm not 100% sure about this but I think that the password is stored in the registry, not the database, in ODBC.INI
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 22802062
You will have to delete and recreate the links.

Use DAO code like this:

Set dbs = CurrentDb
Set tblTable = dbs.CreateTableDef(NameForTableInAccess)
tblTable.Connect = YourConnectionString
tblTable.SourceTableName = TableNameAsNamedInBackEnd
dbs.TableDefs.Append tblTable

For connectionstring:

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer

/gustav

0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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 earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 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