p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Question about form's design view (http://p2p.wrox.com/showthread.php?t=11423)

reindeerw April 5th, 2004 09:48 AM

Question about form's design view
When I am in the design view of the form,can anyone else use this form to entry data?

Kenny Alligood April 5th, 2004 10:15 AM

That depends...

Does this DB employ the Front End - Back End setup? If so it depends on which portion you have the form in design view. If not (and you work with just a Front End) then you will need to have Exclusive Access to the DB to keep others from making changes.

Kenny Alligood

reindeerw April 5th, 2004 10:47 AM

Thanks for the reply.
What is Exclusive Access? I am new in Access.

Kenny Alligood April 6th, 2004 07:11 AM

Look in Microsoft Help under the topic "Open a Microsoft Access database" and that will explain the whole process.

Kenny Alligood

sal April 6th, 2004 08:16 PM

If you have not split your database, chances are, you will not be able to save any changbes to your form if another user has the database open as well. When you open an item in Access in design view, you are automatically opening the database exclusively, with this in mind, try to refrain from opening your objects in design mode while your database is available to other users within your network, even if you do not think that someone else will open it while you make the changes.

If you still must make a change to this form, make sure to close the database after you have made your changes or else you will not be letting other users into the database until you close it.

Try to split your database into front end/backend, but do a lot of reading about the implications of splitting a database. Performance will degrade with a split database, specially if you have it secured with Access security.

Do a search in this forum for "split" under the Access topics and read up on the problems that it can cause. Most times, you have no choice, but to split anyway.


reindeerw April 7th, 2004 09:40 AM

Thank you sal, if I create a front end and backend database, that means all the tables in one database, and all the forms... in another database, and if I want to change the design for the form1 for example, can another person still entry the data in form1?
thank you very much!

sal April 7th, 2004 10:46 AM

What you do is keep a master of the front end database somewhere else (not the shared drive), then make all changes to this master and replace the front end file as needed. The best thing to do is to put the front-end file on the user’s pc. Just make sure to lock that front end and throw away the key when you distribute it By the way, I did not mean by using Access security).

The best thing to do as far as database back end is to use MSDE or SQL Server.

and to answer the question, no, you would still be locking the database.


jurgenw April 7th, 2004 01:18 PM

I disagree with Sal recommending the use of MSDE or SQL Server to people who specifically say 'I am new in Access'. Jet/DAO is more efficient than you imply and is well suited to many environments. Most company's using Access lack the resources to properly maintain SQL Server. I have often seen Jet/DAO outperform SQL Server and would not be making recommendations to switch to a different backend without knowing the number of users and the number of records in the larger tables.

I have heard it said that Jet pulls entire tables across the LAN and that is not so in most cases. I have an application deployed with 20 concurrent users on a LAN with tables of up to 20,000 records and it is many times faster than a similar application on SQL Server pulling data over a WAN. We used replication to synchonize over the WAN and most users preferred the Access BE performance. The environment was switched to Terminal Services and the WAN performance of the Access application remained superior to the SQL Server version over the same connection. Access on a web server is also a true client/server environment.

Many performance issues in Jet/DAO can be addressed with proper data design and the appropriate use of indexes. If the ultimate target is a very large database with many users, it would be wise to consider ADO at the outset but I would guess that there are 20 Access databases that run faster and more efficiently with Jet than to any one that benefits from switching to MSDE or Sql Server as a back end.

Jürgen Welz
Edmonton AB Canada

sal April 7th, 2004 10:29 PM

I guess we are allowed to form our own opinion. All I will say is that I have found many people who say this same thing. The answer as always is "it depends". Access will bring the complete table to your local machine, that is a fact. That means that for every time that you request a record for a table, all of the data comes on the cable and kills your network. If you do a query and join multiple queries, yes it will bring all tables to the client. Multiple clients, multiple requests, multiple packets of data that will kill yuor network and upset your users.

Access has it's limitations. This is only one of many. If you are not very familiar with Access, buy a good book and read on it. I recommend Alison Balter's books in access. She is the best. She will cover the basics of SQL Server as well.
Now, I have worked with Access, SQL Server, Oracle, Mainframes, etc. and I still love Access the most. I use it every day, but I use it for it's power on RAD (Rapid Application Design). There is no better tool for this. But remember the limitations.

Anyway, I wonder why companies that do have the cash, invest in SQL Server? why would they do that if this (sql server)were an inferior product?

This is what I have seen: Users, who call themselves developers or programmers, create a database in access and once it gets huge, they move the tables to SQL Server or Oracle. Oh, they forget one piece, the queries. They end up using the same queries via linked tables. Guess what, this will not give you any performance gain over SQL Server view and stored procedures "because it is not being used". Now you are using Jet on top of SQL Server's engine. Imagine the extra weigth if you carried two engines on your car.

Programmers do remember to connect directly to the database backend on the ADO/DAO/ADO.NET connection string. This would cause them to erroneously believe that ADO or DAO is faster.

If you need to learn something, learn the better way of doing things. Use the newer technology and do some research on your own.


jurgenw April 8th, 2004 12:17 AM

Just to rephrase a bit. If you need to run to the local grocery store to pick up a few things for dinner, hop in your car, bike or jog over. If you need to move a few million cubic yards of ore, the car isn't going to cut it. Building a railway, buying an engine and cars and then hiring the staff to run the works and maintain the track can't be justified for picking up groceries from the corner store, but it sure beats using automobiles for moving ore.

DAO remains the fastest way to get data from an Access database. You need only time it to see. It also has the most complete set of methods and properties for working with Access objects.

While I haven't read Balter's book, I have read several others so I don't know what she says about entire tables being pulled over a LAN. I suggest that if you have dial up networking access to a server, you run a few queries pulling entire tables and single records from the same table limited by a where clause on an indexed field. Check the bytes transferred before and after opening the queries. I am sure you can try some LAN monitoring utilities and I believe that even the Net Use command has parameters to check traffic (though my testing has been limited to dial up networking monitoring). Also ask yourself why indexes improve performance with Access databases. I've posted a few comments in the past month about traffic and indexes and will not repeat them here.

I'll repeat my comment about terminal services. All the processing takes place on the server, and the drive can be on the same bus connected to the RAM, just like SQL Server, but without the overhead of SQL Server and the same applies to Access on an ASP server. I've successfully run Access 97 on a 386 DX with 16 megabytes of RAM and Windows 95. Try running SQL Server on that. On a decent machine, Access will already be delivering data to the user while SQL Server is still loading (takes a while for those boilers to build up steam you know).

And newer isn't necessarily better. Access 97 remains faster than 2000, XP and 2003 while using fewer resources. If you're not a government, use the technology that fits the need. I've done my own research, counted bytes transferred, tested file sizes when indexes are added and removed and timed things with the queryperformancetimer (doesn't measure queries as implied by the name) or timeGetTime API timers. I read books as a theoretical guide but draw my own conclusions from real environments.

Jürgen Welz
Edmonton AB Canada

All times are GMT -4. The time now is 12:57 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.