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
  #11 (permalink)  
Old April 8th, 2004, 06:33 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 drive a 3 cylinder full size van :) 60 miles a gallon.

Who runs SQL Server on Win 95 anyway.

Back to the subject

Cindy, Do split the database, but read up on it first and find out about the problems it can cause. If you do not have it secured with Access security, the performance will not degrade too much. Remember that when a database is split, for the aplication to do it's function, now it has to reference two files in the network and bring both files to the client. If you are using Access security, now you are using one more file to reference the security.

Most people do not need to secure a database with access anyway.

Good luck.



Sal
Reply With Quote
  #12 (permalink)  
Old April 8th, 2004, 08:14 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you to all the replies. I have learned a lot!
Now I am trying to modify the existing databases. The former developer created three databases for there persons to do data entry. The three databases transfer data each other when the databases are closed.
Do you think I should create one database combining all three together and then split the database? The database is not very large.
If I should split the database, I need store the backend database in a shared drive and store the front end database in local drive, right?
thanks
cindy


Reply With Quote
  #13 (permalink)  
Old April 11th, 2004, 07:50 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

The size of your database is not as much an issue as it is the number of users that will be hitting your database at any one time. If this users are opening two or even three databases at the same time to transfer data from one another, when this is data that is related to each other, then yes, put all of the objects into one database. Just remember that you must make a back up of all databases and make sure that no-one has access to make any changes to the databases while you will be making the changes. Bring the databases completly off the network while you get this done.

Something to keep in mind is that access will corrupt itself when you import object(forms,reports) that have any code modules whithin them. To safely import all items into one database, first remote the code module from the form/report and set the form/report's HasModule property to no. save the form/report and only then can you safely import it into another database.

Once this has been done you can put the code back into the form/report.

make sure that you compact and repair the database after you have completed this importing process, use the decompile switch as well to decompile your database.



 
Quote:
quote:If I should split the database, I need store the backend database in a shared drive and store the front end database in local drive, right?


yes.
you can keep the master front end, but the back end must be in a shared folder. You can then copy the changes to a network drive and
if you are comfortable with vba, you can create a filecopy program to copy any new changes of your database to each user'a machine as needed. But this is the next step.

Hope I did not confuse you.




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

When you have multiple users, splitting between application and data files is generally advantageous. Because of the way in which Access handles linked tables, there is no need to merge data files into a single file though doing so permits you to define referential integrity rules in the data file. Opening connections to more files means that there are more lock files to maintain and that can also impact performance. Occasionally you may have a very few users who need access to the data in one file whereas everyone needs access to one or more of the others. This can give you advantages in backing up and maintaining the data files if they are kept separate. Some people run into size limitations (1 gigabyte file size in Access 97, 2 gigabyte in Access 2000 and more recent) such that they have no choice but to split a data file. This situation does not appear to arise in your circumstances.

If you have 20 users and 10 are commonly working on data in one backend and 10 in the other and 3 of those in both, you will likely see fewer lock/concurrency and connection issues should you keep the data files separate. This also depends in large part on how the data is used by those users. If most users are primarily adding new records, fewer problems are likely to arise than if they commonly edit current data.

I just reviewed my Sybex 'Access 2000 Developer's Handbook - Volume 2: Enterprise Edition' by Litwin, Getz & Gilbert and it confirms my assertion about query traffic being limited to index pages followed by actual records requested in an mdb file server environment. A brief mention is made at p 97, chapter 3: Comparing Client-Server and File-Server Systems. While Sal may say: "Access will bring the complete table to your local machine, that is a fact", I have yet to see any authorative documentation that confirms this 'fact' and my own testing confirms the comments made in the Developer's Handbook: traffic is limited to indexes of joins plus the data actually selected.

While the indexes may be sizeable, joining/filtering/selecting on numeric keys results in the maximum efficiency available from a file server environment. Breaking tables into smaller logical blocks of records will also significantly reduce index traffic since index size varies with the number of records. For example, it may be easier to group Contractor,Architect, Engineer and Supplier records into a single company table but they may be split into separate tables and unioned together for certain purposes depending on the manner in which the data must be used.

I agree with Sal on not running SQL Server on a Win 95 box, but my current Access 2000 project is deployed on a system that uses a Win 95 file server with a p II 233 processor, 64 megabtyes of RAM and a 1.6 Gig hard drive hit by various Win 98, ME and XP computers. The fact that it can run well on an older low power machine is an indication that if you move it to a system with sufficient resources to run SQL Server it will run even faster.

While I acknowledge that the most common bottleneck in a File server environment is LAN traffic volume, that only becomes the limiting factor after the data reaches a certain size and that size depends on many many other variables, some of which a developer has a certain degree of control over, and is often much larger than most people would believe.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
  #15 (permalink)  
Old April 12th, 2004, 09:57 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 will quote myself again
"Access will bring the complete table to your local computer"

Try hitting a table that has 200,000 records across a network, a real network. Query the database with any combination of access and SQL Server and you will see that when you use a pass through query, access will bring the one record in the where clause faster.

It Will, and yes, a SQL Server Clustered index on a large table will run faster than any index in Jet. Have you ever worked with SQL Servers or very large databases?

Fact, Microsoft will not come out and tell you "our jet product is slow". They will only highlight the pros not the cons.If I owned MS, I would do the same.

If you bypass Jet, and use ADO or DAO, you may get a faster result than using Jet.

Real networks have other issues at hand. More than just a few 98, 95 and ME unsecured computers, because remember, there is no security outside of NTFS, and do those operating systems support NTFS. This is a topic for another forum.

Terminal services are a service that runs on the server. You could be in Hawaii and run an Access application, that is split, and, if you are lucky, you can dial up with a 14k modem and this will look as if your app rocks. It does not. All of the processing is happening on the powerful server.

I have had to create apps for people who had to access a database via 56k modem. It did run faster via terminal server, but if they tried to run it via VPN, it would hang. Because the tables were being brough to the local laptop in Oklahoma and the server was in San Diego.

I just went to a library of books on line and I searched many books, in 4 of them I found the same thing that I just said, then I stopped looking.





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

Access in Terminal Services is a Client (thin client) Server (terminal server) deployment. All the processing takes place on the server... just like SQL Server. And you can run Terminal Services through VPN and when you do, it is very fast. Also, hosting Access on an ASP host is also a client (browser) server (ASP Server) environment. This is a low traffic solution as well and as you noted, all the processing takes place on the powerful server; only requested data is sent to the client.

Next, Jet = DAO. DataAccessObjects are Jet objects.

Quoting yourself and unnamed references does not make authoritative proof. You may wish to follow your own advice and do some research. It is trivial to test whether a full table is transferred and there is no need to cite books. I have personally done the measurement of this traffic and cited one of the most highly respected books published dealing with Access as confirmation of my test results. You ought to list your references like I did so that they can be contacted to correct their misinformation. I would be happy to enlighten them. I'll throw out another example:

http://accessvbsql.advisor.com/doc/05139

by the editor in chief of Access-VB-SQL Advisor magazine. A quote from the URL:

"However, it's a myth that every file server action requires that all the data be moved to the client. For example, a sophisticated index system might call up only the needed blocks of data."

In an article by Alison Balter from the same magazine, different issue (20 April 2000):

"...when data is stored in an Access database, all 100,000 rows may be returned to the workstation depending on the design of the table."

You'll note the words 'may' and 'depending on the design'.

I have not said that SQL Server doesn't have advantages in certain circumstances nor have I cited any Microsoft propaganda as implied. Just that Access mdb data can be deployed as Client Server and when deployed as a File Server, the volume of traffic over a LAN when selecting a single record with a where clause on an indexed field if far less than the full table and that I have measured this traffic and my testing confirms a very authoritative published reference work. A SQL Server clustered index on 200 records is not going to be any faster than a plain jane Autonumber indexed Access field. Filling a search combo with all the names from a couple thousand record table with a name from each record using a SQL Server based backend is going to be exactly the same traffic as with an Access backend.

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 04:45 AM.


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