MS Access multi user slow

[nabbster]

Hi Guys,

I am running a Access 2003 db from a shared folder(cant move it closer to root) multi user (max 15 users) all queries and such are on an Excel front end in VBA(nothing is accessed by the user from MS access).

Current records 45,000 - expected to max out @ 500,000
Current fields(Columns) - 20

I have run some bench tests on the most intense query with no users working and it runs 20 seconds this climbs to approx 500 - 600 seconds when users are logged in. I have read some info about keeping the connection open for each user but I don't understand this.

Declared publicly

'//================================== db connection strings
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset

In a Module

Public Sub cCon(shtname As String)
    
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & Sheet1.TextBox20.Text & "\CD Invoic.mdb;Jet OLEDB:Database Password=*******;"
    Set rs = New ADODB.Recordset
    cn.Open

End Sub

Query - Cut down version of the Query this basically reports what each user has done in each hour of the day.

Dim uRange As Integer
Dim aRange As Integer
Dim intHr As Integer
Dim MinUpDate As Field
Dim Count As Long
Dim BenchMark As Double

BenchMark = Timer

  Application.ScreenUpdating = False
    clearit1 (shtname)
    cCon (shtname)
    rs.Open "SELECT UserName FROM User_Lookup", cn, adOpenStatic, adLockReadOnly '//Get usernames for Capture list
        Sheet2.Range("H3").CopyFromRecordset rs
        uRange = rs.RecordCount
            rs.Close
        Set rs = Nothing
        
    rs.Open "SELECT UserName FROM User_Lookup", cn, adOpenStatic, adLockReadOnly '//Get usernames for paid list
        Sheet2.Range("H20").CopyFromRecordset rs
            rs.Close
        Set rs = Nothing
       
            
        For aRange = 0 To uRange - 1 '// PH captures ************ from 06:00 to 19:00 ****************
            rs.Open "SELECT ID FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 06:00:00") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 10:00:00") & "# AND TB_PB='PB'", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then

        rs.MoveFirst
        rs.MoveLast
                Sheet2.Range("I" & aRange + 3).Value = rs.RecordCount
        Else
                Sheet2.Range("I" & aRange + 3).Value = 0

        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("I" & "2").Value = Format("10:00:00")

            rs.Open "SELECT ID FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 10:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 11:00:00") & "# AND TB_PB='PB", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then

        rs.MoveFirst
        rs.MoveLast
                Sheet2.Range("J" & aRange + 3).Value = rs.RecordCount
        Else
                Sheet2.Range("J" & aRange + 3).Value = 0

        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("J" & "2").Value = Format("11:00:00")

                   rs.Open "SELECT ID FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 11:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 12:00:00") & "# AND TB_PB='PB'", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then

        rs.MoveFirst
        rs.MoveLast
                Sheet2.Range("K" & aRange + 3).Value = rs.RecordCount
        Else
                Sheet2.Range("K" & aRange + 3).Value = 0

        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("K" & "2").Value = Format("12:00:00")

            rs.Open "SELECT ID FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 12:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:00") & "# AND TB_PB='PB", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then

        rs.MoveFirst
        rs.MoveLast
                Sheet2.Range("L" & aRange + 3).Value = rs.RecordCount
        Else
                Sheet2.Range("L" & aRange + 3).Value = 0

        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("L" & "2").Value = Format("13:00:00")

            rs.Open "SELECT ID FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 14:00:00") & "# AND TB_PB='PB", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then

        rs.MoveFirst
        rs.MoveLast
                Sheet2.Range("M" & aRange + 3).Value = rs.RecordCount
        Else
                Sheet2.Range("M" & aRange + 3).Value = 0

        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("M" & "2").Value = Format("14:00:00")

    MsgBox Round(Timer - BenchMark, 2)
    Application.ScreenUpdating = True
    rscnClean (shtname)

I compared this but didn't see any significant improvement.

            rs.Open "SELECT Count (*) As tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 14:00:00") & "# AND TB_PB='PB", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("M" & aRange + 3).Value = rs("tbC").Value
         End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("M" & "2").Value = Format("14:00:00")

Any help would be gladly appreciated.

[si_the_geek]

Access .. multi user (max 15 users)

That is a bad idea - it would be much better to use a server based system (like the free SQL Server Express) instead.

That is likely to increase speed, and definitely improve reliability.

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset

That slows down everything you do with those variables... see the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum)

    rs.Open ... , cn, adOpenStatic, adLockReadOnly  [, nothing] 

That slows it down (more in multi-user situations), but depending on the circumstances perhaps negligibly... For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of this forum)

        rs.MoveFirst

That is wasteful - there is never a need for that just after a .Open (not until after at least another .Move or deleting/adding/editing records)

        rs.MoveLast
                Sheet2.Range("I" & aRange + 3).Value = rs.RecordCount

That is a very slow method - it would be much faster to change the SQL statement to a SELECT Count() , and just read the single value that is returned.


I haven't looked at all of your code (or any of the SQL statements), but this should be enough to make several improvements.

[nabbster]

Hi Si,

Thank you very much for your response I have a few questions if i may,

I have this:

rs.Open ... , cn, adOpenStatic, adLockReadOnly

You specified this the [,nothing] makes a difference?

rs.Open ... , cn, adOpenStatic, adLockReadOnly  [, nothing]

Could you check this and tell me if this is the correct way to use this Count(*) Sql as i can't find any examples?

         rs.Open "SELECT Count (*) As tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 14:00:00") & "# AND TB_PB='PB", cn, adOpenStatic, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("M" & aRange + 3).Value = rs("tbC").Value
         End If
        rs.Close
        Set rs = Nothing
        Sheet2.Range("M" & "2").Value = Format("14:00:00")

I am busy making the other changes and will post back any result. Once again thanks

[si_the_geek]

rs.Open ... , cn, adOpenStatic, adLockReadOnly

You specified this the [,nothing] makes a difference?

The adOpenStatic and the part you are missing - the FAQ article explains in detail.

Could you check this and tell me if this is the correct way to use this Count(*) Sql as i can't find any examples?

That looks right.

[nabbster]

Ok forgive me if I must be missing some thing but I allready have the openStatic in my code just the [,nothing] bit I am missing according to all the examples I have seen

Can you expand?

[nabbster]

Opppsss the penny has just dropped I want to add openForwardOnly and remove OpenStatic

[nabbster]

Ok I made the changes I am sure they made some difference but In view of the 600 odd seconds the Query is taking they are very slight.

I thought I would compact and repair the db and saw a massive difference I thought it was the compact that did this but infact what I found is that because I was still on the front of the db from MS Access and when I closed MS Access this the query time increase massivly. Any pointers? Is this to do with the lock file that is refered to elsewhere? as I noticed this was open the whole time I was in the front end of Access.

[GaryMazzone]

Any time you access Access (sorry for that) a lock file (ldb) is created.... it is one of the reasons that any user of the database needs create,delete,modify rights on any folder that the database resides in. When the first user connects the file is created... when the last user exits the file is deleted.

[nabbster]

Ok well I have watched this file and its constantly being created and deleted as the user is performing an action So from what your saying this should only happen when finishing for the day?


I have a lot of set rs = nothing after each query as I thought this was right can some one confirm?

[GaryMazzone]

That is the correct action... Do you connect to the DB at app stratup and disconnect at app exit or do you open and close connections as needed... The first will create the ldb file once the app starts if it does not exist or add to the file if it is there and delete the file (or remove entry if others are in) once the last person exits.

The second will create and delete the file as actions are perfrmed.

[nabbster]

Ok so do either have an impact on performance in a multi user setup which is best

[GaryMazzone]

That depends on the version of VB you are using... .Net open and close when needed in VB6 I opened at app startup and closed the connection on app exit.

[si_the_geek]

Opppsss the penny has just dropped I want to add openForwardOnly and remove OpenStatic

Correct... and ideally you should also add the final parameter for .Open too (adCmdText).

Ok I made the changes I am sure they made some difference but In view of the 600 odd seconds the Query is taking they are very slight.

That was always a possibility unfortunately, because the time taken in situations like this can be due to many factors - and it turns out that in this case the vast majority of the time was due to something completely external to your code:

I thought I would compact and repair the db and saw a massive difference I thought it was the compact that did this but infact what I found is that because I was still on the front of the db from MS Access and when I closed MS Access this the query time increase massivly. Any pointers? Is this to do with the lock file that is refered to elsewhere? as I noticed this was open the whole time I was in the front end of Access.

It isn't the lock file directly, which in itself is not a problem - that just means somebody is working with the database somehow (with Access or via a VB program etc).

The issue is what the other user(s) are doing, and Access itself is very bad for that because it locks other users out of whichever table(s) it has open - which means a very big increase in times. You would not get that kind of thing with SQL Server etc.

I have a lot of set rs = nothing after each query as I thought this was right can some one confirm?

That is good if you have actually finished with the rs variable, but not it you are about to re-use it like this kind of thing:

        rs.Close
        Set rs = Nothing
        Set rs = New ADODB.Recordset '(required if you have removed the "Dim as New")
        rs.Open ....

In cases like that you should remove both of the Set lines, as they effectively negate each other (but take time to do it).


In terms of the connection, I fully agree with GaryMazzone on VB6 (I haven't used Access from .Net myself). In my experience keeping it open is the quicker option - and that is no better/worse in multi user situations.

[nabbster]

Hi - Thanks for your patients I will change so my users connect on opening and disconnect on closing.

as for the speed increase when I had the db open in MS Access(no tables open) i did some further testing I benched my code as a single user @ 27 seconds. and with Access open this dropped to 1.67 seconds.

When I hovered over the ldb it seems to increase to 128 bytes when you have MS access open rather than 64 bytes.

Is this what this guy is refering to? from the following sight

http://www.granite.ab.ca/access/perf...ldblocking.htm

Microsoft Access Performance FAQ - LDB locking which a persistent recordset connection fixes

(Last updated 2008/01/05)

When the symptoms encountered indicate that performance is acceptable with a single user in the database but drops significantly when two or more users are in the database, the problem may be caused by interaction with the LDB file.

In Access 2000, when a second and subsequent user tries to access a shared backend database on the server, there seems to be a situation where Access tries to perform a delete on the LDB file (which fails because another user is currently in the file). This attempt is made about 15 times before silently failing and the records are returned from the linked table.

To resolve this issue we need a persistent connection to the back-end from each of the front-end workstations. This can be done using a bound form which is always open or by keeping a recordset open at all times..

A good way of testing this is to ensure you are the only one opening the back end. Then run the front end database until it gets to the main menu. At this point you should see an LDB file present on the server with the same name as the back end.

If you don't see this LDB file then you know you don't have a persistent connection.

Thanks in advance.

One other question - I had noticed A field in my db that I do a sort on that is not indexed. I want to fix this. can I do this without risking loss of the current data in the db?

[si_the_geek]

I'm not entirely sure about what is described on that site, but it seems to be more about working within Access rather than using it externally... I could be wrong tho.

One other question - I had noticed A field in my db that I do a sort on that is not indexed. I want to fix this. can I do this without risking loss of the current data in the db?

That should be fine - but make a backup just in case.

Note however that indexes have bad points too, they will slow down any insert/update you do on that table.

[nabbster]

Ok thankyou - Re my code I originally posted I am wondering if this can be done much more efficiently basically I get a list of users into an array. I then query the db for each name and a 1 hr interval to get a count of what the user has done for that hour. this is repeted in side the loop for each hr of the working day and then the loop iterates to the next user and does the same. this is the slow routine I am running I am just wondering if this can be done in 1 query or certainly more efficiently than I have done it as I am very much a noob I can't believe I got it that right that it can't be done beter by a guru. I have posted lines of code before here and some one in the know has come back with 1 line of much faster code. Heres hoping

Thanks again

[si_the_geek]

It is likely to be possible... show us the current code, as it should be much easier for us to read than what you originally posted.

[nabbster]

Declared publicly
Code:

'//================================== db connection strings
Public cn As ADODB.Connection
Public rs As  ADODB.Recordset

In a Module
Code:

Public Sub cCon(shtname As String)
    
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & Sheet1.TextBox20.Text & "\CD Invoic.mdb;Jet OLEDB:Database Password=*******;"
    Set rs = New ADODB.Recordset
    cn.Open
End Sub

Query - Cut down version of the Query this basically reports what each user has done in each hour of the day.

Code:

Private Sub CommandButton4_Click() '//User Productivity count
Dim uRange As Integer
Dim aRange As Integer
Dim intHr As Integer
Dim MinUpDate As Field
Dim tbC As Field
Dim Count As Long
Dim BenchMark As Double

BenchMark = Timer

    clearit1 (shtname)
    cCon (shtname) 

    rs.Open "SELECT UserName FROM User_Lookup", cn, adOpenStatic, adLockReadOnly '//Get usernames for Capture list
        Sheet2.Range("H3").CopyFromRecordset rs
        uRange = rs.RecordCount
            rs.Close
        'Set rs = Nothing
        
    rs.Open "SELECT UserName FROM User_Lookup", cn, adOpenStatic, adLockReadOnly '//Get usernames for paid list
        Sheet2.Range("H20").CopyFromRecordset rs
            rs.Close
        'Set rs = Nothing
       
            
        For aRange = 0 To uRange - 1 '// Captures ************ from 06:00 to 19:00 ****************
            rs.Open "SELECT Count (*) as tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 06:00:00") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 10:00:00") & "# AND TB_PB='PB'", cn, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("I" & aRange + 3).Value = rs("tbC").Value
        End If
        rs.Close
        Sheet2.Range("I" & "2").Value = Format("10:00:00")

            rs.Open "SELECT Count (*) as tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 10:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 11:00:00") & "# AND TP_PB='PB'", cn, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("J" & aRange + 3).Value = rs("tbC").Value
        End If
        rs.Close
        Sheet2.Range("J" & "2").Value = Format("11:00:00")

            rs.Open "SELECT Count (*) as tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 11:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 12:00:00") & "# AND TP_PB='PB'", cn, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
               Sheet2.Range("K" & aRange + 3).Value = rs("tbC").Value
        End If
        rs.Close
        'Set rs = Nothing
        Sheet2.Range("K" & "2").Value = Format("12:00:00")

            rs.Open "SELECT Count (*) as tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 12:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:00") & "# AND TP_PB='PB'", cn, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("L" & aRange + 3).Value = rs("tbC").Value
         End If
        rs.Close
        Sheet2.Range("L" & "2").Value = Format("13:00:00")

            rs.Open "SELECT Count (*) as tbC FROM Workflow WHERE Captured_By='" & Sheet2.Range("H" & aRange + 3).Value _
            & "' AND Captured_Date BETWEEN #" & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 13:00:01") & "# AND #" _
            & Format(Sheet2.ComboBox1.Text, "yyyy/mm/dd 14:00:00") & "# AND TB_PB='PB'", cn, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
                Sheet2.Range("M" & aRange + 3).Value = rs("tbC").Value
        End If
        rs.Close
        Sheet2.Range("M" & "2").Value = Format("14:00:00")

Next aRange
    MsgBox Round(Timer - BenchMark, 2)
    Set rs = Nothing

Ok my code as is and I am sure this can be done in much less code and potentially return a faster query. basically the above is getting a list of names then looping through those names and counting how many records in the Caprued_By column have that name and the Captured_Date was between a time range so I can see by user how much the have done broken down by the hour.

Regards

[NeedSomeAnswers]

You do realise that most of the slowness you are experiencing is due to you using Access in a multi-user environment. With more than 10 Users Access struggles, and it will struggle more the more data you have in the database.

I have worked at places were they have banned Access due to the effect that it has on Network performance.

I know there are ways of optimising Access databases and your code (and most of them are good practise so you should do them as standard), but personally i would never build a system that had even more than 5 users in Access, especially now that you have SQL Server Express available for Free !

If you are at all able, really consider moving to SQL Server Express it will perform far better than Access, and as all you code is in a Front end rather than Access itself it shouldn't even be that difficult.

[nabbster]

Hi,

Thanks for the reply but I am fully aware of Access drawbacks in multi user environment. Its not a choice made by me its more the tools I have been given to work with. I also believe this has been said previously in this post.

As for the multi user setup its actually very rare to have 10 people using this app possibly one of the reasons that swayed the decision not to spend on further products. I am also fairly certain that by using COUNT(*) with GROUP BY I can do the task posted much quicker and avoid the use of loops with changing variables.

[si_the_geek]

I can't think of a decent way to re-write it to use a single query, but there are several improvements that can be made to the speed.

The first is to not reload the first set of data that you load, and instead just move back to the start of the recordset.

As the queries inside the loop are all the same apart from the values, this is an ideal candidate for using a Command object and Parameters... for an explanation of why they are a good idea in general see the FAQ article Why should I use Parameters instead of putting values into my SQL string?... and for details of how to use them see the article How do I use an ADO Command object?

Another improvement is to not read data from the spreadsheet or combo more often than you need to, because that takes much more time than reading from a variable - particularly when that value then needs to be converted from a String to a Date.

You can also get a small speed gain when referring to a single cell of a spreadsheet by using the .Cells method rather than the .Range method.


Here is your code with those changes (untested!):

Private Sub CommandButton4_Click() '//User Productivity count
Dim uRange As Integer
Dim aRange As Integer
Dim intHr As Integer
Dim MinUpDate As Field
Dim tbC As Field
Dim Count As Long
Dim BenchMark As Double

Dim objCommand As ADODB.Command
Dim datDate As Date

BenchMark = Timer

    clearit1 (shtname)
    cCon (shtname)

    '//Get usernames for Capture list + Paid list
    rs.Open "SELECT UserName FROM User_Lookup", cn, adOpenStatic, adLockReadOnly, adCmdText
    Sheet2.Range("H3").CopyFromRecordset rs
    uRange = rs.RecordCount
    
    rs.MoveFirst
    Sheet2.Range("H20").CopyFromRecordset rs
    rs.Close
       
    '// Captures ************ from 06:00 to 19:00 ****************
    With objCommand
      .ActiveConnection = cn
      .CommandText = "SELECT Count (*) as tbC " _
                   & "FROM Workflow " _
                   & "WHERE Captured_By=? " _
                   & "  AND Captured_Date BETWEEN ? AND ? " _
                   & "  AND TB_PB='PB'"
      .CommandType = adCmdText
      .Prepared = True
      .Parameters.Append .CreateParameter("Captured_By", adVarChar, adParamInput, 50)
      .Parameters.Append .CreateParameter("MinDate", adDate, adParamInput)
      .Parameters.Append .CreateParameter("MaxDate", adDate, adParamInput)
    End With
    datDate = Sheet2.ComboBox1.Text
    
    For aRange = 0 To uRange - 1
        objCommand.Parameters("Captured_By").Value = Sheet2.Cells(aRange + 3, "H").Value
        
        '(first "hour" separately, as it has a different range)
        objCommand.Parameters("MinDate") = datDate + #6:00:00 AM#
        objCommand.Parameters("MinDate") = datDate + #10:00:00 AM#
        Set rs = objCommand.Execute
        If Not rs.EOF Then
            Sheet2.Cells(aRange + 3, "I").Value = rs("tbC").Value
        End If
        rs.Close
        Set rs = Nothing
        Sheet2.Cells(2, "I").Value = "10:00:00"
        
        '(a loop for the rest)
        For lngHour = 10 To 13
          objCommand.Parameters("MinDate") = datDate + TimeSerial(lngHour, 0, 1)
          objCommand.Parameters("MinDate") = datDate + TimeSerial(lngHour + 1, 0, 0)
          Set rs = objCommand.Execute
          If Not rs.EOF Then
              Sheet2.Cells(aRange + 3, lngHour).Value = rs("tbC").Value
          End If
          rs.Close
          Set rs = Nothing
          Sheet2.Cells(2, lngHour).Value = Format(TimeSerial(lngHour, 0, 0), "hh:nn:ss")
        Next lngHour
    
    Next aRange
    Set objCommand = Nothing
    
    MsgBox Round(Timer - BenchMark, 2)

End Sub

I guessed at your Captured_By field being defined as 50 characters, if it is something else then change the number on the "CreateParameter" line.

Hopefully this (after any corrections you need to make!) will be noticeably faster than what you had before.

As for the multi user setup its actually very rare to have 10 people using this app possibly one of the reasons that swayed the decision not to spend on further products.

SQL Server Express is free - so the only "cost" is re-working some of your existing code.

[nabbster]

Hi Si,

Thanks for that very little correction was required. I totally understand the use of access vs SQLExpress but its not a cost issue that is the problem. I work for a company that if you didn't know beter you would think the I.T dept ran. thus getting low end solutions put in place takes ears at the moment as they are otherwise engaged. the powers that be decide what we can have on our desktops. I am not as you may of guessed by my lack of knowlege an I.T person I am a hobbyist that has been rafted accross to do this solution with the tools available on the desktops. I personally have SQL express installed my self and I have had help in the past from your self re some queries so I totally understand its value but by the time we got our I.T to agrre to it the window of opertunity would of definatly passed.

Anyway I also added an additional index to my db and ths made a big difference along with your code.
What I wanted to know is I guess i should limit my fields in the db to as small as possible? also I was thinking of moving all my closed records to a second table thus leaving the bulk of my queries only actions on the remaining 2000-3000 open records at any one time. that being the case the import routin I use to pull in data from text files would need to check the second table too before adding a record to avoid duplication. is it as simple as adding refernce to both tables in the select query? I.E

STRsql = "SELECT * FROM Table1, Table2
WHERE Table1.field1 OR Table2.field1='Criteria'
If not rs.EOF then
'do some thing here
end if
rs.close
Set rs = Nothing

Thanks in advance.

[si_the_geek]

An "archive" table can be a good idea in terms of speed, but it depends on the circumstances... if a relatively high percentage of the records are 'closed', and you exclude them from the queries that are run most frequently, it is likely to be a good thing.


Your query might work (once you add criteria to Table1.field1 too), but it certainly isn't ideal - if you have more than one table in a query, you either join them appropriately (not valid in this case), or get a Cartesian Product (in this case each valid row from one table linked to all rows in the other table).

What you should do instead is run separate queries for each table, and join the result sets using UNION ALL (the same as a UNION, but without a slow "DISTINCT" applied afterwards), eg:

SELECT * 
FROM Table1
WHERE Table1.field1 ='Criteria'

UNION ALL

SELECT * 
FROM Table2
WHERE Table2.field1 ='Criteria'

..and of course avoid using "SELECT *" if you can help it (for a simple .EOF check as you showed, just one tiny field would be best).