Access ODBC He!! - I need some help with this thing....

[Blasting Cap]

I am having a heck of a time trying to figure out why I cannot change
servers on this application. I posted this a week or so ago, but so
far, none of the suggestions that were made have resolved the problem.
I don't know enough about Access and how it handles linked tables to see
what is going on, although what I "think" is wrong is a connectstring to
the new server.

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 (in Query Analyzer) I run against it seem to work fine.

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

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.

I cannot get the application to point to the new server.

When I hold the mouse over a table name in the application, this is what
shows up in the tooltip:

"ODBC;Description=Consumer;Driver=SQL Server; Server=MyOldServer;
App=Microsoft Access;WSID=ABCD-K54T1F; Database=MyCompany Consumer
Database; Trusted_Connection=Yes;Table=dbo.Individual"

One other thing I've found by holding the mouse over various tables in
that application is that some appear to be pointed to the new server,
others do not. The ones that do not appear to be pointed to the new
server have the old server name in that same tooltip. Some of the ones
that appear to be connecting to the right database do not have any
server name specified in it.

Another thing I have noticed is that the description in those things
sometimes differs. Some say "Consumer Database" others say "Consumer"
some do not have this description specified at all.

I tried to re-link the tables (right click on the Tables option under
Objects) and select Link tables, point it to the new server, and it
ended up creating 2 or 3 instances of the same table. I'd have
dbo.individual (pointing to the old database), and dbo.individual1,
individual2 and individual3 pointing to the new one.

I'd appreciate any suggestions or help.

BC

 

[Jeff L]

When you go to the Link Table Manager, select the tables that are
pointing to the old server. Down in the bottom left corner there is a
check box labeled "Always prompt for new location". Check it. You
will then be prompted for your ODBC connection, username and password.

OR

What you have done with the Link table manager is create multiple
copies of the same table. All you need to do is rename dbo.individual1
to dbo.individual. You will be prompted to replace the original
dbo.individual, say Yes to replace and you should be fine. Delete the
other copies.

 

[aaron.kempf]

MDB is a total piece of shit.

plus dipstick here forgot that you may need to go throgh manually and
change the connection strings on various Sql-Passthru queries.

MDB is just too complex; too many bugs; too many workarounds.

Use ADP and you just would have to change the server name in a single
place.

Or shit; use CNAMEs and you woudln't have to change it anywhere

-Aaron