Topics Search

Huge array makes VBA execute slowly

Huge array makes VBA execute slowly
Views: 27
access programmers,

I have some VBA code running in a macro which populates an array with rows from a text file. The array resizes dynamically using 'redim'.

The code runs fine until I attempt to load a large text file: c. 300,000 rows. The array itself builds absolutely fine (and surprisingly quickly) but from then on, all VBA code runs painfully slowly. Even stepping over a simple "x=x+1" line takes long enough for me to see "Running." in the title bar of the window.

If I destroy this array, the VBA code runs normally again.
Sponsored Links:

More topics


Creating Combo Box from a Table with Custom 1st Item

I'm trying to create a combo box that lists all the names from a table, but I want the first item to be "Add New". I think I can do it by:
1. loading all the names from the table into an array starting at array[1]
2. insert "Add New" to array[0]
3. use array to build combo box

Is there any simpler way than this? If the table contains huge number of records, it could e a memory hog

String Arrays and Function Calls

I have some very important code that I need to write myself but am relatively new to VBA.

I need to be able to:

1.) Obtain the field names of a query
2.) Place those field names in a string array
3.) Use the string array in looped a function call

I found some good tutorials on the first two, however I cannot find anything on the last. I know it is possible to use variables of a string array in a function call in the C languages, but is it in VBA and if so, how can I do it

save results from a query into an Array?

I've been programming in VBA in Excel for awhile, but am new to Access.

Is there a way to run a SQL Query and store the values into an array for further manipulation through VBA? (I don't really want the query output to the screen

Output array to a table

I created a function in VBA that takes strings from different access tables and splits them into one multidimensional array. How do I output this multidimensional array to a new table in access?
Also, how do I get this to happen when the user runs a query

Writing Excel VBA arrays to MS Access tables

I have been struggling with the following problem: I have a Excel file called 'A' which contains 53 tabs with three columns of data (250000 rows) in each. These data are the results of simulation, written from Excel array to array range. But since it takes awful long to save and work with large Excel file, I would like to print each VBA array directly to MS access tables (access file is called 'DikesSim').

It would be great if I could create new tables in access with headers when writing Excel array to Access. I have Excel and Access 2010!

Huge Union Query Taking hours

I'm trying to execute the query below. I has been running for more than one hour, and my computer is not too responsive anymore. I'm just trying to put these 26 tables that contain the samedata (different months) into one table.

Each table has about 50,000 lines. So the total would have around 1.2 millions lines, which is not that huge. My Access DB is around 600 megs before the union.

What's the best way to create this master table if union doesn't work? Append maybe?

Passing value from table in a subform to an array!

I am very new to VBA & Access.

I want to use the data populated in a table in the subform. I want to pass the value into an Array

How to pass a normal datatype argument to an array parameter?

I have an array parameter named listofiles() but sometimes I need to pass a single file too. How to pass it to an array parameter?
Is it possible to pass the 'List of items' in a listbox as an array arugment to an array parameter directly

Executing SQL Statements in VBA Code

There are a number of ways to execute a SQL Data Manipulation Language (DML) statement from Microsoft Access, besides the obvious process of creating an Action Query and double-clicking its icon. Understanding your options with respect to executing SQL will make your code cleaner, give you more flexibility and we'll even throw in a great debugging feature as an added bonus.
The following article, while not exploring every facet and option, will demonstrate how to execute SQL using the following methods:
* DoCmd.RunSQL
* DoCmd.OpenQuery
* [Querydef].Execute
* [Database].Execute
* dbFailOnError

Storing Values into Dynamic Array

I am having so much trouble with this. As I am searching through the text file, if I find a value that looks like a date that I need to store, I want to store it into an array; pubTransDates().

However, I am running into a subscript error because I do not have an idea of the size of the array. If I attempt to reDim my array each time I encounter a value that I want to store, it loses the other values.

Any way to store into a dynamic array without having to run my code twice, the first time to count and the second time to store?