VBA to create dynamic table - slow to run

[bodders24]

I hope someone can help me with a performance issue in creating dynamic tables using VBA.

I have a generic procedure which rebuilds a table to store the contents of a crosstab query. This table is then used to populate a subform. On one particular table the code takes around 4 seconds to run – on other tables it is <100ms. This is the SQL to create the table:

CREATE TABLE tblHolidayDateXT (
Description TEXT (255),
[04-Sep-2022] TEXT (255),
[05-Sep-2022] TEXT (255),
[06-Sep-2022] TEXT (255),
[07-Sep-2022] TEXT (255),
[08-Sep-2022] TEXT (255),
[09-Sep-2022] TEXT (255),
[10-Sep-2022] TEXT (255),
[11-Sep-2022] TEXT (255),
[12-Sep-2022] TEXT (255),
[13-Sep-2022] TEXT (255),
[14-Sep-2022] TEXT (255),
[15-Sep-2022] TEXT (255),
[16-Sep-2022] TEXT (255),
[17-Sep-2022] TEXT (255),
[18-Sep-2022] TEXT (255),
[19-Sep-2022] TEXT (255),
[20-Sep-2022] TEXT (255));

Can anyone suggest why this should take (relatively) such a long time to process – when I run it directly against the database it creates the table instantly.

Thanks for your help

 

[The_Doc_Man]

Is it that slow to create the table or to create and then populate it?

 

[bodders24]

Actually both. There are only two records to add but that takes 4 seconds as well

 

[theDBguy]

Hi. Welcome to AWF!

What is different between the two computers?

PS. I may have read your question incorrectly. I thought you said running the code on one computer was slower than from another one.

 

[The_Doc_Man]

Is this a monolithic (unsplit) database, split and native Access for front-end and back-end, or Access front-end + some flavor of SQL backend? Slow table creation via DDL shouldn't be that slow.

 

[bodders24]

This is an Access front-end and Access back-end. This table is being created on the front end. Size of the dB is about 30 Meg.

 

[bodders24]

Hi. Welcome to AWF!

What is different between the two computers?

PS. I may have read your question incorrectly. I thought you said running the code on one computer was slower than from another one.

It's all on the same computer. Intel i5 with 8Gb Ram.

 

[The_Doc_Man]

But on re-reading the original question, I need more information because I don't see what is going on.

when I run it directly against the database it creates the table instantly

OK, got that. What exactly are you doing when you DON'T run this directly against the database? I don't have a clear picture of what you are doing in a mechanical sense for this failing case.

 

[Cronk]

Instead of recreating the tables with the consequent bloat, you could try to change the field names. If this process is being done daily, there would only be one field to change name.

 

[bodders24]

But on re-reading the original question, I need more information because I don't see what is going on.



OK, got that. What exactly are you doing when you DON'T run this directly against the database? I don't have a clear picture of what you are doing in a mechanical sense for this failing case.

It's a database I have created to plan my holidays. There is a form that shows the details for each holiday and two subforms, one for dates and one for payments. They are dynamically recreated each time the holiday details are updated.

 

[bodders24]

Instead of recreating the tables with the consequent bloat, you could try to change the field names. If this process is being done daily, there would only be one field to change name.

I think this might be worth trying. I will let you know if it works. Thanks.

 

[The_Doc_Man]

They are dynamically recreated each time the holiday details are updated.

Yes, but HOW are they dynamically created?

Cronk's suggestion is a good one, but if you rename the columns, that won't empty out the prior contents, thus possibly causing a bulk delete, which leads directly to bloat. You said there were only a couple of records involved, so that will not be MUCH bloat, but for a daily process it will add up.

 

[CJ_London]

On one particular table the code takes around 4 seconds to run – on other tables it is <100ms.

do your other tables have the same really bad field naming convention? Field names should not start with a number and Description is a reserved word and it may be that is what is causing the issue.

 

[isladogs]

Why create the table at all?
Why not use the crosstab query as the record source for the subform?
That will save time but the subform will be read only. Does that matter?

 

[bodders24]

Thanks for the responses.

I have tried changing the field names to remove "Description" and those starting with a number - this made very little difference.

I will try the suggestion from Isladogs to make the crosstab query as the source - it does not matter that it is read only.

 

[isladogs]

It should help!

 

[MarkK]

Another consideration, if you just need a quick list to bind to an Access control or Subform, is you can create a disconnected ADODB.Recordset. This will be very fast, and it will not cause the bloat that is common with creating and destroying tables.

Here's a class that makes it dead simple to create disconnected ADODB.Recordset objects...

Private m_rs As ADODB.Recordset

'******************************************************** Aug 17 2021 *****
'
'   This class provides a quick way to construct a disconnected ADO Recordset
'   Fields have to be added before the recordset can be opened.  Key field
'   must be appended first if a key field is used at all.
'
'**************************************************************************
Property Get Recordset() As ADODB.Recordset
    If m_rs Is Nothing Then Err.Raise 5, , "Add fields before referencing the Recordset property"
    With m_rs
        If .State = 0 Then
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .LockType = adLockPessimistic
            .Open
        End If
    End With
    Set Recordset = m_rs
End Property

Sub AppendField(Name As String, adType As ADODB.DataTypeEnum, Optional Size As Integer, Optional Attrib As ADODB.FieldAttributeEnum)
    If m_rs Is Nothing Then Set m_rs = New ADODB.Recordset
    m_rs.Fields.Append Name, adType, Size, Attrib Or adFldMayBeNull
End Sub

Sub AppendKeyField(Name As String)
    If Not m_rs Is Nothing Then
        Err.Raise 5, , "Key field must be appended first"
    Else
        AppendField Name, adInteger, , adFldKeyColumn
    End If
End Sub

Sub AppendTextField(Name As String, Size As Integer)
    AppendField Name, adVarChar, Size
End Sub

Here's code that uses the class to create the recordset you described in your post #1...

    Dim rs As ADODB.Recordset
    Dim i As Date
    
    With New cADORecordsetFree
        .AppendTextField "Description", 255
        With .Recordset
            For i = #9/4/2022# To #9/22/2022#
                .AddNew
                !Description = i
                .Update
            Next
        End With
        Set rs = .Recordset
    End With

To bind a Listbox, Combobox, or Form to the new recordset, set the Recordset property like...

Set Me.MySubformControl.Form.Recordset = rs

hth

 

[bodders24]

A follow-up to the above & thanks again for the responses.
I looked at the cross-tab query which was the recordsource for the subform, and optimised by using a subquery. The result was that it solved the probem instantly, and reduced the time to run by a factor of 100