Access 2003 - ODBC File Data Source - Relink every Access Session
Mike L
Login". There is a check box "Use Trusted Connection", a Login ID and
Password field. The login is populated but the password field is blank.
There is an option button that expands the the dialog box, but the fields are
not related to the problem.
There is no option to save password on the dialog box. There is also no
option to save the password in creating the File Data Source.
When I check the "Use Trusted Connection", I get a "Connection failed:....
Access to selected database has been denied."
How do I save the password?
"Frank Hickman" wrote:
> I don't think that the password is saved in a File Data Source. I have
> linked tables to both SQL Server and Oracle that I do not have to re-link
> every time but I use a System DSN.
>
> --
> ============
> Frank Hickman
> NobleSoft, Inc.
> ============
> Replace the _nosp@m_ with @ to reply.
>
>
> "Mike L" <Mi...@discussions.microsoft.com> wrote in message
> news:9C2A5D4A-2293-498E...@microsoft.com...
> > Why must I have to relink tables everytime I open my Access 2003 project?
> >
> > I go to "Linked Table Manager" select all tables, have "Always prompt for
> > new location" checked, select my ODBC file under "File Data Source", and
> > connects everytime, the problem is I have to do this everytime I open my
> > Project in Access 2003 that connects to my SQL 2000 database.
>
>
>
Frank Hickman [MVP]
> After I select the File Data Source a dialog box pops up, titled "SQL
> Server
> Login". There is a check box "Use Trusted Connection", a Login ID and
> Password field. The login is populated but the password field is blank.
> There is an option button that expands the the dialog box, but the fields
> are
> not related to the problem.
>
> There is no option to save password on the dialog box. There is also no
> option to save the password in creating the File Data Source.
> When I check the "Use Trusted Connection", I get a "Connection failed:....
> Access to selected database has been denied."
>
> How do I save the password?
>
<snip>
You cannot save the password in a File DSN. You need to use either a User
DSN or System DSN. Alternativly, if you want to use a Trusted Connection,
then you need to give your network login/machine login access rights to SQL
Server.
--
============
Frank Hickman
Microsoft MVP
Mike L
Access, tried to open a table and a SQL Server Login box popped up.
Login ID: sa
Password:
When I click on "Use Trusted Connection" I get Connection failed error.
When I type in the sa password, I connect.
Does this mean I have to type in the password everytime I start my Access
project to connect?
Frank Hickman [MVP]
>I tried System DSN, entered the sa and password. Connected to SQL, then
>close
> Access, tried to open a table and a SQL Server Login box popped up.
>
> Login ID: sa
> Password:
>
> When I click on "Use Trusted Connection" I get Connection failed error.
> When I type in the sa password, I connect.
>
> Does this mean I have to type in the password everytime I start my Access
> project to connect?
>
No. After you select your DSN and enter the connection information, an
other window comes up for you to select a table from, make sure that the
Save Password check box is selected on this window.
<snip>
Mike L
Two parts to look at; "Creating the ODBC connection" and "Access connecting
to SQL Server".
Creating the ODBC connection
Start-Control Panel-Administrative Tools-Data Sources(ODBC)
ODBC Data Source Adminstrator dialog box pops up.
Click on System DSN, click Add, select SQL Server, click Finish.
Box pops up, titled "Create a New Data Source to SQL Server."
Enter in Name, Description and select Server, click Next.
Select "With SQL Server authentication using a Login ID and password entered
by the user."
By default the "Connect to SQL Server to obtain default settings..." is
checked.
Enter in Login ID: sa
Enter in Password: (I enter in password)
Click Next, "Change the default database to:" I select the database.
"Attach database file:", I don't touch this.
There are other options on this step but none that have anything to do with
saving a password. I click next.
"Change the language of SQL....", defaults to English, I don't touch.
"Use strong encryption for data", I don't touch
"Perform translation for character data", default checked, I don't touch.
"Use regional settings...", I don't touch.
"Save long running queries to the log file:", defaults to local drive, I
don't touch.
"Log ODBC dirver...", defaults to local drive, I don't touch.
Click finish. Click on "Test Data Source...", comes back "Tests Completed
Successfully!", I click on OK. I'm now back at "ODBC Data Source
Administrator".
Part Two: Access connecting to SQL Server
I open Access Project, I double click on a table to open it, dialog box pops
up, "SQL Server Login", Login ID: sa, and the Password is blank. I type in
the password click OK, and the table appears with the records.
I close Access Project, open Access Project again, click on Tools, Database
Utilities, Linked Table Manager. Dialog box pops up, I click on "Select
All", by default the "Always prompt for new location." is not checked, I
leave it unchecked, I then click "OK".
"SQL Server Login" pops up, with the Data Source and then the name I called
my Data Source, "Use Trusted Connection" is unchecked. Login ID has sa.
Password is blank. If I enter Password, then click OK, msgbox "All selected
linked tables were successfully refreshed.", then I click OK. Also in the
options there is no reference to saving a password. It has, Database:,
Language:, Application Name:, and WorkStation ID:
Please don't give up on me, Frank. We might figure this out soon. Thanks
Frank Hickman [MVP]
This part is all correct.
> Part Two: Access connecting to SQL Server
>
> I open Access Project, I double click on a table to open it, dialog box
> pops
> up, "SQL Server Login", Login ID: sa, and the Password is blank. I type in
> the password click OK, and the table appears with the records.
>
Ah, I see what's going on. Your table is already linked to Access so when
you double click on it, it requests the password because when it was
originally linked the Save Password was not checked.
Do this, remove the link to the table then right click in the table view.
Click on the Link Tables option and select your ODBC data source as usual.
When prompted, enter the password. When the Link Tables dialog pops up to
select the table to link to, select the table and then check the Save
Password checkbox. This is the only place it will be for linked tables.