SQL Server DB moved

[robert demo via AccessMonster.com]

I'm just starting to try SQL SERVER as a backend to my front end.

With my current Access FE and Jet BE, I give the user the opportunity to try
and relink to the backend if for some reason it got moved. A dialog box comes
up and the user selects the back end file to link to and linking proceeds.

With SQL Server I'm using ODBC to connect my Access .mdb front end using
trusted connection.

So, I wondered what would happen if I moved the SQL Server database. Well the
linking failed and the SQL Server logon screen appeared. There doesn't seem
to be a means on that screen for selecting the location of the database. It
shows the correct database initially but if you select it, the hourglass
appears and then after a little while the box is empty and the database for
the app is no longer a possible selection (because it has been moved). Any
other databases that were in that location are available for selection, but I
didn't try to see what might happen. OK, so why don't they give one the
opportunity to find the database so that linking can proceed.

Although you wouldn't expect to do this often, it could happen if for some
reason the Server administrators decide to move files around or move
databases to a new server. How does one go about resolving this problem?

I've been testing out Doug Steele's "How to Create a DSN-less Connection to
SQL Server...." The server name and database name are variables to be input,
but if I open a dialog box to find the database file and do locate it, I
wouldn't know how to get the server name.

Thanks for any help.

 

[Rick Brandt]

I'm just starting to try SQL SERVER as a backend to my front end.

With my current Access FE and Jet BE, I give the user the opportunity
to try and relink to the backend if for some reason it got moved. A
dialog box comes up and the user selects the back end file to link to
and linking proceeds.

With SQL Server I'm using ODBC to connect my Access .mdb front end
using trusted connection.

So, I wondered what would happen if I moved the SQL Server database.
Well the linking failed and the SQL Server logon screen appeared.
There doesn't seem to be a means on that screen for selecting the
location of the database. It shows the correct database initially but
if you select it, the hourglass appears and then after a little while
the box is empty and the database for the app is no longer a possible
selection (because it has been moved). Any other databases that were
in that location are available for selection, but I didn't try to see
what might happen. OK, so why don't they give one the opportunity to
find the database so that linking can proceed.

Although you wouldn't expect to do this often, it could happen if for
some reason the Server administrators decide to move files around or
move databases to a new server. How does one go about resolving this
problem?

I've been testing out Doug Steele's "How to Create a DSN-less
Connection to SQL Server...." The server name and database name are
variables to be input, but if I open a dialog box to find the
database file and do locate it, I wouldn't know how to get the server
name.

Thanks for any help.

Change the server location in the ODBC DSN and then the Access file doesn't need
to be changed at all.

 

[robert demo via AccessMonster.com]

I was hoping to set this up as a DSN-less linking. If I do what you suggest,
then doesn't each PC which is running the front end have to have the ODBC DSN
modified?

Thanks.

I'm just starting to try SQL SERVER as a backend to my front end.

[quoted text clipped - 29 lines]

Thanks for any help.

Change the server location in the ODBC DSN and then the Access file doesn't need
to be changed at all.

 

[Rick Brandt]

I was hoping to set this up as a DSN-less linking. If I do what you
suggest, then doesn't each PC which is running the front end have to
have the ODBC DSN modified?

Is this a one-time thing or are you planning on making a habit of moving the SQL
Server? DSN-Less is nice, but you can also have code that modifies a DSN on the
PC automatically. The nice thing about using a DSN is the ease with which you
can point to a different server (like for testing).

 

[robert demo via AccessMonster.com]

Maybe I should add that I'm actually using MSDE2000 for testing purposes. I
just realized that MSDE doesn't include any type of GUI interface like SQL
Server does. I'll be getting SQL Server Developer's version tomorrow, so
maybe it'll include the ability to find the new location of the database??

Thanks.

I was hoping to set this up as a DSN-less linking. If I do what you suggest,
then doesn't each PC which is running the front end have to have the ODBC DSN
modified?

Thanks.
[quoted text clipped - 4 lines]

Change the server location in the ODBC DSN and then the Access file doesn't need
to be changed at all.

 

[robert demo via AccessMonster.com]

Rick,

Thanks for the response. I'm concerned that my potential clients might move
the database files. I will not be doing this but they might. I don't think
they'll be happy if IT has to go to 50 user stations and modify the DSNs. I
don't know that much about it, but if this is the case, they'll be major
delays in getting that done and dissatisfaction will set in. Or do you not
see it this way?

Thanks.

 

[Rick Brandt]

Rick,

Thanks for the response. I'm concerned that my potential clients
might move the database files. I will not be doing this but they
might. I don't think they'll be happy if IT has to go to 50 user
stations and modify the DSNs. I don't know that much about it, but
if this is the case, they'll be major delays in getting that done and
dissatisfaction will set in. Or do you not see it this way?

If they have an IT department then it will be IT that sets up and maintains the
SQL Server and what reason would they have to move it? Our SQL Server has been
upgraded to new boxes and/or *physically* moved several times but it doesn't
break anything if they keep the same node and ip number.

An IT department should also have an automated way of pushing such changes to
the user's PC in those cases where it is required.

My app creates its own DSNs every time it is lauched so that is another
possibility.

 

[robert demo via AccessMonster.com]

Rick:

Is what I'm requesting difficult? MS has a paradigm with every application
that you can select a file to work on. I don't understand why it would be
difficult for a user to change the "pointer" if the database changes location.
For example, when that logon screen came up as I mentioned way at the top,
there is a box under 'Options' for selecting the database, but you can only
select from the default folder (which is MSSQL\data). I don't understand
that. It's so common to just move around the hard drive or to look at files
on a server, why wouldn't they include it there. I would think that there
has to be some reason (and, it doesn't seem an answer of 'well, it won't
happen often or should never happen suffices).

Thanks.

Rick,

[quoted text clipped - 4 lines]

if this is the case, they'll be major delays in getting that done and
dissatisfaction will set in. Or do you not see it this way?

If they have an IT department then it will be IT that sets up and maintains the
SQL Server and what reason would they have to move it? Our SQL Server has been
upgraded to new boxes and/or *physically* moved several times but it doesn't
break anything if they keep the same node and ip number.

An IT department should also have an automated way of pushing such changes to
the user's PC in those cases where it is required.

My app creates its own DSNs every time it is lauched so that is another
possibility.

 

[Rick Brandt]

Rick:

Is what I'm requesting difficult? MS has a paradigm with every
application that you can select a file to work on.

SQL Server is not a "file" it is a service. When your system connects to a
particular SQL Server it is not asking for a remote file in the same manner as
when you open a file share. Where the *files* for the database reside is not
important. Your ODBC connection is sending data requests to a *program* running
on another machine whose purpose is to receive requests for data and return that
data over a network port.

The Linked Table Manager can be used to change the location of which *service*
to use for ODBC linked tables in exactly the same manner as it can be used to
point to different files for MDB linked tables. The assumption though is that
an ODBC source already exists that points to the new location or that the user
is going to create a new one.

I don't
understand why it would be difficult for a user to change the
"pointer" if the database changes location.

It is not difficult. It is just that for ODBC Access is only storing a "pointer
to a pointer". That is it stores a pointer to an ODBC datasource as configured
in Control Panel. The whole point of ODBC DSNs is to separate the settings in
the DSN from the programs that consume them. One can argue that there are
disadvantages to this strategy, but there are also a lot of advantages.

You make the point that if 50 users have their ODBC server moved that all 50
users have to have their DSN changed. That is correct, but also consider if I
am a single user with a couple dozen separate databases all pointing to a
certain ODBC server. If that location changes I only change the one DSN in
Control Panel and all of my apps work after that one correction. With a DSNLess
strategy one has to have a custom code routine in all of those apps that allow
for a simple way to change all of the connections.

For example, when that
logon screen came up as I mentioned way at the top, there is a box
under 'Options' for selecting the database, but you can only select
from the default folder (which is MSSQL\data). I don't understand
that. It's so common to just move around the hard drive or to look
at files on a server, why wouldn't they include it there. I would
think that there has to be some reason (and, it doesn't seem an
answer of 'well, it won't happen often or should never happen
suffices).

Again, ODBC is not about "serving files" it is about "consuming services".

 

[robert d via AccessMonster.com]

Rick:

Thanks for bearing with me. I'm beginning to understand this and you are, of
course right, in that I have to stop viewing SQL Server as a file application.


I've finally been able to login from a remote computer to SQL Server via my
application (for now I'm using DSN).

Wow! I am amazed at the speed with which data is retrieved and displayed on
Computer B when using SQL Server to access data on Computer A. I've done
this test before just using Jet databases and the response is significantly
slower.

I think I'm gonna like SQL Server. Thanks for your patience and help.

Rick:

Is what I'm requesting difficult? MS has a paradigm with every
application that you can select a file to work on.

SQL Server is not a "file" it is a service. When your system connects to a
particular SQL Server it is not asking for a remote file in the same manner as
when you open a file share. Where the *files* for the database reside is not
important. Your ODBC connection is sending data requests to a *program* running
on another machine whose purpose is to receive requests for data and return that
data over a network port.

The Linked Table Manager can be used to change the location of which *service*
to use for ODBC linked tables in exactly the same manner as it can be used to
point to different files for MDB linked tables. The assumption though is that
an ODBC source already exists that points to the new location or that the user
is going to create a new one.

I don't
understand why it would be difficult for a user to change the
"pointer" if the database changes location.

It is not difficult. It is just that for ODBC Access is only storing a "pointer
to a pointer". That is it stores a pointer to an ODBC datasource as configured
in Control Panel. The whole point of ODBC DSNs is to separate the settings in
the DSN from the programs that consume them. One can argue that there are
disadvantages to this strategy, but there are also a lot of advantages.

You make the point that if 50 users have their ODBC server moved that all 50
users have to have their DSN changed. That is correct, but also consider if I
am a single user with a couple dozen separate databases all pointing to a
certain ODBC server. If that location changes I only change the one DSN in
Control Panel and all of my apps work after that one correction. With a DSNLess
strategy one has to have a custom code routine in all of those apps that allow
for a simple way to change all of the connections.

For example, when that
logon screen came up as I mentioned way at the top, there is a box

[quoted text clipped - 5 lines]

answer of 'well, it won't happen often or should never happen
suffices).

Again, ODBC is not about "serving files" it is about "consuming services".