Question on Access ODBC

[Blasting Cap]

I work with SQL a lot, but have very limited expertise with Access.

There is an application (dashboard?) out in a file folder that points to
an old database server. It's a SQL server with about 400,000 records on
it.

I need to change to a new server. The old one is Windows 2000, SQL
2000. New one is Windows 2003 and SQL 2000. All the other applications
on the new server work except for this one, including one that uses a
similar front-end and a similar database (also on this same server).
I've checked settings between the old & new database servers, and they
are identical. I backed up the old database and put it on the new one.
All the queries I run against it seem to work fine.

However, the problem comes when the app is run, or when you attempt to
browse one of the tables that show up in the app.

I shut down SQL on the old server and brought it up on the new one.

To my way of thinking, I need to change a connection string on the app
to have it point to the "new" server.

If I go into Linked Table manager, and select tables, each of them
(about 18 or 20) will time out & give me a message that says it failed
to connect to the database, then prompts me with a box with the old
database server name in it, and my username. I change the username to
be the one I know to work with the new database, and click the Trusted
Connection box, change the name from OLDSERVER to NEWSERVER and then
click Advanced and change the workstation ID to my computer. I click
Close once I've done all of these, and attempt to just open one of those
tables. If I hold my mouse over it, it gives me a tooltip that shows it
still pointed to the old database server, even if I just did the steps
above. If I open the table, it tells me access is denied, and gives me
the screen above with the old database server name showing.

Any idea why it perpetually points to the old server? I do not know
what the h-e-doubletoothpicks you have to do to make it stay pointed to
the right server.

I would appreciate any suggestions/assistance.

BC

 

[Jerry Porter]

BC,

I haven't tried using the linked table manager on ODBC connections. But
since it doesn't seem like you have to do this frequently, I'd try
recreating the links manually.

Right-click in the database window and choose Link Tables.
Change Files of Type to ODBC Database()
Click OK and choose your Data Source and table.

You should be able to recreate the attachments pretty quickly.

You can either delete the original attachments and recreate them this
way, or create the new ones, and then rename them to the original
names, which will cause the originals to be deleted.

Jerry

 

[Guest]

Change the name of the DSN, as well as any values.

Access reads the information from the DSN, then stores
it in the local database. Access won't know that the DSN
has changed. The information in the local database will
only be updated if the connect string changes.

DSN's are for executable's that don't have a local database
to store information, so they must use either an external
file, either a text file or the registry. Access always has a
local database, so you don't need an external DSN.

(david)

 

[Baz]

Change the name of the DSN, as well as any values.

Access reads the information from the DSN, then stores
it in the local database. Access won't know that the DSN
has changed. The information in the local database will
only be updated if the connect string changes.

DSN's are for executable's that don't have a local database
to store information, so they must use either an external
file, either a text file or the registry. Access always has a
local database, so you don't need an external DSN.

(david)

Huh? I've been pointing Access front-ends at different servers for years
just by changing the name of the server in the DSN. Never needed to change
the DSN name.

 

[Guest]

Yes, I could make it more complicated by trying to list
what is cached and what is not, but if it works for you,
I'm glad.

(david)

 

[Baz]

Yes, I could make it more complicated by trying to list
what is cached and what is not, but if it works for you,
I'm glad.

(david)

Go for it. You never know, a lesser mortal like me might succeed in
understanding a small percentage of your wisdom, in which case it'll all
have been worthwhile.

 

[Blasting Cap]

How do you get it to "take" the linked connection? It seems that no
matter how many times I change it, the changes won't be remembered or saved.

I go into ODBC in Control panel and create a system DSN there, matching
the name of my SQL database.

Then I open the application. One table returns values readily, others
do not.

I click on one table, and get the message:

Connection Failed:
SQL State '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection failed:
SQL State '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.

When I click OK, it prompts me with the SQL Server Login, which points
to the old database server (which I have stopped, trying to get it to
"point" to the new server).

If I edit those settings & point it to the new server, it will pop up
the same message box, still pointing to the old server, and do it about
a dozen times more.

Am I doing something wrong with setting up the system DSN or something?

BC

 

[Guest]

You need to re-link the database. Change the DSN name, so
that you can be sure the connect string is correctly refreshed,
re-link the database, and compact.

(Or just compact, or just re-link, or just ... there are multiple
paths to the same end).

Better yet, don't use a DSN at all:

http://www.granite.ab.ca/access/dsnlessconnections.htm

(david)

How do you get it to "take" the linked connection? It seems that no
matter how many times I change it, the changes won't be remembered or saved.

I go into ODBC in Control panel and create a system DSN there, matching
the name of my SQL database.

Then I open the application. One table returns values readily, others
do not.

I click on one table, and get the message:

Connection Failed:
SQL State '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection failed:
SQL State '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.

When I click OK, it prompts me with the SQL Server Login, which points
to the old database server (which I have stopped, trying to get it to
"point" to the new server).

If I edit those settings & point it to the new server, it will pop up
the same message box, still pointing to the old server, and do it about
a dozen times more.

Am I doing something wrong with setting up the system DSN or something?

BC

Change the name of the DSN, as well as any values.

Access reads the information from the DSN, then stores
it in the local database. Access won't know that the DSN
has changed. The information in the local database will
only be updated if the connect string changes.

DSN's are for executable's that don't have a local database
to store information, so they must use either an external
file, either a text file or the registry. Access always has a
local database, so you don't need an external DSN.

(david)

 

[aaron.kempf]

MDB is a pain in the ass.

aren't you tired of all these workarounds?

keep your data on a single server; and you'll have a much much simpler
life; there is only a single connection for Access Data Projects.

MDB is for babies.

-Aaron

You need to re-link the database. Change the DSN name, so
that you can be sure the connect string is correctly refreshed,
re-link the database, and compact.

(Or just compact, or just re-link, or just ... there are multiple
paths to the same end).

Better yet, don't use a DSN at all:

http://www.granite.ab.ca/access/dsnlessconnections.htm

(david)

How do you get it to "take" the linked connection? It seems that no
matter how many times I change it, the changes won't be remembered or saved.

I go into ODBC in Control panel and create a system DSN there, matching
the name of my SQL database.

Then I open the application. One table returns values readily, others
do not.

I click on one table, and get the message:

Connection Failed:
SQL State '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection failed:
SQL State '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.

When I click OK, it prompts me with the SQL Server Login, which points
to the old database server (which I have stopped, trying to get it to
"point" to the new server).

If I edit those settings & point it to the new server, it will pop up
the same message box, still pointing to the old server, and do it about
a dozen times more.

Am I doing something wrong with setting up the system DSN or something?

BC

Change the name of the DSN, as well as any values.

Access reads the information from the DSN, then stores
it in the local database. Access won't know that the DSN
has changed. The information in the local database will
only be updated if the connect string changes.

DSN's are for executable's that don't have a local database
to store information, so they must use either an external
file, either a text file or the registry. Access always has a
local database, so you don't need an external DSN.

(david)

I work with SQL a lot, but have very limited expertise with Access.

There is an application (dashboard?) out in a file folder that points to
an old database server. It's a SQL server with about 400,000 records on
it.

I need to change to a new server. The old one is Windows 2000, SQL
2000. New one is Windows 2003 and SQL 2000. All the other applications
on the new server work except for this one, including one that uses a
similar front-end and a similar database (also on this same server).
I've checked settings between the old & new database servers, and they
are identical. I backed up the old database and put it on the new one.
All the queries I run against it seem to work fine.

However, the problem comes when the app is run, or when you attempt to
browse one of the tables that show up in the app.

I shut down SQL on the old server and brought it up on the new one.

To my way of thinking, I need to change a connection string on the app
to have it point to the "new" server.

If I go into Linked Table manager, and select tables, each of them
(about 18 or 20) will time out & give me a message that says it failed
to connect to the database, then prompts me with a box with the old
database server name in it, and my username. I change the username to
be the one I know to work with the new database, and click the Trusted
Connection box, change the name from OLDSERVER to NEWSERVER and then
click Advanced and change the workstation ID to my computer. I click
Close once I've done all of these, and attempt to just open one of those
tables. If I hold my mouse over it, it gives me a tooltip that shows it
still pointed to the old database server, even if I just did the steps
above. If I open the table, it tells me access is denied, and gives me
the screen above with the old database server name showing.

Any idea why it perpetually points to the old server? I do not know
what the h-e-doubletoothpicks you have to do to make it stay pointed to
the right server.

I would appreciate any suggestions/assistance.

BC