Msde ot Jet?

[Guest]

Hi ,

I have been trying to get my head around a database issue for an application
that I am planning. The biggest problem I am faced with is a lack of clear
information and knowledge of certain issues. The scenario is this:
I have a primary accounting application that runs msde, certain tables in
msde holds vital data that I need to access for the application that I am
planning. I can simply open an ODBC connection to msde and link tables via
ODBC. My concern is that this method is sluggish and performance will be
hampered when I distribute the application over the network. There will be
about 7 heavy uses of the app that I am planning and about 5 light users. I
expect about 5 concurrent sessions at any time. The data that will be
provided by msde will not be amended in any way for the most part, but it
will be contorted in various ways through queries etc so that the data is
presented logically and pragmatically to my end users in my app.
Is using a jet database the best approach here? I am familiar with jet and I
especially like the tight integration of jet with access. I would prefer to
use this approach, provided that it does not leave me with users tapping on
the desk and giving me glares because of the lack of performance.

Is it possible to connect to msde using ADO , and then writing to a jet back
end? Will this be faster than simply linking tables via ODBC? Is it possible
to have a mixed approach like this?

Any help here would be greatly appreciated. Please feel free to knock me
over the head if I have not made myself clear, I will try and answer any
queries.

Regards

Andy

 

[Tim Ferguson]

A few answers to pick up from a non-expert...

I can simply open an ODBC
connection to msde and link tables via ODBC.

Or, even easier, use an ADP, which is specifically designed to talk to MSDE
and SQLS.

.. My concern is that this

method is sluggish and performance will be hampered when I distribute
the application over the network.

Don't see why: if you take care to minimise network traffic, all the work
is done on the server.

There will be about 7 heavy uses of
the app that I am planning and about 5 light users. I expect about 5
concurrent sessions at any time.

This is a lot for MSDE -- my version (1.0) starts to penalise access
heavily after 4 connections. Bearing in mind that a session does not need
to have a Connection object in use all the time though, you may be okay. I
think that v2 counts concurrency in a different way though.

The data that will be provided by
msde will not be amended in any way for the most part, but it will be
contorted in various ways through queries etc

Is there an opportunity for local apps just to download snapshots into
local tables and work on them from there? Over a WAN, that may be thing to
do.

Alternatively, what about MSQuery or Crystal reports or something designed
to do flexible reporting?

Is it possible to connect to msde using ADO , and then writing to a
jet back end? Will this be faster than simply linking tables via ODBC?
Is it possible to have a mixed approach like this?

If you are making small queries on large databases, I would probably just
use an ADP; if you are repeatedly hitting nearly the whole tables, then
downloading the whole lot once would start to look kinder to the network.

HTH


Tim F

 

[Guest]

Thank you Tim for taking the time to respond:

I have thought of going the ADP route but what concrens me is the built in
degradation on msde ,This is why I think jet would be preferable , and maybe
pulling data from msde using ADO. The other solution I considered is using
sql 2005 express. But I am not sure how express will work with access ,
perhaps you know a little more about express?

 

[Tim Ferguson]

I have thought of going the ADP route but what concrens me is the
built in degradation on msde ,

The only way round this is SQL Server itself; if the cash for that is
difficult, then MySQL is certainly feasible.

This is why I think jet would be preferable

Well, yes: with care jet does work well with multiple users, but there are
problems, fatal file corruption being one of them. It is _very_ sensitive
to small network problems. And a WAN would rule out a shared-Jet solution.

If you really are just carrying out decision-support types of analysis,
without any updates, then probably you could manage with just downloading
the dataset once into local (i.e. not even shared) jet databases for
further manipulation.

The other
solution I considered is using sql 2005 express. But I am not sure how
express will work with access , perhaps you know a little more about
express?

Not really: there is a lot of information on the MS site about licensing
the different flavours of SQL Server. I did read it although much of it did
not make a lot of sense to me. It would be worth while looking at the
options there.

All the best


Tim F