one frontend different type of backends

Hello all!!!

For a year and a half now, we have been developing different Access
2000 applications. Most of them are access frontend and access
backends. Some of them have SQL server 2000 as a backend. There is one
application, which consists of a frontend and 3 backend mdb's.
Different companies (80) are using the application:

1. Companies with just one pc and one user.
2. Companies with a Cytrix environment.
3. Companies with Normal network (pc's with WIN 2000, XP, 98, etc).

Recently another company wanted to use the application and this
company has over 50 users and multiple locations. The first thing that
came into my mind was: "let's make another version of the application
with SQL server 2000 as backend", BUT the thing is, we actually just
want one frontend which can communicate with (with normal performance
ofcourse) both the access backend and the sql server backend. This
way, we don't have to solve problems in 2 frontends, but just in one.

My question is, is there anybody who has tried making an access
frontend being able to talk to different kind of backends?

Any help would be appreciated.

Wilco

re: one frontend different type of backends

I guess this idea is in the air, and I think many people already do this.
I am working on this at the moment, too, for the same reason as yours. I
chose to write "disconnected" front ends (containing no real data
tables), using ADO to connect to back ends regardless of their type. I
use MDBs as opposed to ADPs (I am not against ADPs, it is just that I
can do all the same from code, too, and have some added flexibility).
Once a connection is established I can just run SQL on it and feed forms
and reprts from that SQL.
It is still in the prototype stage but I plan to replace my customers'
production DBs with it soon. Basically this is one step from writing
compiled front ends because it turns out there is little native Access
functionality that I end up using. The main reason I chose to use Access
for this "universal FE" is because I really like the ease of development
and I really like the reporting facilities. I am sure it would take me
forever to hand code reports similar to Access'.
I spend a lot of time choosing back ends for my different applications.
I only once had a customer lose two days worth of data entries in a
corrupted Jet DB over LAN (the DB was mine but they want me to maintain
their backup for them because the built-in backup was worthless) but it
annoyed me beyond belief, so I am getting the feeling I will use MSDE a
lot more even for small transactional DBs. The "universal FE" should
further simplify this move (I hope).
Pavel

w wrote:[color=blue]
>
> Hello all!!!
>
> For a year and a half now, we have been developing different Access
> 2000 applications. Most of them are access frontend and access
> backends. Some of them have SQL server 2000 as a backend. There is one
> application, which consists of a frontend and 3 backend mdb's.
> Different companies (80) are using the application:
>
> 1. Companies with just one pc and one user.
> 2. Companies with a Cytrix environment.
> 3. Companies with Normal network (pc's with WIN 2000, XP, 98, etc).
>
> Recently another company wanted to use the application and this
> company has over 50 users and multiple locations. The first thing that
> came into my mind was: "let's make another version of the application
> with SQL server 2000 as backend", BUT the thing is, we actually just
> want one frontend which can communicate with (with normal performance
> ofcourse) both the access backend and the sql server backend. This
> way, we don't have to solve problems in 2 frontends, but just in one.
>
> My question is, is there anybody who has tried making an access
> frontend being able to talk to different kind of backends?
>
> Any help would be appreciated.
>
> Wilco[/color]

re: one frontend different type of backends

w.blom@mailcity.com (w) wrote:
[color=blue]
>My question is, is there anybody who has tried making an access
>frontend being able to talk to different kind of backends?[/color]

Yes, I started down this road for a client. However it's a custom app so the
thought was that once it was upsized I would then move the more used queries over to
views and stored procedures.

So yes it is possible from what I could see. But don't expect to get superb
performance from the SQL Server backend without using views and stored procedures.
Trouble is that much of my long running modules wouldn't be possible within stored
procedures anyhow. I have thousands of lines of VBA code I execute per record in
some cases.

You're probably familiar with most of these anyhow but here is what I learned.
Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

re: one frontend different type of backends

I would have thought the easiest way to achieve this would be to use a
middle tier.

The middle tier could be
1) COM components which supply the data required by your Access client.
2) An Access db which is linked to the datasource of your choice using
ODBC connections.



--
Terry Kreft
MVP Microsoft Access


"w" <w.blom@mailcity.com> wrote in message
news:d4cbe916.0405120606.4081c970@posting.google.c om...[color=blue]
> Hello all!!!
>
> For a year and a half now, we have been developing different Access
> 2000 applications. Most of them are access frontend and access
> backends. Some of them have SQL server 2000 as a backend. There is one
> application, which consists of a frontend and 3 backend mdb's.
> Different companies (80) are using the application:
>
> 1. Companies with just one pc and one user.
> 2. Companies with a Cytrix environment.
> 3. Companies with Normal network (pc's with WIN 2000, XP, 98, etc).
>
> Recently another company wanted to use the application and this
> company has over 50 users and multiple locations. The first thing that
> came into my mind was: "let's make another version of the application
> with SQL server 2000 as backend", BUT the thing is, we actually just
> want one frontend which can communicate with (with normal performance
> ofcourse) both the access backend and the sql server backend. This
> way, we don't have to solve problems in 2 frontends, but just in one.
>
> My question is, is there anybody who has tried making an access
> frontend being able to talk to different kind of backends?
>
> Any help would be appreciated.
>
> Wilco[/color]