MS Access Database with External Data

Author

SQL Server DB moved

robert demo via AccessMonster.com

2005-09-20, 8:24 pm

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.

--
Message posted via http://www.webservertalk.com

Rick Brandt

2005-09-20, 8:24 pm

robert demo via webservertalk.com wrote:
> 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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

robert demo via AccessMonster.com

2005-09-20, 8:24 pm

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.

Rick Brandt wrote:
>[quoted text clipped - 29 lines]
>
>Change the server location in the ODBC DSN and then the Access file doesn't need
>to be changed at all.
>

--
Message posted via http://www.webservertalk.com

Rick Brandt

2005-09-20, 8:24 pm

robert demo via webservertalk.com wrote:
> 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).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

robert demo via AccessMonster.com

2005-09-20, 8:24 pm

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.

robert demo wrote:[color=darkred
]
>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]

--
Message posted via http://www.webservertalk.com

robert demo via AccessMonster.com

2005-09-20, 8:24 pm

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 wrote:
>
>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).
>

--
Message posted via http://www.webservertalk.com

Rick Brandt

2005-09-20, 8:24 pm

robert demo via webservertalk.com wrote:
> 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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

robert demo via AccessMonster.com

2005-09-20, 8:24 pm

Message-ID: < 54AC3B14DF6A8@webser
vertalk.com>
X-Original-NNTP-Posting-Host: 64.121.198.44
X-Complaints-To: http://www.webservertalk.com/Uwe/NB/Abuse.aspx
Complaints-To: http://www.webservertalk.com/Uwe/NB/Abuse.aspx
Newsgroups: microsoft.public.access.externaldata
NNTP-Posting-Host: mail.advenet.com 216.32.72.34
Path: TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.access.externaldata:64659

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 Brandt wrote:
>[quoted text clipped - 4 lines]
>
>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.
>

--
Message posted via http://www.webservertalk.com

Rick Brandt

2005-09-21, 7:24 am

robert demo via webservertalk.com wrote:
> 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".

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

robert d via AccessMonster.com

2005-09-24, 11:24 am

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 Brandt wrote:
>
>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.
>
>
>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.
>
>[quoted text clipped - 5 lines]
>
>Again, ODBC is not about "serving files" it is about "consuming services".
>

--
Message posted via http://www.webservertalk.com

Sponsored Links