Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 5th, 2004, 09:48 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?
thanks
cindy

Reply With Quote
  #2 (permalink)  
Old April 5th, 2004, 10:15 AM
Authorized User
 
Join Date: Jun 2003
Location: , FL, USA.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old April 5th, 2004, 10:47 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old April 6th, 2004, 07:11 AM
Authorized User
 
Join Date: Jun 2003
Location: , FL, USA.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Kenny Alligood
Reply With Quote
  #5 (permalink)  
Old April 6th, 2004, 08:16 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Cindy,
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.





Sal
Reply With Quote
  #6 (permalink)  
Old April 7th, 2004, 09:40 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
cindy


Reply With Quote
  #7 (permalink)  
Old April 7th, 2004, 10:46 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




Sal
Reply With Quote
  #8 (permalink)  
Old April 7th, 2004, 01:18 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
  #9 (permalink)  
Old April 7th, 2004, 10:29 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




Sal
Reply With Quote
  #10 (permalink)  
Old April 8th, 2004, 12:17 AM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Snap Shot of Design View Little Shell VB.NET 2 November 17th, 2006 10:34 AM
Form's Unbound field question ebburks Access 4 June 6th, 2006 07:37 AM
Blank Design View ??? smuger Dreamweaver (all versions) 4 October 11th, 2004 04:40 PM
how to enable the design view yylee Access VBA 2 October 4th, 2004 02:23 PM
Could not open in design view!! texasraven ASP.NET 1.x and 2.0 Application Design 4 March 17th, 2004 03:22 PM



All times are GMT -4. The time now is 10:05 AM.


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