Theoretical Limits MS Access 2003

[Lightwave]

Dear All

Wondering if anyone could advise

I have a 2003 database that has been being developed for 10 years now. It has some 200 forms and maybe 500 to 600 queries. About 6 modules and 300 or so reports.

I notice that after designing of forms it is taking longer to save the changes - can take 5 minutes. The compiled application still performs fine its just when making design changes.

I have run a decompile on it and it is regularly compacted.

Can anyone advise on what might be going on?
I'm wondering whether I am approaching any theoretical limits


M

 

[CJ_London]

only other thing I can suggest is to create a new blank db and copy all the objects across. Also worth turning off the name autocorrect feature

 

[Lightwave]

Thanks I think it is ok I think it might be something to do with our network

I am designing by remoting to access as a RDP client session and which may not be helping. The front end is about 77MB

 

[Trevor G]

Have you split the database? If you work on a local copy and it is faster when designing/modifying then all you should do is design/modify on the local copy and then place/replace the local copy forms etc to the main database.

 

[AOB]

Is it a split database with linked tables?

If you have linked tables, I have found that performance drops considerably the more levels you have to navigate in the directory structure on the network?

So if a linked table is located at something like :

\\server

\folder

\subfolder1

\subfolder2

\subfolder3

\subfolder4

\subfolder5

\YourBackEnd.mdb
​

Try mapping the path to a drive letter; e.g. make Z: map to :

\\server

\folder

\subfolder1

\subfolder2

\subfolder3

\subfolder4

\subfolder5\
​

and then change the path of your linked table to the mapped drive :

Z:\YourBackEnd.mdb

I have a multi-user FE/BE and, because everybody can potentially map their drives differently, I have to use the UNC path to the backend for each of the linked tables in the frontend. So I use a function, fired from AutoExec, to automatically relink all the tables based on the individual users local drive mapping, to keep things zippy.

Whenever I make updates to the master copy of the FE, if I forget to change the paths from UNC to mapped drives, I also find editing forms and saving design changes can take minutes. Even clicking on a control to edit its properties can take 1-2 minutes. When I manually relink the tables to the mapped paths, it becomes quick again.

Just a thought - when I read your post, that was the first thing that popped into my head - it may not be the root cause...

 

[Lightwave]

Speed I think was a combination of network and the complexity of the database - It improved after I posted but shortly after I got a compile limit error.

As follows.

Microsoft Access was Unable to Create an MDE Database

This error is usually associated with compiling a large database into an MDE file.  Due to the method used to compile the database, a considerable number of TableID references are created for each table.  The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time.  Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE.  However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.

So I think complexity of the database hunch was linked.
It looks like ten years of coding on the database and I have finally hit the limit Sure I can figure something to get round it.

Does anyone know if this limit was upped in 07/10 or 13?

 

[CJ_London]

Does anyone know if this limit was upped in 07/10 or 13?

Don't think so.

If you have a backup from before the last changes, can you go back to that and move forward incrementally, it may be that you have an error somewhere.

The key seems to be regarding hasmodule - you may be able to clean your db where forms have redundant code.

More work, but another option is to move some forms formcode to their own module so you can set the hasmodule to false, then replace [Event Procedure] with a call to the module e.g. =mybutton_click()

 

[Lightwave]

Thanks CJ / All - I found an old thread that indicated that you can get this error when you have problems in your code.

The suggestion was to go to my project - go to a module and try and compile the code. If there was an error I should fix it and then try to make an MDE file.

I discovered I had defined the same variable twice within a procedure. This mean't that the VB would not compile. Fixing the variables allowed the whole project to be made into an mde.

User error apologies

 

[CJ_London]

always worth using Option Explicit and compiling the code before creating an mde....

 

[BlueIshDan]

I've always thought of creating a query management form.
One that saves all your queries in a single table, that way instead of loading and saving 300 queries you narrow it down to one table.

The form would be able to load an instance of a design view form and break it down into a new or update record upon close. I don't know if this would be a reasonable idea or not.

Thoughts?

 

[BlueIshDan]

Something like this sub could send your queries into a table, once backed up and removed you could use the function as a quick mod method to your query calls. Might take awhile but saves lost time in the long run... maybe.

Private Sub PlaceQueriesInTable()

    Dim qry As Variant
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblQueryCompression")
    
    With rs
        
        For Each qry In CurrentDb.QueryDefs
            
            ' Ignore System & Form Quiries by filtering out tables that begin with "~"
            ' Add what ever other instances of system filter stuff here.
            If Left(qry.Name, 1) <> "~" Then
                
                .AddNew
                    !Name = qry.Name
                    !SQL = qry.SQL
                .Update
                
            End If
            
        Next
        
        .Close
    
    End With
    
    Set rs = Nothing
    Set qry = Nothing
    
End Sub

Public Function GetQuerySQL(ByVal qryName As String) As String

    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset( _
        "SELECT sql " & _
        "FROM tblQueryCompression " & _
        "WHERE name = """ & qryName & """")
    
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        GetQuerySQL = rs!SQL
    Else: MsgBox "Error retrieving sql for [" & qryName & "]", vbCritical, "Error"
    End If
    
    rs.Close: Set rs = Nothing
    
End Function

 

[CJ_London]

I've always thought of creating a query management form.
One that saves all your queries in a single table, that way instead of loading and saving 300 queries you narrow it down to one table.

I've done that before, but put the table in another, password protected db and open in VBA using a recordset. Reason is to prevent users messing with them.

Other benefits include being able to add a detailed description of what the query is supposed to do, any business rules it is required to meet, where it can/can't be used, etc.

I've also done things like combine queries together (separated with a semi colon) so my vba routine can split it and run each query in turn (e.g. make table query, append query etc.)

 

[BlueIshDan]

Cool! Thought I was just throwing a wild pitch out there, glad someone with some credit has also come across it! haha

 

[BlueIshDan]

I wonder if Binary Access Read / Write would be faster than linking to tables and using recordsets?

Though in Access I find it is harder to load things into memory, and making them last a whole session.

 

[CJ_London]

I wonder if Binary Access Read / Write would be faster than linking to tables and using recordsets?

Don't know without trying, but if the tables are indexed properly it has a negligible effect on opening a form or report.

Though in Access I find it is harder to load things into memory, and making them last a whole session.

I just keep them for the life of the form or report

 

[BlueIshDan]

If there weren't so many queries and what not, you could store them in TempVars on load but that doesnt seem to be an option here! lol

 

[The_Doc_Man]

Binary Access Read/Write makes it impossible to build a query on a subset of the record you are Reading/Writing, but if you ALWAYS do, say, unfiltered Recordset operations on the table (that you were planning to switch to binary operations) in effect always reading the entire table linearly anyway, then MAYBE a binary operation is very slightly faster because you don't have to go through the index at all.

For BlueIshDan, this is partly a rhetorical question: Why would loading something in memory NOT last the whole session unless you are overwriting it or performing a RESET or (for items accessed via SET xxx=object) doing a SET xxx = Nothing? Are you implying that something crashes or is corrupted?

 

[BlueIshDan]

I'm saying that when using public variables, I've never had success with them carrying out the entire duration of the use of the application. Though that was way back when I first made the transition from working in .NET to working with Access.

My idea was to place your queries either into a table or into a custom file type. The two were not in the same idea. The table idea was to keep it all within the bounds of access, to keep it easier and quicker. The file based idea was aiming more towards managing the loading time of the project. If Access has to create overhead for every query when loading, my idea was to manage that memory with the least amount of overhead by getting rid of the classes and breaking it down to whichever data type is the most effective. Cost and usability affecting process time wise.

Again, all just ideas. I find it fun to think of out of the box schemas.