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: -
' [Date],[Security],[Field]
-
With rs
-
For nSec = 0 To UBound(vtData, 2)
-
For ndate = 0 To UBound(vtData, 1)
-
For nfield = 1 To UBound(vtData, 3)
-
If IsNumeric(vtData(ndate, nSec, nfield)) Then
-
' Add to results table
-
.AddNew
-
!Security = vtSec(nSec)
-
!Date = vtData(ndate, nSec, 0)
-
!FieldName = vtFields(nfield - 1)
-
!FieldData = vtData(ndate, nSec, nfield)
-
.Update
-
End If
-
Next
-
Next
-
Next
-
End With
-
5 10047 ChipR 1,287
Recognized Expert Top Contributor
It may help to surround the whole process with
Access.Applicat ion.DBEngine.Be ginTrans
and
Access.Applicat ion.DBEngine.Co mmitTrans
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?
ChipR 1,287
Recognized Expert Top Contributor
I haven't found any alternative in vba, or vb for that matter.
NeoPa 32,569
Recognized Expert Moderator MVP
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.
The data arrives in an array. Thanks for all your input.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Joachim Klassen |
last post by:
Hi all,
first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-).
We are having performance problems when inserting/deleting rows from a large
table.
My scenario:
Table (lets call it FACT1) with 1000 million rows distributed on 12
|
by: Alistair |
last post by:
Hi all,
I am creating a database based site that keeps track of books, who has read
them and the comments they have.
After a little help in M.P.I.asp.DB I managed to create a database (access
2000) as follows
USERS TABLE
|
by: Jaraba |
last post by:
I am working in a project that I need to parse an arrayt an select
records based upon the values parsed. I used the functions developed by
Knut Stolze in his article 'Parsing Strings'. I am particulary having
problems inserting records using a function.
I am attaching the code for your review. Please, help.
=================================--Function
1--====================================
|
by: freeskier |
last post by:
Hello,
Sorry for the newb question; I've spent a good amount of time trying to figure this out. I am fairly new to the Postgre world. I am currently in the process of upsizing several MS Access apps to a single PostgreSQL system; I will continue to use Access as the frontend. I have duplicated the table structure in Postgre and have successfully connected Access as a frontend to view data.
I am having trouble inserting a record into...
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database.
The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an...
| |
by: AlexanderDeLarge |
last post by:
Hi!
I got a problem that's driving me crazy and I'm desperately in need of help.
I'll explain my scenario:
I'm doing a database driven site for a band, I got these tables for their discography section:
Discography
---------------------
DiscID
|
by: hakkatil |
last post by:
Hi to all,
I have a page that inserts excel sheet to access database. I am using asp. What I want to do is to check the inserting record if it is in the database. Basicly checking the dublicate record.
My code inserts records one by one using addnew-updatebatch. If there is a duplicate in the db, it will display "already exists" and if it is not in the db it will display "record added".
Below is my asp code I found on the net and...
|
by: cluce |
last post by:
I am trying to read a csv file with user info (username, password, email, address, city, zip, state, etc.) I am inserting the username, password, email into the aspnet_memberhsip table using the membership class and trying to insert the rest of the related info with a stored procedure into the custom table I created called aspnet_UserInfo using a one - one relationship by UserID but its not working. can someone look at my stored procedure for...
|
by: rando1000 |
last post by:
Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record.
So here's my method. I created two tables with the same structure as the table I'm inserting from. One table, Split_Temp, is the one I'll be inserting to. The other table, Split_Insert, contains the "Blank" record, which actually just has the word...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |