M
Marcel
Actually Arvin the original poster says "Or is it best to use SQL server or
something?"
Let’s go through some of the logic that the posters have been suggesting
Replication, hmm complex, not reliable, replication can end up being mind
numbingly slow and would require reworking the application.
Terminal Services wow over a phone line yep lets make international phone
calls to watch the application hardly work with expensive international call
costs, go team. Or maybe they can get terminal Services to work over VOIP.
What about Terminal Services across the internet, the though of exposing and
a computer onto the internet with terminal services access is a frightening
idea
No, I hear you say that would be a security risk you would use VPN. Why
would I run Terminal Services through a VPN?? What a double performance hit
even before you open the Access App.
They say no use the VPN and let the international office access the DB
directly across the internet through the VPN. What as in file server? No
that wouldn't work; as the application grinds to a halt.
OR Maybe we should split the program so that we have a total separation of
interface/logic and all non data objects in the Access Application and all
the data tables’ queries, either on SQL Server or MSDE.
Oh I hear you crying out but that would require a total rebuild and SQL
server is expensive. Well no it wouldn’t require a rebuild as you would use
the feature in access designed for this purpose "smart Microsoft of
foreseeing this and future proofing the applications that developers have
work so hard to build"
Go Tool \Database Utilities\Upsize wizard
You ask what about SQL server the costs money? True but not that much but
there is always the FREE MSDE, talk about future proofing, go Microsoft.
arrhh, but I hear you say "Without Terminal Services or an ASP front-end,
JET will never perform as fast as any server based DBMS" true
Jet wont run as fast as any server based DBMS/MSDE, that’s why "smart
Microsoft “ADP files don’t use JET "go Microsoft".
True, ADP files don’t use JET nor depends on JET there for Arvin there is no
JET overhead killing the performance of the Database In actual fact APD with
SQL/MSDE back end could be thought of in the same way as ASP, their
difference being that ASP also transmits the interface constructs and data
ADP file are designed exactly for the issue the original poster. ADP file
are client server by design where as mdb files are file server by design
ADP only transmits the db query result unlike jet which again I have to say
transmits significantly more than just sending the Indexes
Ill quote from the book (Access 2003 vba, a programmer’s reference published
by wrox and written by some MVPs)
And I just love how one of the posters suggests Microsoft don’t even know
their own product, anyhow this is what happens;
"1) (Access using JET) requests primary Keys for each data table SEPARATELY
";
2) join the key Data locally on the client machine;
3) Request ALL needed field data from each table SEPARATELY based on the key
fields;
4) Join the requested data together in a local recordset and display to the
user"
And here is the good part
"The end result is that if you have a large compound primary keys defined
for each table, Access can pull down a lot of data that needs to be joined
locally BEFORE it even begins to retrieve data that will eventually be
displayed to the user."
It continues to give the grim details "do yourself a favour and buy the
book" Chapter 17 Understanding Client Server development with VBA.
Anyhow
James if your still there good luck and I hope it goes well for you.
something?"
Let’s go through some of the logic that the posters have been suggesting
Replication, hmm complex, not reliable, replication can end up being mind
numbingly slow and would require reworking the application.
Terminal Services wow over a phone line yep lets make international phone
calls to watch the application hardly work with expensive international call
costs, go team. Or maybe they can get terminal Services to work over VOIP.
What about Terminal Services across the internet, the though of exposing and
a computer onto the internet with terminal services access is a frightening
idea
No, I hear you say that would be a security risk you would use VPN. Why
would I run Terminal Services through a VPN?? What a double performance hit
even before you open the Access App.
They say no use the VPN and let the international office access the DB
directly across the internet through the VPN. What as in file server? No
that wouldn't work; as the application grinds to a halt.
OR Maybe we should split the program so that we have a total separation of
interface/logic and all non data objects in the Access Application and all
the data tables’ queries, either on SQL Server or MSDE.
Oh I hear you crying out but that would require a total rebuild and SQL
server is expensive. Well no it wouldn’t require a rebuild as you would use
the feature in access designed for this purpose "smart Microsoft of
foreseeing this and future proofing the applications that developers have
work so hard to build"
Go Tool \Database Utilities\Upsize wizard
You ask what about SQL server the costs money? True but not that much but
there is always the FREE MSDE, talk about future proofing, go Microsoft.
arrhh, but I hear you say "Without Terminal Services or an ASP front-end,
JET will never perform as fast as any server based DBMS" true
Jet wont run as fast as any server based DBMS/MSDE, that’s why "smart
Microsoft “ADP files don’t use JET "go Microsoft".
True, ADP files don’t use JET nor depends on JET there for Arvin there is no
JET overhead killing the performance of the Database In actual fact APD with
SQL/MSDE back end could be thought of in the same way as ASP, their
difference being that ASP also transmits the interface constructs and data
ADP file are designed exactly for the issue the original poster. ADP file
are client server by design where as mdb files are file server by design
ADP only transmits the db query result unlike jet which again I have to say
transmits significantly more than just sending the Indexes
Ill quote from the book (Access 2003 vba, a programmer’s reference published
by wrox and written by some MVPs)
And I just love how one of the posters suggests Microsoft don’t even know
their own product, anyhow this is what happens;
"1) (Access using JET) requests primary Keys for each data table SEPARATELY
";
2) join the key Data locally on the client machine;
3) Request ALL needed field data from each table SEPARATELY based on the key
fields;
4) Join the requested data together in a local recordset and display to the
user"
And here is the good part
"The end result is that if you have a large compound primary keys defined
for each table, Access can pull down a lot of data that needs to be joined
locally BEFORE it even begins to retrieve data that will eventually be
displayed to the user."
It continues to give the grim details "do yourself a favour and buy the
book" Chapter 17 Understanding Client Server development with VBA.
Anyhow
James if your still there good luck and I hope it goes well for you.