Access 2007 SQL Server Express 2008 R2 View Datasource for Form Fails (1 Viewer)

[bparkinson]

Hello,

First, thanks to all who try to help in this forum. This is a very civilized place, compared to others of it's kind.

I'm using Access 2007 against SQL Server Express 2008 R2 with linked tables. Works great, and it's not an ADP.

I installed SQL Express on a new machine. Restored the database from backup. SQL Express works fine, and schema and data restored fine.

I created an ODBC DSN with the same name as on my development machine on the new machine. DSN connects fine.

Copied the Access database to the new machine. It runs fine, re-links the tables from SQL Server at load with a problem.

The problem:

Forms that are bound to TABLES open fine. Forms that are bound to SQL Server Views won't open, because they think that the connection string to the view is pointing to my development machine, not the new machine. Access throws a connection error, then puts up a dialog to select a new database server. The new machine's instance of SQL Server is listed in the dropdown, but selecting it does not resolve the wrong server problem.

Clearly, Access treats SQL Server views differently than tables, even though it links views as if they were tables (strange, to me).

Linked tables and views in Access both have a well-formed connection string stored in a Description property. That the tables open and the views don't on the new machine is confounding.

Thanks in advance for any insight you may have.

Bob Parkinson

 

[bparkinson]

Remoted into the new machine and manually re-linked the views (and a couple of tables as well), now it all works. I'll have to write some code to do that, but that's no problem.