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
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