By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Inserting an Array of Data into an Access Table

100+
P: 119
I have a large array of data (1000 x 40 x 3) that I am inserting into a database table. It is incredibly slow, and so I was wondering if there is a quicker way of inserting array data into a table.

Here is the code I am currently using:

Expand|Select|Wrap|Line Numbers
  1.     ' [Date],[Security],[Field]
  2.     With rs
  3.         For nSec = 0 To UBound(vtData, 2)
  4.             For ndate = 0 To UBound(vtData, 1)
  5.                 For nfield = 1 To UBound(vtData, 3)
  6.                     If IsNumeric(vtData(ndate, nSec, nfield)) Then
  7.                         ' Add to results table
  8.                         .AddNew
  9.                         !Security = vtSec(nSec)
  10.                         !Date = vtData(ndate, nSec, 0)
  11.                         !FieldName = vtFields(nfield - 1)
  12.                         !FieldData = vtData(ndate, nSec, nfield)
  13.                         .Update
  14.                     End If
  15.                 Next
  16.             Next
  17.         Next
  18.     End With
  19.  
Jan 20 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
It may help to surround the whole process with
Access.Application.DBEngine.BeginTrans
and
Access.Application.DBEngine.CommitTrans
Jan 20 '09 #2

100+
P: 119
That seems to have helped a lot - thanks.

As an aside - is looping through the array the only/best way of inserting data into a table?
Jan 21 '09 #3

Expert 100+
P: 1,287
I haven't found any alternative in vba, or vb for that matter.
Jan 21 '09 #4

NeoPa
Expert Mod 15k+
P: 31,494
Like Chip, I'm not aware of any alternative way of importing data from an array.

How did the data arrive in the first place though? It may be that the data can better be imported from there.
Jan 28 '09 #5

100+
P: 119
The data arrives in an array. Thanks for all your input.
Jan 28 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.