Huge array makes VBA execute slowly

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:

Related 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

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?

Pass the '=' sign in an array to excel range
all. First see this website /forums/showthread.php?t=218472 thread for the beginning of my trouble.

I managed to find out the above problem was the data in my array was causing problems with the code. In the array was a series of ==== signs. When I changed the data to all 'a' (aaaaa) the code worked perfectly. My next question is I need to keep the === in the data, How would I pass the array to have forumula.Array see the === as string I guess?

Need your expertise. If you pull the file you will see what I am talking about

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

Query calling a custom function with an array
this might be something that is pretty basic, but I'm confused so I thought I would ask for your help.

I have a query called qry_A and it selects 6 installment dates (Instal1, Instal2, Instal3, Instal4, Instal5, Instal6) from an installment table. I want qry_A to call a custom VBA function called PrevWeekDay which takes the 6 installment dates and confirms that they are all weekdays. For the dates which fall on a weekend, I want to change them to the last weekday prior (I guess that would be the previous Friday).

What I am wondering is:

1 - Is it possible for me to call the function PrevWeekDay and pass the argument as an array which contains the 6 installment dates?
2- If I can use an array, how and where do I declare the array and how do I populate it with the installment dates?
3- Again, if I can use an array as the argument for the function, how do I pass back the updated installment dates to qry_A and update the installment table with the updated values

sorting a dictionary
In javascript you can sort an array with "array.sort(sortfunc)" where sortfunc is a function which compares two items that you can design yourself. This has always been tremendously useful for me.

Is there a comparable way to sort dictionaries or arrays in VBA

How to transfer a 2d array into a table
I have a 2d array, with 10 columns, and somewhere between 200 and 10.000 rows. I was hoping there was a simple(and efficient) way to transfer the entire array into a table containing over a million rows, and growing.

I allready know how to transfer it by looping through the array, adding 1 row at a time. However, this is not that effective, and I believe that one of the problems stems from the fact that the main table has its indexes updated each time a row is added.

I could probably work around this by first moving the array into a temporary table, and then use SQL to add all the rows at once.

Tables open slowly
I have a user who was complaining because a particular database feature that she uses a lot is running painfully slow. And it's true. It is ridiculous.

So I was trying to figure out why. What I noticed is that it's taking her computer a lot longer than others to open tables. It doesn't matter if it's a local Access table or if it's linked to SQL. It opens unusually slowly.

1. She has a newer computer with plenty of memory.
2. She's on the same network as the other users and I didn't notice other things on the network taking unusually long to open.
3. If I open a form, it will open quickly, but maybe that's because while it does load all the records, it only needs to have loaded one for the form to open. At least I assume that makes a difference.

Has this kind of thing been a problem for anyone else? Does anyone have any idea why this might be happening

querydef won't execute
I have a query that executes just fine by double clicking it or by hitting the red ! to execute. However for some reason it will not work using the querydef.execute method.

It says data type mismatch in criteria expression. What I cannot figure out is why this would fail only when running it throughthe execute method of the querydef object.

Join on a Range of an array
I would like to do a Join on a part of an array. My code would look like this:

strJoinedVars(1) = Join(strItemsArray(1:1000), ",") strJoinedVars(2) = Join(strItemsArray(1001:2000), ",") strJoinedVars(3) = Join(strItemsArray(2001:3000), ",")

Obviously the strItemsArray(2001:3000) bit is wrong. Is there a correct way to do this, besides writing a UDF which loops through the array, stores the selected data into a different array and returns the new array?

Count commas and use as array size
I need to use VBA to retrieve a record from a query, count the number of commas, and use that number +1 as the size of an array. How do I go about doing this

VBA + SQL statement creation/execution.
I have a form with a lot of unbound fields. I chose this method because I need a lot of control over the form. I have completed everything I need, seemingly, except writing the information to the database.

I have declared a string variable to dynamically generate my INSERT sql statement then wish to execute it.

I am having two issues:

1) a string can only be 256 characters. what data type can I use for a much longer statement?

2) how do I execute my string (or whatever new datatype you suggest) against the database and have it return an error code so I can be sure it happened; for example, just using CurrentDB.Execute(sql) does not tell me if anything went wrong

How to run a Macro or VBA code in another database
I am designing 2 databases (frontend and backend) and writing all my macros and vba in the backend database. In the frontend database I basically have a form for user to select options (button commands).

I want to link this command buttons from Frontend database to run VBA codes/macros in the backend database.

So far I tried 2 options and none of them are working

1) Shell command - I used the following command found on internet
Call Shell ("filepath \x Macroname, 1)
I get run time error 76

(It allows ONLY to run queries and NOT Macros)

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("filepath")
'db.Execute "query1"
db.Execute "query2"
db.Execute "query3"

This runs query 1 and query 2 but give RUN TIME ERROR 6 (overflow for query3)