Connect to multiple SQL Server databases

[ecovindaloo via AccessMonster.com]

I currently have multiple access databases that are linked to different
access databases also.

Because of performance issues (i.e. servers in different locations), I'm
going to convert these access databases to access projects. So I wanted to
know if I will be able to link to different SQL Server databases? And if so
is it possible to do this through code, because this will be distributed to
over 50 users.

Thanks in advance for the help.

 

[Sylvain Lafontaine]

An ADP project can cannect to only one server at a time. Of course, you can
also make your own queries to any databases using ADO and a proper
connection string or you can etablish a linked server.

Of course, there is also this subtle distinction between multiple databases
on the same server or on different server. I never worked with ADP against
multiple databases located on the same server; so I cannot tell if you will
or not encounter problems but you can try it, if you want.

The fact that you want to work against multiple databases looks also
suspicious in your case. If you are doing this in order to circumvent the
database size limitation of SQL-Server Express instead of buying a regular
edition of SQL-Server, then you don't really save any money.

In your case, you should also consider the possibility of using Terminal
Server or Citrix instead of migrating to SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[ecovindaloo via AccessMonster.com]

Thanks for the quick response. The reason I was thinking of having multiple
SQL Server databases was because right now I have three different access
databases.

My logic for this was because there are two different servers on each coast.
Right now everyone is using a server on the East Coast. So the West Coast
users are having significant speed issues. So I was going to use replication
of the SQL Server databases. This was going to solve the speed issues.

How would Citrix or Terminal Server solve this problem?

An ADP project can cannect to only one server at a time. Of course, you can
also make your own queries to any databases using ADO and a proper
connection string or you can etablish a linked server.

Of course, there is also this subtle distinction between multiple databases
on the same server or on different server. I never worked with ADP against
multiple databases located on the same server; so I cannot tell if you will
or not encounter problems but you can try it, if you want.

The fact that you want to work against multiple databases looks also
suspicious in your case. If you are doing this in order to circumvent the
database size limitation of SQL-Server Express instead of buying a regular
edition of SQL-Server, then you don't really save any money.

In your case, you should also consider the possibility of using Terminal
Server or Citrix instead of migrating to SQL-Server.

I currently have multiple access databases that are linked to different
access databases also.

[quoted text clipped - 9 lines]

Thanks in advance for the help.

 

[Sylvain Lafontaine]

Hum, my last message doesn't seem to show up, so here's a second try:

Thanks for the quick response. The reason I was thinking of having
multiple
SQL Server databases was because right now I have three different access
databases.

Working with three databases located on the same server or with three
databases located on three different servers are two very different things
and each situation will have its own share of problems. However, from your
description of the problem, it's hard to say which one of these two
possibilities is your case here.

My logic for this was because there are two different servers on each
coast.
Right now everyone is using a server on the East Coast. So the West Coast
users are having significant speed issues. So I was going to use
replication
of the SQL Server databases. This was going to solve the speed issues.

You're right, using replication will solve the speed issues for people
working on the remote LAN. As far as ADP is concerned, an ADP database
project won't care if the database that it's connecting to is replicated or
not. However, as you will be using replication, you might - or might not -
be hit by the many problems that replication can bring on some occasions.

How would Citrix or Terminal Server solve this problem?

TS/Citrix are advanced remote desktop functionality. They are able to solve
the problem of using Access over the WAN (Wide Area Network) quite easily.
The speed will not be as perfect as with replication but you save yourself
from the many problems that replication can bring on many situations. Also,
with TS/Citrix, you don't have to switch to SQL-Server and you can keep your
old Access/JET backend. (However, as this is for a company, using
Access/JET as the backend is not necessarily the best choice that you can
make in life.). You can search Google for Access/Terminal Server.

Another possibility would be to use synchronisation instead of replication.
Much less trouble than replication but still can be troublesome.

Finally, you could also use an ADP project to work over the WAN; however,
this requires some deep understanding on how to optimize an ADP project;
otherwise, you might very well end up suffering the same speed problems as
with a regular Access frontend.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tony Toews [MVP]]

Because of performance issues (i.e. servers in different locations), I'm
going to convert these access databases to access projects.

I'm not the expert that Sylvain is but I wonder if converting to ADPs
would make any significant performance difference.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Bob McClellan]

ecovindaloo,

explore using stored procedures to return the rows you want.
The stored procedures can reside in a single db and,.. provided the users
security permits,...
you can query any db on any server.

you connect the .adp to a sql db and use ssms to write your procedures,
triggers, functions and whatever
else you need.

hth,
bob.

 

[Debra]

I currently have multiple access databases that are linked to different
access databases also.

Because of performance issues (i.e. servers in different locations), I'm
going to convert these access databases to access projects. So I wanted
to
know if I will be able to link to different SQL Server databases? And if
so
is it possible to do this through code, because this will be distributed
to
over 50 users.

Thanks in advance for the help.

 

[Debra]

I currently have multiple access databases that are linked to different
access databases also.

Because of performance issues (i.e. servers in different locations), I'm
going to convert these access databases to access projects. So I wanted
to
know if I will be able to link to different SQL Server databases? And if
so
is it possible to do this through code, because this will be distributed
to
over 50 users.

Thanks in advance for the help.

 

[Sylvain Lafontaine]

No problem here running one of my program over the WAN using either directly
the ADP project or TS on a very ordinary high speed internet connection.
The ADP without TS is a very little bit slower opening a form, moving
between record or writing a new record but once the form is open, it doesn't
exhibit the usual jerks of TS. Both modes are used and one person is
obligated to use the direct ADP version because the hosting company has not
been able to set up correctly on the TS the driver for the special printer
that he need to use. (I'm not saying here that the failure of installing
the driver has been the responsability of the hosting company because it's
quite possibly that's the driver itself that is faulty. I'm only saying
that this printer doesn't work correctly/reliably over TS at the present
time.).

As far as I can remember (it has been four years now), this is a very
ordinary, plain vanilla ADP project with no unbound form or any other trick
to accelerate the communication over the Internet.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[David W. Fenton]

I'm not the expert that Sylvain is but I wonder if converting to
ADPs would make any significant performance difference.

Based on Microsoft's recommendations, I don't think they's say that
it would.

It will be interesting to see what happens to ADPs in the next
version of Access (i.e., the one after 2010).

 

[Paul Shapiro]

Based on Microsoft's recommendations, I don't think they's say that
it would.

It will be interesting to see what happens to ADPs in the next
version of Access (i.e., the one after 2010).

Any approach to working with SQL Server as the backend should give good
performance over a WAN as long as you adjust forms to minimize the number of
round-trips to the database and the amount of data being retrieved. I found
it necessary to change from the typical Access approach of using Select *
From MyTable as a form record source to letting the user specify a single
row (or a small set of rows) to be retrieved and then updating the form
record source. So the base form record source could be Select * From MyTable
Where 1=0, returning no rows. An unbound textbox or combo box lets the user
select a record to see, and the after-update event code updates the form
record source to retrieve the specified row, or hopefully small set of rows.
If the underlying table has more than a few thousand rows, forms open faster
if the unbound combo box is only filled after the user types a few
characters, again limiting the data retrieved. Report performance depends on
how much data is being retrieved, and it can be harder to improve, but for
most apps I've done the reporting is used much less frequently than data
management.

 

[ecovindaloo via AccessMonster.com]

Hi Paul,

Thanks for the info. I usually try to do what you suggested with the
recordsource for the form. But I don't think I'm going to be able to do that
with these forms. At this point there are only a 1,000 records so it's not
an issue at this point.

Do you think it's a good idea to stick with the normal version of Access and
just link to the three database in SQL Server or going with an adp setup?

[quoted text clipped - 8 lines]

It will be interesting to see what happens to ADPs in the next
version of Access (i.e., the one after 2010).

Any approach to working with SQL Server as the backend should give good
performance over a WAN as long as you adjust forms to minimize the number of
round-trips to the database and the amount of data being retrieved. I found
it necessary to change from the typical Access approach of using Select *
From MyTable as a form record source to letting the user specify a single
row (or a small set of rows) to be retrieved and then updating the form
record source. So the base form record source could be Select * From MyTable
Where 1=0, returning no rows. An unbound textbox or combo box lets the user
select a record to see, and the after-update event code updates the form
record source to retrieve the specified row, or hopefully small set of rows.
If the underlying table has more than a few thousand rows, forms open faster
if the unbound combo box is only filled after the user types a few
characters, again limiting the data retrieved. Report performance depends on
how much data is being retrieved, and it can be harder to improve, but for
most apps I've done the reporting is used much less frequently than data
management.

 

[Paul Shapiro]

I've used ADP's, but I don't have any experience with direct linking to make
a comparison. Plenty of people have suggested that either approach works
fine, so I think it's ok to try whichever you prefer. You mentioned 3
databases in SQL Server. Is it really 3 db, or 3 tables? If it's 3 separate
databases required in a single Access application, you probably have an
easier time with linked tables in an Access mdb. The adp presumes you are
working with a single database. You could probably work around that, but
I'll guess it would be easier with linked tables.

You're right that 1000 rows should not be a serious performance issue.

Hi Paul,

Thanks for the info. I usually try to do what you suggested with the
recordsource for the form. But I don't think I'm going to be able to do
that
with these forms. At this point there are only a 1,000 records so it's
not
an issue at this point.

Do you think it's a good idea to stick with the normal version of Access
and
just link to the three database in SQL Server or going with an adp setup?

Because of performance issues (i.e. servers in different
locations), I'm going to convert these access databases to access

[quoted text clipped - 8 lines]

It will be interesting to see what happens to ADPs in the next
version of Access (i.e., the one after 2010).

Any approach to working with SQL Server as the backend should give good
performance over a WAN as long as you adjust forms to minimize the number
of
round-trips to the database and the amount of data being retrieved. I
found
it necessary to change from the typical Access approach of using Select *
From MyTable as a form record source to letting the user specify a single
row (or a small set of rows) to be retrieved and then updating the form
record source. So the base form record source could be Select * From
MyTable
Where 1=0, returning no rows. An unbound textbox or combo box lets the
user
select a record to see, and the after-update event code updates the form
record source to retrieve the specified row, or hopefully small set of
rows.
If the underlying table has more than a few thousand rows, forms open
faster
if the unbound combo box is only filled after the user types a few
characters, again limiting the data retrieved. Report performance depends
on
how much data is being retrieved, and it can be harder to improve, but for
most apps I've done the reporting is used much less frequently than data
management.

 

[ecovindaloo via AccessMonster.com]

It's actually three separate databases not three tables. Each one is a
separate program but two of the access front ends use tables in the other
databases.

I'm waiting to go back to this consulting job to finish this project. But I
think I'm going to try converting the access databases to SQL Server
databases and linking the tables.

I've used ADP's, but I don't have any experience with direct linking to make
a comparison. Plenty of people have suggested that either approach works
fine, so I think it's ok to try whichever you prefer. You mentioned 3
databases in SQL Server. Is it really 3 db, or 3 tables? If it's 3 separate
databases required in a single Access application, you probably have an
easier time with linked tables in an Access mdb. The adp presumes you are
working with a single database. You could probably work around that, but
I'll guess it would be easier with linked tables.

You're right that 1000 rows should not be a serious performance issue.

Hi Paul,

[quoted text clipped - 38 lines]

 

[Mary Chipman [MSFT]]

If the SQL Server databases are all on the same server, you can link
to one database, and within that database, create views or stored
procedures that access data in the other two databases. You use the
three-part name instead of just schemaname.objectname:

SELECT colname FROM MyDb.MySchema.MyTable

--Mary

It's actually three separate databases not three tables. Each one is a
separate program but two of the access front ends use tables in the other
databases.

I'm waiting to go back to this consulting job to finish this project. But I
think I'm going to try converting the access databases to SQL Server
databases and linking the tables.

I've used ADP's, but I don't have any experience with direct linking to make
a comparison. Plenty of people have suggested that either approach works
fine, so I think it's ok to try whichever you prefer. You mentioned 3
databases in SQL Server. Is it really 3 db, or 3 tables? If it's 3 separate
databases required in a single Access application, you probably have an
easier time with linked tables in an Access mdb. The adp presumes you are
working with a single database. You could probably work around that, but
I'll guess it would be easier with linked tables.

You're right that 1000 rows should not be a serious performance issue.

Hi Paul,

[quoted text clipped - 38 lines]

most apps I've done the reporting is used much less frequently than data
management.

 

[ecovindaloo via AccessMonster.com]

I may wind doing what you suggested in the end. The problem was that when
this project started it was only going to be used by five people in one
location using the same server. Now it may be used by as many as 100 users
and in different locations.

I'm trying to avoid having to rewrite all the original code. That's why I
was thinking of just linking the SQL tables through code and then I wouldn't
have to rewrite the existing code.

If the SQL Server databases are all on the same server, you can link
to one database, and within that database, create views or stored
procedures that access data in the other two databases. You use the
three-part name instead of just schemaname.objectname:

SELECT colname FROM MyDb.MySchema.MyTable

--Mary

It's actually three separate databases not three tables. Each one is a
separate program but two of the access front ends use tables in the other

[quoted text clipped - 20 lines]

 

[David W. Fenton]

I found
it necessary to change from the typical Access approach of using
Select * From MyTable as a form record source

Uh, even with a Jet back end, it's wise to not do that! The same
things that make the process efficient with a server back end also
make Access/Jet/ACE more efficient. And it also means that upsizing
is much less painful. Personally, I've been designing all my Jet
apps with upsizing in mind since 1998 or so. The result has been
more efficiency with Jet, but also ease of upsizing in the few cases
where that's happened.

 

[David W. Fenton]

Do you think it's a good idea to stick with the normal version of
Access and just link to the three database in SQL Server or going
with an adp setup?

Microsoft recommends MDB/ACCDB/ODBC over ADP/ADO/OLEDB, so I think
I'd take their advice.

 

[David W. Fenton]

If the SQL Server databases are all on the same server, you can
link to one database, and within that database, create views or
stored procedures that access data in the other two databases. You
use the three-part name instead of just schemaname.objectname:

SELECT colname FROM MyDb.MySchema.MyTable

There's also linked servers, no?