?
Solved

Adding multiple rows to an mdb database using OleDb

Posted on 2009-04-14
10
Medium Priority
?
323 Views
Last Modified: 2012-05-06
I have a simple mdb database with a simple table and i have a couple of thousand rows to insert into this table.

If i add them one by one its slow - is there any way to batch them together? In sql you just seperate them with carriage returns but that doesnt seem to work. Can someone give me an example?

0
Comment
Question by:tfsln
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 13

Expert Comment

by:newyuppie
ID: 24143422
Just to clarify... Where is the data that you want to insert to the database? A plain text file? Excel? Another MDB?

NY
0
 

Author Comment

by:tfsln
ID: 24143475
It comes from a web service. But that shouldnt matter should it? The question is quite specific... I already have the data and i have built a number of insert statements like this;

Insert into testtable(test1,test2) Values('test1','test2')

What i want to know is the fastest way of committing those inserts to the database. Im guessing that running them in a batch would be quicker than running them one by one
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24143551
Based on what you have said I would only expect there to be one insert query.  Why do you need more than one?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:tfsln
ID: 24143557
I gave an example of an insert query i am peforming. I have thousands of those inserts that i specified to perform all at once.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24143595
I have the same Q as NY then.

"Just to clarify... Where is the data that you want to insert to the database? A plain text file? Excel? Another MDB?"
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 24143604
0
 

Author Comment

by:tfsln
ID: 24143647
I dont understand why it is so important to know where my data is coming from. It is not relevant. My question is specfic;

Given a list of transact-sql INSERT statements (the data i used to generate those statemets is not relevant to this question), how can i send that list to access for inserting - as opposed to running the insert one at a time?

In SQL server all you need to do is have a carriage return seperating each INSERT statement but that doesnt work for access and i suspect its a syntax thing.
0
 

Author Comment

by:tfsln
ID: 24143655
newyupie: I cant make any sense of that link... It appears to me that all he is just doing it the long way by adding parameters using OleDbCommand when he could have just specified them in the Values clause and executed it straight away
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 24143678
"how can i send that list to access for inserting - as opposed to running the insert one at a time?"

You can't.
Access/Jet SQL only processes one command at a time. You cannot combine commands into one sql script.
0
 

Author Comment

by:tfsln
ID: 24143681
Ok cool, thats all i needed to know - cheers
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question