Split database, SLOW performance

[darkstar257]

Thanks for all the tips.

I have another issue once I split the database that seems odd.

In my coding for some forms I open a recordset for a TABLE and use dbOpenTable since it's a table.

    Set db = CurrentDb()
'    Set rst = db.OpenRecordset("Item_Line", dbOpenTable) 'dbOpenTable
    Set rst = db.OpenRecordset("Item_Line", dbOpenDynaset) 'dbOpenDynaset

Once I split into a front end and backend, the form operations now give me a "Invalid Operation" error on the recordset lines. I changed them to "dbOpenDynaset" instead and now it works. But I thought dbOpenDynaset was only for queries and such. How has a split database could've affected this?

 

[HiTechCoach]

Thanks for all the tips.

I have another issue once I split the database that seems odd.

In my coding for some forms I open a recordset for a TABLE and use dbOpenTable since it's a table.

    Set db = CurrentDb()
'    Set rst = db.OpenRecordset("Item_Line", dbOpenTable) 'dbOpenTable
    Set rst = db.OpenRecordset("Item_Line", dbOpenDynaset) 'dbOpenDynaset

Once I split into a front end and backend, the form operations now give me a "Invalid Operation" error on the recordset lines. I changed them to "dbOpenDynaset" instead and now it works. But I thought dbOpenDynaset was only for queries and such. How has a split database could've affected this?

The parameter dbOpenTable is only valid for local tables. I actually NEVER use it since I only would with split databases.

I would recommend that you always use dbOpenDynaset.

I also find it not good for performance to open a table unless you really wan to process every record in the table. It is generally better to use an SQL statement to retrieve only the fields and records needed.

 

[darkstar257]

The parameter dbOpenTable is only valid for local tables. I actually NEVER use it since I only would with split databases.

I would recommend that you always use dbOpenDynaset.

I also find it not good for performance to open a table unless you really wan to process every record in the table. It is generally better to use an SQL statement to retrieve only the fields and records needed.

I used dbOpenDynaset before, but Access gives me an error due to it being a local table before I split the database. Now i have to use dbOpenDynaset since it is split. I was using recordsets in a while loop to grab specific information from subforms for auto emails. Didn't know how to do it using SQL statements.

 

[HiTechCoach]

I used dbOpenDynaset before, but Access gives me an error due to it being a local table before I split the database. Now i have to use dbOpenDynaset since it is split.

AFAIK, using dbOpenDynaset does not care if the table is local or linked.

I was using recordsets in a while loop to grab specific information from subforms for auto emails. Didn't know how to do it using SQL statements.

How were you limiting the records in the recordset to only these in the sub form?

Will you post the code that you were using the dbOpenTable to process that data in a sub form?

 

[darkstar257]

AFAIK, using dbOpenDynaset does not care if the table is local or linked.

Really? In that case I have no idea why. I had an error in this line before the database split:

Set rst = db.OpenRecordset("Item_Line")

Using dbOpenDynaset and leaving it default blank, both gave me errors. It works when I declared it as a dbOpenTable. But now dbOpenDynaset works when the database is split.

How were you limiting the records in the recordset to only these in the sub form?

Will you post the code that you were using the dbOpenTable to process that data in a sub form?

I didn't limit the records as the subform only contains one table called Item_Line. I had to go through all items in the subform's table to match quantity shipped vs. qty ordered. And as the recordset is cycled to check each line item, I am also appending them to a string array so I can send out as an email later. I needed to check every item in the subform's table so I can only think of doing it this way as I don't know how to use SQL for this.

    qtyMatch = True 'Global boolean for matching quantity shipped
    blDimensioned = False

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Item_Line", dbOpenDynaset) 
    FormID = Forms![Packing Slip]!ID
    
    'Creates strOrderList string array of ordered items and updates global boolean for shipping qty
    Do While Not rst.EOF
        If FormID = rst![ID] And Len(rst![Description]) <> 0 Then
            If blDimensioned = True Then 'Has array been dimensioned?
                ReDim Preserve strOrderList(0 To UBound(strOrderList) + 1) As String
            Else
                ReDim strOrderList(0 To 0) As String
                blDimensioned = True
            End If
        'Add item to list
            strOrderList(UBound(strOrderList)) = "Qty Shipped: " & rst![Qty Shipped] & " " & rst![Unit of Measure] & "   " & rst![Description] & vbCr & _
            "Ship Date: " & rst![ShipDate] & "   " & "Tracking No.: " & rst![TrackingNo] & vbCr
            If (rst![Qty Shipped] < rst![Qty Ordered] Or IsNull(rst![Qty Shipped]) Or rst![Qty Shipped] = 0) Then
                MsgBox ("Notice: You are only shipping " & rst![Qty Shipped] & vbCr & " of the " & rst![Qty Ordered] & " " & rst![Description] & " ordered")
                qtyMatch = False
            End If
            rst.MoveNext
        Else
        rst.MoveNext
        End If
    Loop
    
    'Displays individual items in string array
    For arrayPosition = LBound(strOrderList) To UBound(strOrderList)
        strBuf = strBuf & strOrderList(arrayPosition) & vbCr
    Next arrayPosition

....
...
strSubject = "Order Notice"
strEmailMsg = "...." & strBuf & "..." ....
SendCDO TheAddress, strEMailMsg, strSubject, Fileattachment
....

 

[HiTechCoach]

I didn't limit the records as the subform only contains one table called Item_Line. I had to go through all items in the subform's table to match quantity shipped vs. qty ordered. And as the recordset is cycled to check each line item, I am also appending them to a string array so I can send out as an email later.

That is not what your code indicated!

I needed to check every item in the subform's table so I can only think of doing it this way as I don't know how to use SQL for this.

That is not what your code indicated!

Why read every recon in the table when you only really need the records that match Forms![Packing Slip]!ID?

If there are 1000 record in the table used by the sub form and you only need the last 10 record in the table you will have to read all 1000 record with your code and not just the 10 needed. Does that sound efficient?

If Forms![Packing Slip]!ID only has one record that needs to be updated you code will still read every single record. Does that sound efficient?

The line:

If FormID = rst![ID] And Len(rst![Description]) <> 0 Then

Indicate you aonly need a select number of record WHERE these conditions are true. TIP: The bold word are clue to a query SQL statement.

DIM strSQL as String

strSQL = "SELECT "Item_Line".* from "Item_Line" WHERE [ID] = " & Forms![Packing Slip]!ID & " And Len([Description]) > 0

Set rst = db.OpenRecordset(strSQL , dbOpenDynaset)

Now you only have the record you need to process. Not the whole table!

    'Creates strOrderList string array of ordered items and updates global boolean for shipping qty
    Do While Not rst.EOF
            If blDimensioned = True Then 'Has array been dimensioned?
                ReDim Preserve strOrderList(0 To UBound(strOrderList) + 1) As String
            Else
                ReDim strOrderList(0 To 0) As String
                blDimensioned = True
            End If

        'Add item to list
            strOrderList(UBound(strOrderList)) = "Qty Shipped: " & rst![Qty Shipped] & " " & rst![Unit of Measure] & "   " & rst![Description] & vbCr & _
            "Ship Date: " & rst![ShipDate] & "   " & "Tracking No.: " & rst![TrackingNo] & vbCr
            If (rst![Qty Shipped] < rst![Qty Ordered] Or IsNull(rst![Qty Shipped]) Or rst![Qty Shipped] = 0) Then
                MsgBox ("Notice: You are only shipping " & rst![Qty Shipped] & vbCr & " of the " & rst![Qty Ordered] & " " & rst![Description] & " ordered")
                qtyMatch = False

    rst.MoveNext

    Loop

 

[darkstar257]

That is not what your code indicated!


That is not what your code indicated!

Why read every recon in the table when you only really need the records that match Forms![Packing Slip]!ID?

If there are 1000 record in the table used by the sub form and you only need the last 10 record in the table you will have to read all 1000 record with your code and not just the 10 needed. Does that sound efficient?

If Forms![Packing Slip]!ID only has one record that needs to be updated you code will still read every single record. Does that sound efficient?

The line:

If FormID = rst![ID] And Len(rst![Description]) <> 0 Then

Indicate you aonly need a select number of record WHERE these conditions are true. TIP: The bold word are clue to a query SQL statement.

DIM strSQL as String

strSQL = "SELECT "Item_Line".* from "Item_Line" WHERE [ID] = " & Forms![Packing Slip]!ID & " And Len([Description]) > 0

Set rst = db.OpenRecordset(strSQL , dbOpenDynaset)

Now you only have the record you need to process. Not the whole table!

    'Creates strOrderList string array of ordered items and updates global boolean for shipping qty
    Do While Not rst.EOF
            If blDimensioned = True Then 'Has array been dimensioned?
                ReDim Preserve strOrderList(0 To UBound(strOrderList) + 1) As String
            Else
                ReDim strOrderList(0 To 0) As String
                blDimensioned = True
            End If

        'Add item to list
            strOrderList(UBound(strOrderList)) = "Qty Shipped: " & rst![Qty Shipped] & " " & rst![Unit of Measure] & "   " & rst![Description] & vbCr & _
            "Ship Date: " & rst![ShipDate] & "   " & "Tracking No.: " & rst![TrackingNo] & vbCr
            If (rst![Qty Shipped] < rst![Qty Ordered] Or IsNull(rst![Qty Shipped]) Or rst![Qty Shipped] = 0) Then
                MsgBox ("Notice: You are only shipping " & rst![Qty Shipped] & vbCr & " of the " & rst![Qty Ordered] & " " & rst![Description] & " ordered")
                qtyMatch = False

    rst.MoveNext

    Loop

I know it's incredibly inefficient to search through the whole table like this. But as said before, I don't know how SQL statements work as I don't have much knowledge of Access and VB, but I only know some programming. I learned Visual Basic just by reading Access module code and sample codes from this forum. So the only way I know how to search for items from a record is by writing the actual low level script for it (ie. searching through the whole table) instead of using the higher level SQL to directly do this. I've seen SQL statements like this used in Access query forms but never knew how to use them in VB code directly. Thanks for showing me though

 

[HiTechCoach]

You're welcome.

I think that learning to make queries and SQL statements is one of the most important thing to learn about Access. If I had to rank what to learn first between queries and VBA coding I would say queries first. VBA second.

This might be helpful: Overview of Access Queries

 

[darkstar257]

You're welcome.

I think that learning to make queries and SQL statements is one of the most important thing to learn about Access. If I had to rank what to learn first between queries and VBA coding I would say queries first. VBA second.

This might be helpful: Overview of Access Queries

Cool website. I found some interesting samples another way to do dynamic search and CDO which I also recently used.

However, I tried out the SQL statement, but I seem to be getting the same "End of Statement" error that most SQL beginners are running into.

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT "Item_Line".* from "Item_Line" WHERE [ID] =  " &  Forms![Packing Slip]!ID  &  "  And Len([Description]) > 0

Gives me a "End of Statement" error

So I just tried using an asterisk to grab everything instead of .*

strSQL = "SELECT * FROM Item_Line WHERE [ID] = """ & Forms![Packing Slip]!ID & """ AND Len([Description]) > 0"

Gives me a "To few paremeters. Expected 1" error

I think something with the quotations is messing me up with the Item_Line table. The Access VB compiler is not liking quotations "Item_Line" highlighting it with an end of statement error

 

[HiTechCoach]

Glad you found my site helpful.

Object name (tables, fields, etc.) are wrapped with [ ] as the delimiter..

Quotes are used as a delimiter around literal text strings.

If the [Item_Line].[ID] field is a numeric data type then use:

strSQL = "SELECT [Item_Line].* from [Item_Line] WHERE [Item_Line].[ID] =  " &  Forms![Packing Slip]!ID  &  "  And Len([Description]) > 0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If the [Item_Line].[ID] field is a text data type then use:

strSQL = "SELECT [Item_Line].* from [Item_Line] WHERE [Item_Line].[ID] =  """ &  Forms![Packing Slip]!ID  &  """  And Len([Description]) > 0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

 

[darkstar257]

Thanks Boyd! May I ask what is the difference between using the SELECT * which selects all columns

strSQL = "SELECT * FROM

and...

strSQL = "SELECT  [Item_Line].* FROM

Is it just to SELECT the first column which contains the [ID] for the WHERE matches part of the SQL statement? If that is the case how much faster performance would I see from SELECTing * and SELECTing .* ?

 

[HiTechCoach]

Thanks Boyd! May I ask what is the difference between using the SELECT * which selects all columns

strSQL = "SELECT * FROM

and...

strSQL = "SELECT  [Item_Line].* FROM

Is it just to SELECT the first column which contains the [ID] for the WHERE matches part of the SQL statement? If that is the case how much faster performance would I see from SELECTing * and SELECTing .* ?

Neither of your examples are valid since the table name is missing after the FROM part. This is a very important part to how it works.

I find it best to fully qualify the commands as much as possible.

strSQL = "SELECT  * FROM [Item_Line]

and

strSQL = "SELECT  [Item_Line].* FROM [Item_Line]

are identical is results.

The second one will compile and optimize faster. This is important when building the SQL in VBA code.


Actually you should avoid very using "Select *" . This forces Acess to look up all the field names every time the query running. This only add extra processing overhead. Any extra processing does slow done the performance.


I only used the "SELECT [Item_Line].*" as filler. The important part what the WHERE clause section. That is the part you really need to learn and understand.

 

[AnilBagga]

Part of the problem may also be how you set up the database's locking.

I have set up a split FE/BE in which the FE is on each desktop sharing a common BE file. However, I've taken certain steps.

1. NO form ever opens a table as a recordsource or in underlying VBA DAO recordsets. Instead, every form (and report, for that matter) opens a QUERY even if it is the moral equivalent of SELECT * FROM tUNDERLYING ;

2. When I open recordsets, they are always set to optimistic locking. The queries are set to minimum locking or optimistic locking.

3. The problem we see isn't FE/BE related, it is network-contention related. In the morning, what I do takes a few seconds. When it hits the afternoon, we see less than 20 seconds for a massive update turn into more than 20 MINUTES for a simple update.

4. The corruption occurs even for this configuration, usually when the network gets glitchy and dumps users or just slows them down to the limit of their (im)patience.

We are working on getting a backend file server for our department, one that does not involve cross-country hops to the centralized server we are currently using.

I super-highly recommend the split FE/BE database. I keep a digitally signed, compacted copy of the FE file on the BE server. The file knows its own version and can tell when it is out of sync with the BE, so when I push UP a new version, the folks with the old version on their desktops gets warned to just make a new copy of the FE. (Yes, I could do that for them but my priorities are to get other features to work first.)

Hi,

I run a FE/BE split database and have the speed issues. The Fe file is copied at each users computer and all the tables are linked to the be file using the UNC path to the be file. As the DB has grown, the speed is a major issue, specially when editing reports and queries in the fe, not so much when you run them.


The tables in the fe file are still linked to the be but the path will be straight forward as it is the same drive. Correct and this makes the fe run faster. What I could not understand is how do the users use this fe file?

 

[CJ_London]

This is a 20 year old post with more responses 10 years ago

see you in 2031?

suggest you start a new thread - technology has changed a lot over the last 20 years

as a tip, you say the dB has slowed as the data grows. This implies a number of things at the top of which is a lack of indexing, inefficient queries and forms based on whole tables. So when you start your new thread, provide these details

 

[NauticalGent]

see you in 2031?

 

[Pat Hartman]

I found that leaving the DB in one piece, compiled and then converted to an MDE and copied onto a server (with shortcuts on users' desktops pointing to it) resulted in much better performance on this LAN.

This is the worst solution possible and the one that will most likely cause data loss. It also makes maintenance of the FE impossible without disrupting the users.

DBL explained why splitting is NOT more difficult to maintain than a monolithic implementation and in fact is significantly easier.

LANs are each different and beyond my pay grade. If you have IT support, see if you can get them to explain the topography and the speed and we might be able to make suggestions on how to make it work better with Access.

One other thing that might help is creating a new form bound to a table that doesn't get much use. You can make it a dummy table if you want. Open this form when the database open. In the form's open event, it can open the menu and set its own .Visible property to false so it stays hidden. The effect of this is to make a permanent connection to the BE. It will solve a lot of slowness issues especially when you have a very small user base. The way Access works is it opens the BE when you open a bound form or report or run a query. When the operation is done, it closes the BE unless it has another user logged in doing something. Even without any actual I/O, the hidden bound form will keep the BE from closing until the "last" connection closes.

Also, make sure that the BE is NOT set to compact on close. The FE's shouldn't either. Set up a schedule to Compact the BE when there is no user activity every week or so depending on how quickly the BE grows and if you have any delete activity.

 

[gemma-the-husky]

Hi,

I run a FE/BE split database and have the speed issues. The Fe file is copied at each users computer and all the tables are linked to the be file using the UNC path to the be file. As the DB has grown, the speed is a major issue, specially when editing reports and queries in the fe, not so much when you run them.


The tables in the fe file are still linked to the be but the path will be straight forward as it is the same drive. Correct and this makes the fe run faster. What I could not understand is how do the users use this fe file?

I am reluctant to post on a very old thread.

However, The main issue that affects speed might be not having a persistent connection to the database. If you don't maintain one, have a look at that.

Secondly make sure your database is well normalised, so it works efficiently.

Thirdly, queries can move a lot of data around the network, so try to ensure that queries are as efficient as possible.

As others have said, use a LAN not a WAN, and try to use wired connections, rather than wireless.