Access Projects?
-
Hi
I am considering migrating a database from Access 2000 to Access Project 2003 (.adp). I am concerned that as more people use the database (already linked to SQL Server 2000 tables) the speed will diminish.
Can anybody tell me if they have made the change to Access Project and feel positive about it? This will mean a lot of work, and I don't want to start if it is going to create more problems than it will solve.
Many thanks
Paul
-
Also, if I can just continue this a moment. I have been given the following excerpt from a forum discussing a similar issue. The contributor replying to a question whether to use .mdb or .adp says:
'I would use
linked .mdbs over .ADPs.
MS has been recommending this for over a year now. Seems like .adps are
another dead end technology.'
Can anybody hazard an opinion as to whether .adp is potentially 'dead end technology'?
Paul
-
Without knowing the motivation of the poster of the above quote I can only hazard the following:
Access is GREAT at providing quick, dirty, small applications rapidly. I don't think that Access is the best place to create/manage SQL objects. Possibly MS realized that.
As far as which to use ADP vs. MDB I prefer the following:
Create the MDB without linking tables or building queries. Create your forms and your reports to use VBA code. The VBA code will use SQL stored-procedures.
Once this is accomplished create an MDE. This will preserve your code ad infinitum and will ensure that the customer will NOT be able to MODIFY your code.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi and thanks for your useful comments. I guess I will be staying with the mdb.
I am used to using Access 2000 (just about to install 2003) with tables linked to SQL Server 2000 and using Access queries as well as Views and SPs.
I am noticing that things are slowing the more people that come to use it on the network - and I'm getting worried.
If I attempt a rebuild - removing linked tables and use vb to both view data and insert records - do you think that this will help prevent the whole thing grinding to a halt sometime in the future?
Thanks again
Paul
-
I have to disagree with the above suggestions. I also have seen that ill-advised post (from a non-MS insider, who I will not name) declaring the "death" of ADPs, but there is no evidence whatsoever that it is based on fact. I have been searching for months for any evidence that MS is abandoning ADPs, and I have come up with nothing.
Also remember that ODBC (the technology that Access MDBs uses for SQL Server) is a deprecated technology, according to several articles on MSDN. ODBC is not the way of the future, although Access will likely use it in the next version or two (my guess).
On the other hand, it is clear that probably 95% of Access users have no need for SQL Server, so MS must focus on MDBs primarily. This has been strongly hinted in several interviews with the next-version Access 12 team. It is also clear from these interviews, that ADPs are not being abandoned, but they probably won't get many new special features in Access 12 either - this is my interpretation of the interviews.
If you have an exclusively SQL Server project for a mission critical app, ADPs are definately the way to go. MDPs and MDEs offer essentially no built-in security, because it is so incredibly easy to hack the Admin password. ADPs (actually ADEs -never distribute an ADP!) force you to rely completely on SQL Server security or your own security scheme, and there is no mdb Admin password to hack.
Also, ADPs offer a significant number of performance enhancing features for SQL Server. In particular, you can finely control how records are sent over the network to your app, and you can let your user control this as well. Features like MaxRecs, input parameters, and Server Filters ensure that you don't bring entire tables over to your forms. You use native T-SQL and OLE-DB, instead of the slower and less-controllable Jet SQL and ODBC. Thus in ADPs, more processing is on the server, instead of the client. You can natively edit Tables, Views, Functions and SPs directly in Access ADPs. etc, etc.
There are bugs and strange behaviors, as in any programming environment, but overall, ADPs are FAR better than MDBs for SQL Server access. If you have a big and important SQL Server project and use an MDB, I bet you will eventually regret it. If you have a small database with just a few users, and it is unlikely to grow much, I would stick with an MDB for convenience.
HTH,
Rich
-
Thanks Rich
I need to weigh as many comments as I can before embarking on what is likely to be a massive change to the mdb I am currently using. The mdb works brilliantly - so you can imagine my reluctance to change it. It is just the speed as user numbers increase that persuades me that I need to do something.
Your comments are really persuasive towards adopting an adp approach.
Thanks again
Paul
-
Hi again Paul,
Just one other note: It is a LOT of work to convert an MDP to an ADP. Your forms may look similar, but a lot of the "guts" and plumbing may need to be re-written. And it also requires learning a new data paradigm that is not at all trivial. Don't let anyone convince you that it will be easy or pain-free. I would plan for a few months (full time), at least, for the complete conversion of a small (50 form) project. And get a copy of Access 2002 Enterprise Developer's Handbook, if you don't already have it. It's basically required reading for the task at hand.
HTH,
Rich
-
You might also want to investigate where the bottleneck is. Is it the network? Is it because Access copies the whole db to the client requesing data (as I've heard it can do that)? If it ain't broken don't fix it... or go with ADEs
. -
We never said it was gonna be easy... we said you might see big benifits
. -
Thanks Rich, Remi
When I have an application which fulfils the requirements of all concerned, I am most certainly attracted to the 'if it ain't broken, don't fix it' school of thought.
However, I have several weeks to get this right - but if I don't by then, I've got big problems. I'm willing to put in the time and effort - if it is going to yield the correct results - speed being top priority.
The current mdb has had bugs fixed and problems with it solved (thanks to Remi for several of them), and I know a new build would present a lot of new ones.
Are linked tables an anathama or can they be tolerated where 60+ users will be using the mdb on a network. Most of the queries are also local.
For example, when a form opens, it uses the sysuser name (from a linked SQL view) to show only the records assigned to that username. So the form's record source is a query which combines a linked table/s with the sysuser view on username in each. Thus the user logged in only sees their own records. Hope that makes sense.
Am I downloading the whole table - thus increasing network traffic, or just the few records for the individual user. Maybe this may be a cause of a bottleneck??
Remi, you obviously don't like adps - do you have a major reason for this standpoint?
Thanks
Paul
-
No I like ADPS (ADE), but they are not the same as mdb. You're gonna have to see how much different they are by yourself.
As for the downloading the whole table, I think the answer is yes. Maybe you could figure this one out by opening the active connection state window and opening a form that loads only one record from a big table. If the connection activity doesn't move much (a few 100 packets at the very most), then I guess you're downloading only the necessary data. But if you see the packets go up by a few 10k, then you are fetching the whole table.
-
"Am I downloading the whole table - thus increasing network traffic, or just the few records for the individual user. Maybe this may be a cause of a bottleneck??"
It all depends on your SQL statement. If you use passthrough, then you get only the records you requested. If you go through Jet, and all your JET SQL is directly convertable to T-SQL statements, you are probably still OK. However, if you use a JET-specific function like TRANSFORM, or IIF, or call a VBE function, then you will likely bring all the records to your client.
(On the other hand, sometimes JET SQL produces results much more easily than T-SQL (e.g. TRANSFORM, IIF, VBA functions), and it's really nice to have it in the mdb when you need it. You can always do it in an SP, it's just harder, IMO.)
60 simultaneous users is asking a lot from JET in an mdb. Many would say that you risk data corruption or client mdb data table corruption at this level of use. I have never seen anyone post real data on mdb or data corruption, especially regarding a SQL Server back end. With a Jet back end, I would say it is likely a problem. With SQL Server backend , does anyone have any hard data on number of users vs corruption vs slowdown??? I'm not sure its a problem.
A final note on your last post: Switching to an ADP does not guarantee faster access time. It mostly depends on your querying efficiency (T-SQL being more efficient in general), server load, etc. However, an ADP will help to keep entire tables zipping over the network by eliminating JET SQL and ODBC, and give you additional tools to manage the records returned from the server.
Before you swithch to ADP, you shoud look at all your SQL statements, and try to convert as many as possible to either pass through, or very simple SQL Server compatable statements. Then re-test your client speed.
HTH,
Rich
-
"For example, when a form opens, it uses the sysuser name (from a linked SQL view) to show only the records assigned to that username. So the form's record source is a query which combines a linked table/s with the sysuser view on username in each. Thus the user logged in only sees their own records. Hope that makes sense."
I'm not entirely clear on this. Are you getting the suser_sname from SQL Server, or passing the local user name form the client? Are you actually joining tables and views? If so, do you have a relationship defined in SQL Server, or are you joining on the fly in your SQL recordsource?
The reason I mention these points is that they may affect whether JET perfoms the joins locally or on SQL Server. You definately don't want a local (client side) join.
To avoid this, consider converting your View into a View or function (UDF) that performs any joins internally, and uses the suser_sname for the current connection to limit records to your user. Thus you call a view or function that requires no parameters or external joins: MyForm.RecordSource = MyViewName. This will prevent any records from being joined on the client.
It may be worth a try...
Rich
-
Just putting my two penny-worth in, go with the adp/ade approach, from my experience it makes overall management a lot easier and more secure.
In terms of speed it is a hell of a lot faster, I had an mdb (not created my me, but left to me to sort out!) that was getting on for 30MB which 11 users were using across a network, it was taking best part of 15 mins to close and would often crash when trying to compact. Now in ade format it starts and stops as one would expect, and I know the actual data is safely contained and backed up properly.
If your forms and reports are based upon views and SPs it is so much easier to tweak them, than having to alter the mdbs, it also means that you can use the full TSQL muscle, without having to write several vb functions, it also makes it a lot easier for mutliple client types in terms of security (i.e. your local users along with web clients). It drives me up the wall when I get asked to fix someone's acces DB and discover that a certain SQL command is not implemented in Access.
The way I have done it in the past is to use the up-sizing wizard, followed by 'tweaking' (admittedly a lot of) of the forms and reports. Ultimatly though it has cut my management time.
-
Thanks Everybody.
Rich - I am joining a table and a view (a SQL Server view containing the username of the person logged in) within an Access query, and basing the form on that. I guess this is a no no. I can change this to do the join in a view on SQL Server, and then use this as the recordsource. Maybe this will help......
Remi - I've been through some stuff with IT Support re using the 'active connection state window' but we cannot find its location - presumably this is available in Server 2000? I think that it would be really useful to monitor packet activity as you suggest. I would be grateful if you could explain a little more about it.
James - many thanks for your contribution. I'm finding it very difficult to get my head around what needs to be done for the best. I am going to try to improve what I have as my mdb is also a large 30mb application and will take a lot of work to change to adp. It works smoothly and well, but I know I have to make some major changes to accommodate a potentially large amount of users. Perhaps, as everybody seems to be saying, adp is the way forward and what I should be aiming towards.
Thanks again Everybody for taking the trouble to help with this.
Paul
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply