I am with Bob.
If you can upgrade to SQL Server, Do it.
Just remember that if you have any queries in Access, you will have to create them in SQL Server as view or stored procedures. This will give you ultimate performance.
It is not that hard to create view in SQL Server either. It may take some time to re-do all of them, but once they are done, you can link them to your Access front end just the same way you do a table.
You do not have to re-create all your queries at once, but remenber that even if the data is in SQL Server, if you use Access queries, you are still bringing all the data to the client, all 40,000 records, all 4MB, every time.


Sal

Here's a littel low-level Jet engine fun: The ISAMStats and ShowPlan function.

1. ShowPlan

When Jet compiles a query, it uses a cost-based optimizer to generate a query execution plan. You can view this query plan output by activating the ShowPlan function.

Run regedit to open up the registry editor. Add an new key named Debug below:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\

so you get

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Debug
Right click on the Debug key and add a new string value. Name it JETSHOWPLAN (use caps).

Right click on the new string value, and assign it the value ON.

When, ShowPlan is activated, Jet will append the query plan of any query you run to a text file named ShowPlan.out that is created in your MyDocuments folder. You can use this plan to see if Jet is applying the indexes you think its applying, or using table scans instead, etc. DON'T FORGET TO TURN SHOWPLAN OFF, or your text file will grow forever.

Now set up the ISAMStats function to run a query and view its ShowPlan output:

2. ISAMStats

Paste the following module in any new Access db. (the module executes an update query against the Northwind.mdb on your file system. Just change the path).

Option Compare Database

Sub Main()
    Dim dbs As Database, ws As Workspace
    Dim strSQL As String
    Dim lngDiskRead As Long, lngDiskWrite As Long, _
        lngCacheRead As Long, _
        lngCacheReadAheadCache As Long, _
        lngLocksPlaced As Long, _
        lngLocksReleased As Long
    ' Explicitly set the counters to zero.
    lngDiskRead = DBEngine.ISAMStats(0, True)
    lngDiskWrite = DBEngine.ISAMStats(1, True)
    lngCacheRead = DBEngine.ISAMStats(2, True)
    lngCacheReadAheadCache = DBEngine.ISAMStats(3, True)
    lngLocksPlaced = DBEngine.ISAMStats(4, True)
    lngLocksReleased = DBEngine.ISAMStats(5, True)
    Set dbs = OpenDatabase("C:\Temp\Northwind.mdb", False, False)
    Set ws = Workspaces(0)
    strSQL = _
        "UPDATE Customers SET ContactName = ContactName"
    dbs.Execute strSQL, dbFailOnError

    ' The null transaction ensures no more asynchronous
    ' activity that could yield inaccurate statistics.
    ws.BeginTrans
    ws.CommitTrans

    ' The following ISAMStats calls will retrieve the latest
    ' values. The values will accumulate until they
    ' are reset
    lngDiskRead = DBEngine.ISAMStats(0)
    lngDiskWrite = DBEngine.ISAMStats(1)
    lngCacheRead = DBEngine.ISAMStats(2)
    lngCacheReadAheadCache = DBEngine.ISAMStats(3)
    lngLocksPlaced = DBEngine.ISAMStats(4)
    lngLocksReleased = DBEngine.ISAMStats(5)
    Debug.Print "Disk reads " & lngDiskRead
    Debug.Print "Disk writes " & lngDiskWrite
    Debug.Print "Cache reads " & lngCacheRead
    Debug.Print "Cache reads from RA cache " & lngCacheReadAheadCache
    Debug.Print "Locks placed " & lngLocksPlaced
    Debug.Print "Locks released " & lngLocksReleased

End Sub

Run Sub Main from the debug window. This will do two things. First it will output statistics about the queries raw disk reads, writes, locks and caching to the Debug Window. You can find more info about these values on the MSDN site is MS hasn't archived this stuff already (after all Jet has been deprecated). Second, because you just ran a query and ShowPlan is turned on, you can view the query plan by opening the ShowPlan.out file in NotePad or any text editor.

On my system, here is the ISAMStats output:

Disk reads 21
Disk writes 7
Cache reads 435
Cache reads from RA cache 7
Locks placed 12
Locks released 11

And here is the query plan:



---------------------------------------------
DATE: 10/14/03
VER: 4.00.6218

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'Customers'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 90 entries, 1 page, 90 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    PostalCode 90 entries, 1 page, 87 values
      which has 1 column, fixed
    CompanyName 90 entries, 1 page, 90 values
      which has 1 column, fixed
    City 90 entries, 1 page, 70 values
      which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Customers'
    Using index 'PrimaryKey'
02) Update

---------------------------------------------
DATE: 10/14/03
VER: 4.00.6218

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'Customers'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 90 entries, 1 page, 90 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    PostalCode 90 entries, 1 page, 87 values
      which has 1 column, fixed
    CompanyName 90 entries, 1 page, 90 values
      which has 1 column, fixed
    City 90 entries, 1 page, 70 values
      which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Customers'
    Using index 'PrimaryKey'
02) Update

You can see the the appropriate index (primary key) was scanned to perform the query.

Never did find much pragmatic value in this stuff, but it is kinda' interesting to see how the query optimizer works.

FWIW,

Bob

Bob, Sal, & Kenny I really appreciate all the time you are all putting into this question

I am slowly wading through making changes in the DB and looking carefully at what bad elements I have introduced to it during a long learning curve (that is far from over), and how I can make changes to implement segments of your advice, while still balancing the ubiquitous string of changes and that have to be made "right now".

Here is some background for the whopper question coming up.
I am an engineering student working as a developer. I am relatively strong in Java Programmer (not the best background for Access), and I have been reading books left right and center from WROX press; O'Oreily, and basically any book that has the word Access in the title including the Bible series and Programming Access 2002.

They ALL cover roughly 2 sets of material
1. How to use the damn wizards
2. Programming with not enough depth in VBA & Simple SQL queries

The long awaited question...

Where do you get this!!! (referring to bob's last post)
How to I get this knowledge
Is there some secret fraternity of writers holding back all the good and golden info?

What can I and all of the lowly peons like me read that will open the pearly gates of solid DB implementation.

Ahhh, Sorry about the rant but it pains me to keep buying the same books only to find that there is no real substance.

-Roni


Roni Estein
[email protected]
https://www.e-drugsCanada.com

Before you buy a book, make sure that you go to Amazon or any other place that you can get a rating for the book. If it rates high, make sure that it has a lot of ratings 10 or better. Thenread the ratings. Make sure that it is not a rating from someone who just bought the wrong book for themselves. The you go to the bookstore and you review the book your self. Make wure it cover the stuff you want to lean. After that you go to a place like bookpool.com or half.com and pay half price for the book.

My favorite Access books
Access 2000 Bible - Excellent for someone who is a befiner and wants to learn the basics and get fairly good with just access

Mastering Microsoft Access 2000 Development - by Alison Balter - The best book there is on the subject. I know that she has the one for Access 2002 and it has to be good. She is the best.

and finally if you dare to move to SQL Server
Microsoft® Access Projects with Microsoft SQL Server™
By Ralf Albrecht, Natascha Nicol
ISBN 0-7356-1002-9

It covers a lot of good stuff.
Fof you I would say, get the one By Alison Balter ISBN 0672314843 for 2000 version, she has two versions for 2002 so review them in the book store.
Read a lot about table design, relationships and indexes.

As far as Bob, I do not know where he gets this stuff..

Where do you get this Bob?





Sal

Hi everyone
I'm new here and desperately needing some help.
I'm also having performance problems with a split database, but it's not just in the network environment - even when I have both FE and BE on my computer, the forms containing code take VERY LONG to open or design view. When I'm working in the VBE editor by computer resources get very low 20% or so and I have 256 mb of ram.

I am very new to programming - I used to be the macro queen but got the Smith/Sussman Beginning Access 2000 vba book and began to make the change because everyone said the vba runs faster than macros - YIPES!

I've debugged, compiled, compacted, with no change. I had the database split before with my macros running the automation and there was no "visible" delay in opening forms. Now I can count to 8 or even 12 sometimes! Same functionality, just convereted to vba.

Can anyone offer me any advise?
Thanks
Lisa

Read this complete tread and see if it helps at all. (I am sure you already did that).
After that, how large in megabites is your database? How many records?



Sal

LOL :)

OK. I'm putting my life on the line here, but...Yes...there is a secret fraternity of Access developer's. They have an initiation ritual...my palms a sweating just thinking about it...I could tell you who they are...but they'd find me...

OK, I'll come clean.

A good place to start is to read everything ever written by Ken Getz and his cohorts at MCW Technoligies:

http://www.mcwtech.com/Books.htm

Start with the most recent edition of the Developer Handbooks (Desktop and Enterprise editions) for Access and VBA. The Mary Chipman/Andy Baron book is the book for Access/SQL Server development, in my opinion. Load the SQL Server Developer edition ($50.00) on your local system and learn T-SQL. (I don't think Jet will be with us much longer, just a hunch).

Subscribe to a decent industry mag to stay abreast of new developments, something like:

http://accessvbsqladvisor.com/

or Visual Studio Magazine or MSDN Magazine or Smart Access or Inside Access.

Check out all the other online forums, especially:

http://databaseadvisors.com

Get a quarterly subscription to the MSDN library.

Downlaod every code sample you can find on the internet.

Get Visual Studio and learn all about object-oriented programming languages. LEARN .NET!

The code from the previous post came from a folder on my E: drive labelled "Backup 01022002". I'm sure I found it on a newsgroup somewhere, probably databaseadvisors. The second edition of the "Microsoft Jet Database Engine Programmer's Guide" touches on the subject, but that particular book hasn't been updated since 1997 and there won't be a new edition.

HTH,

Bob

Hi again.
Yikes.
My database is about 7 mb - it is secured so that adds some size. It was about 3 1/2 mb with the macros.
The records are not that numerous - it's just an order entry system for a small business. The data file is about 3 1/2 mb.

the majority of the automation in the database is simple enough stuff I can do with macros and code that runs a few action queries - I think I'll go back to that - I am not interested in reading EVERYTHING I can get my hands on about vba etc. I'm not left brained enough to be a serious programmer. I love Access - but not writing lines and lines of code!

Thanks anyway
Lisa

You may just have PC issues. Try running it from another pc and see what happens.
Your resources should not degrade by doing this.
Read the rest of this tread and tell us if you havedone the suggestions and then we can help.

Do you have referential integrity, indexes and everything else that we posted today?


Sal

Sorry Lisa. I was actually responding to Roni's post a bit
further back. Did'nt mean to startle you.