re-linking Access db to new updated SQL 2005 DB

[kalensr]

DESCRIPTION of the setup:

Access database (front-end) LINKED TO TABLES in a (previously SQL 2000 DB,
working wonderfully) newly upgraded SQL 2005 DB.

MY PROBLEM:
1. Modify existing ODBC with the new MS SQL DB Server name; include new id
and password; test connection; SUCCESSFUL!

2. Open a copy of the existing Access file, link to new SQL database
instance ('05 version) - by linking tables (selecting option to save password)

3. After linked, run through and test the access application in all
operations... all is well (reading, writing to new database ('05 version of
MS SQL).

4. Close access

5. Re-open Access

6. Immediately receive dialog prompt to 'Select Data Source'

[First this is a problem; I cannot have a prompt appear at each time opening
the access db]

[Second, even after selecting the correct System DSN, Access DB fails to
connect to the MS SQL db]

MY THEORY:
The Access DB file is not retaining the DSN info specified during the
linking of the tables.

QUESTION:
Is the DSN info stored in Access Tables or local configuration files?
Are there tables contained in the MS SQL '05 DB, that store this information?

Thanks so much!

kalen dot howell at g mail dot com

 

[Guest]

Some of the info is copied from the DSN into Access. Some of the
info is copied from the Server into Access. However, your problem
is that

[Second, even after selecting the correct System DSN, Access DB
fails to connect to the MS SQL db]

This often indicates either a problem with the login, or a problem
with the connection library.

Check that you only have one connection library enabled (normally,
TCP/IP), and try it again without the option to save the password.

The behaviour of 'save password' may have changed - in fact, I
don't think that is available anymore for ODBC. You may have to
use Windows Login instead of using a saved password.

(david)

DESCRIPTION of the setup:

Access database (front-end) LINKED TO TABLES in a (previously SQL 2000 DB,
working wonderfully) newly upgraded SQL 2005 DB.

MY PROBLEM:
1. Modify existing ODBC with the new MS SQL DB Server name; include new id
and password; test connection; SUCCESSFUL!

2. Open a copy of the existing Access file, link to new SQL database
instance ('05 version) - by linking tables (selecting option to save password)

3. After linked, run through and test the access application in all
operations... all is well (reading, writing to new database ('05 version of
MS SQL).

4. Close access

5. Re-open Access

6. Immediately receive dialog prompt to 'Select Data Source'

[First this is a problem; I cannot have a prompt appear at each time opening
the access db]

[Second, even after selecting the correct System DSN, Access DB fails to
connect to the MS SQL db]

MY THEORY:
The Access DB file is not retaining the DSN info specified during the
linking of the tables.

QUESTION:
Is the DSN info stored in Access Tables or local configuration files?
Are there tables contained in the MS SQL '05 DB, that store this information?

Thanks so much!

kalen dot howell at g mail dot com

 

[kalensr]

Thank you David!

1) I am not sure where to verify or specify the connection library in Access
2003.

2) I want to explain a little more of the context around how the access db
is used. I setup the Access db by linking to the SQL Server, and then I give
copies of the access file to 40+ users (non-technical users). The users in
turn use the access db.

- The SQL Server is setup to use a specific ID and PWD; we do not maintain a
listing of valid/invalid users which removes the ability to use Windows Login
without db changes. The SQL DB is controlled and maintained by a dba that is
not in my department. So my challenge is making Access work with little or no
impact to the 40+ users and no db schema changes at all for this migration to
ms sql 2005.

I am hoping that there is something I can do on the access end to make this
work, or secondly if there is some minimum setting/configuration changes on
the ms sql side that will do the trick.

Thanks,

Kalen

Some of the info is copied from the DSN into Access. Some of the
info is copied from the Server into Access. However, your problem
is that

[Second, even after selecting the correct System DSN, Access DB
fails to connect to the MS SQL db]

This often indicates either a problem with the login, or a problem
with the connection library.

Check that you only have one connection library enabled (normally,
TCP/IP), and try it again without the option to save the password.

The behaviour of 'save password' may have changed - in fact, I
don't think that is available anymore for ODBC. You may have to
use Windows Login instead of using a saved password.

(david)

DESCRIPTION of the setup:

Access database (front-end) LINKED TO TABLES in a (previously SQL 2000 DB,
working wonderfully) newly upgraded SQL 2005 DB.

MY PROBLEM:
1. Modify existing ODBC with the new MS SQL DB Server name; include new id
and password; test connection; SUCCESSFUL!

2. Open a copy of the existing Access file, link to new SQL database
instance ('05 version) - by linking tables (selecting option to save password)

3. After linked, run through and test the access application in all
operations... all is well (reading, writing to new database ('05 version of
MS SQL).

4. Close access

5. Re-open Access

6. Immediately receive dialog prompt to 'Select Data Source'

[First this is a problem; I cannot have a prompt appear at each time opening
the access db]

[Second, even after selecting the correct System DSN, Access DB fails to
connect to the MS SQL db]

MY THEORY:
The Access DB file is not retaining the DSN info specified during the
linking of the tables.

QUESTION:
Is the DSN info stored in Access Tables or local configuration files?
Are there tables contained in the MS SQL '05 DB, that store this information?

Thanks so much!

kalen dot howell at g mail dot com

 

[kalensr]

Thank you David!

1) I am not sure where to verify or specify the connection library in Access
2003.

2) I want to explain a little more of the context around how the access db
is used. I setup the Access db by linking to the SQL Server, and then I give
copies of the access file to 40+ users (non-technical users). The users in
turn use the access db.

- The SQL Server is setup to use a specific ID and PWD; we do not maintain a
listing of valid/invalid users which removes the ability to use Windows Login
without db changes. The SQL DB is controlled and maintained by a dba that is
not in my department. So my challenge is making Access work with little or no
impact to the 40+ users and no db schema changes at all for this migration to
ms sql 2005.

I am hoping that there is something I can do on the access end to make this
work, or secondly if there is some minimum setting/configuration changes on
the ms sql side that will do the trick.

Thanks,

Kalen

Some of the info is copied from the DSN into Access. Some of the
info is copied from the Server into Access. However, your problem
is that

[Second, even after selecting the correct System DSN, Access DB
fails to connect to the MS SQL db]

This often indicates either a problem with the login, or a problem
with the connection library.

Check that you only have one connection library enabled (normally,
TCP/IP), and try it again without the option to save the password.

The behaviour of 'save password' may have changed - in fact, I
don't think that is available anymore for ODBC. You may have to
use Windows Login instead of using a saved password.

(david)

DESCRIPTION of the setup:

Access database (front-end) LINKED TO TABLES in a (previously SQL 2000 DB,
working wonderfully) newly upgraded SQL 2005 DB.

MY PROBLEM:
1. Modify existing ODBC with the new MS SQL DB Server name; include new id
and password; test connection; SUCCESSFUL!

2. Open a copy of the existing Access file, link to new SQL database
instance ('05 version) - by linking tables (selecting option to save password)

3. After linked, run through and test the access application in all
operations... all is well (reading, writing to new database ('05 version of
MS SQL).

4. Close access

5. Re-open Access

6. Immediately receive dialog prompt to 'Select Data Source'

[First this is a problem; I cannot have a prompt appear at each time opening
the access db]

[Second, even after selecting the correct System DSN, Access DB fails to
connect to the MS SQL db]

MY THEORY:
The Access DB file is not retaining the DSN info specified during the
linking of the tables.

QUESTION:
Is the DSN info stored in Access Tables or local configuration files?
Are there tables contained in the MS SQL '05 DB, that store this information?

Thanks so much!

kalen dot howell at g mail dot com