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 VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 March 7th, 2005, 10:01 AM
Authorized User
 
Join Date: May 2004
Location: Bucharest, , Romania.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default how can I speed up the opening of forms?

Hi there, Access users!
Please help me improve the performance of my database. Here are the problems:

I have a table "clients" related one-to-many to table "requests" which is related one-to-many to table "models".

"Clients" has 11000 records, "Requests" - 13000 records and "models" - 14000 records.

1st problem: I have a form used to introduce new clients and requests (Data Entry), which is based on a query on this 2 tables, and a subform for models, linked to the corresponding table.
Opening this form takes about 30 seconds because it runs the entire query to open the form.
If I pass the SQL code to the "Record Source" of the form, it opens instantly, but I get errors of locking and up-dating the data, when more users are entering data in the same time.
What can you recommend me?

2: A second form is used for editing records. It is related to table "clients" with a subform - "requests" and a sub-subform - "models". I open this form passing the name of the client (from another form) and it takes 1 minute to open it (shows all requests for one client and the models for the selected request).
How can I speed up the process?

Good Luck to all!
Mihai

Reply With Quote
  #2 (permalink)  
Old March 9th, 2005, 04:42 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Buy a faster computer... :)

If you can find a way to restrict the amount of data being accessed in the requests, your performance will improve.

(Access is not really a good choice for large databases...)
Reply With Quote
  #3 (permalink)  
Old March 10th, 2005, 10:00 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   I had this very same problem and here is how I got around it (with a lot of help from Access Advisor Magazine - the best).

   You need to have a main form with a button to open this huge client form. Put a combo box on that main form to allow the user to select the Client they want (populate it with a look up to your clients table.)

     Then on the button to open the form from the main form, put this code on the On Click event:

'=====
    Dim stDocName As String
    Dim stClient As String
    Dim stLinkCriteria As String

    stClient = Me.cboClient

    stLinkCriteria = "[ClientID] = " & stClient
    stDocName = "frmHugeClientForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'...
'=====

   This will open your huge form with just one record. That will be very fast.

   Then on the huge form, if you want your user to navigate to another record, put a look up combo box again looking up the same client records (in the table, not on the form).

   Then on that combo box's After Update event, put this code:

'=====
Me.RecordSource = "SELECT * from dbo_tblUserName WHERE UserID = " & Str(cboUserName)
'=====

   This will pass the client name to the recordsource of the form and open it again with just one record.

   This will ber very fast, and will prevent locking issues etc since the user will only ever have one record open from that table at a time. This will populate your subform(s) of course.

   I am not sure how to resolve your second issue. DO you have more information?

HTH





mmcdonal
Reply With Quote
  #4 (permalink)  
Old March 10th, 2005, 10:03 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, the second code snippet was word for word from my large Access database - that performs quite well thank you.

   It should be this for generic reference:

'=====
Me.RecordSource = "SELECT * from tblClient WHERE ClientID = " & Str(cboClientLookUp)
'=====

Thanks



mmcdonal
Reply With Quote
  #5 (permalink)  
Old March 10th, 2005, 10:05 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry again, but given the number of transactions that you are doing here, it might be better to move to SQL as BrianWren suggests.



mmcdonal
Reply With Quote
  #6 (permalink)  
Old March 11th, 2005, 03:12 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Open all your forms when you open your database. Instead of opening them...switch the visibilities on and off.

Hope this helps,
Modest
Reply With Quote
  #7 (permalink)  
Old March 14th, 2005, 06:05 AM
Authorized User
 
Join Date: May 2004
Location: Bucharest, , Romania.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you all for the input!
The way the forms work is quite the same as Michael suggested. Opening the form with just one client is instant. But it takes a lot of time to find the data for the subforms.
So, this week I've been studying SQL server and starting from June we'll migrate the data to SQL Server. And of course we'll buy a faster server, as Brian Suggested.
Thanks again!

Mihai

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
Opening 1 form from 2 forms rocknrisk Access 5 July 29th, 2005 06:05 PM
Help with opening and closing forms Dwizz VB.NET 2002/2003 Basics 4 April 21st, 2005 03:19 AM
opening and closing forms mp VB.NET 2002/2003 Basics 6 April 2nd, 2005 07:37 PM
Opening forms from other forms Paulsh Access VBA 1 September 30th, 2004 06:54 PM
Closing and Opening Forms Louisa VB.NET 2002/2003 Basics 1 February 5th, 2004 02:40 PM



All times are GMT -4. The time now is 01:11 PM.


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