Copying data from an array to an Access table

Question

HansV pointed me to the GetRows method of the Recordset object: http://msdn.microsoft.com/en-us/library/office/ff194427.aspx for copying multiple rows of data from an Access table to an array.

After I edit the data in the array (which I assume is much faster than doing so in the Recordset), I want to write it back to the table.  What I don't know how to do is:

1. How do I get the edited array data back into the Recordset?
2. Is there a more efficient way to write the edited data from the Recordset back to the table, other than iterating through each record in the Recordset and using the Update method of the Recordset object?

Thanks in advance for any assistance.

Answer 1

Yes, I am aware of the Query Designer, and converting double quotes within a string to single quotes.  In fact, I always use Query Designer when first building a query.

Thanks for your feedback.

Answer 2

Did you know that to get the syntax correct for an SQL string in VBA that you can use the Query design feature and design the query in the matrix. When the query is working correctly, while in Query Design Mode,  you can select View -> SQL View and copy the syntax. You might need to convert double quotes within the string to single quotes and then just enclose the entire string in double quotes to assing to a string variable.

You can also just save the designed query and then just run it from within the VBA.

Answer 3

I don't believe that there is a more efficient method than looping to load the array into a table.

I doubt the accuracy of your assumption of loading the data into an array and then manipulating is faster. If it is, then only in theory and I doubt that you will be able to notice the difference in practice.

 

SQL queries are very fast. IMHO I think that you would be better to post a new question, including the code you are using to manipulate the data in the array, and ask "Can someone please provide example code to do this without loading into an array and then back to a table"?

Thanks for your feedback.  My assumption was based solely on my experience using arrays in Excel, which are extremely fast versus reading/writing to individual spreadsheet cells.

I find it interesting that Microsoft created the GetRows method in Access for copying multiple rows of data from a table to an array, but did not create a corresponding "PutRows" method for doing the reverse.

From an Access perspective, it would appear that manipulating a RecordSet or using SQL UPDATE queries is, in fact, the fastest way to manipulate data in a table.

Answer 4

  After I edit the data in the array (which I assume is much faster than doing so in the Recordset), I want to write it back to the table.  What I don't know how to do is:

1. How do I get the edited array data back into the Recordset?
2. Is there a more efficient way to write the edited data from the Recordset back to the table, other than iterating through each record in the Recordset and using the Update method of the Recordset object?

           

I don't believe that there is a more efficient method than looping to load the array into a table.

I doubt the accuracy of your assumption of loading the data into an array and then manipulating is faster. If it is, then only in theory and I doubt that you will be able to notice the difference in practice.

SQL queries are very fast. IMHO I think that you would be better to post a new question, including the code you are using to manipulate the data in the array, and ask "Can someone please provide example code to do this without loading into an array and then back to a table"?