Solved

Bulk insert records using Diamond Access

Posted on 2008-06-15
17
1,063 Views
Last Modified: 2013-11-23
Hello,
I'm using Diamond Access in Delphi 7 to read/write a Microsoft Access (2003 format).
I want to bulk insert many records at once so I need a solution what is the fastest way to add 1000 records into the MDB file using Diamond Access components.
0
Comment
Question by:WebWolf1
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 18

Expert Comment

by:Johnjces
Comment Utility
Diamond Access if I recall is pretty much another wrapper for ADO.

Are you using Tables or Queries?

John
0
 

Author Comment

by:WebWolf1
Comment Utility
Hello,

is DAO not ADO. You should check better ;)
Im not using anything. This is what I want from you (experts). What to use and how.
0
 
LVL 18

Expert Comment

by:Johnjces
Comment Utility
No... DAO is different from ADO.

http://www.utteraccess.com/forums/printthread.php?Board=53&main=1241488&type=post

To help you, are you using Tables or Queries?
0
 
LVL 18

Expert Comment

by:Johnjces
Comment Utility
DAO was designed more specifically for Access and I was under the assumption form playing with the Diamond components that it was ADO.... but he makes a whole slew of different database components.
0
 

Author Comment

by:WebWolf1
Comment Utility
Diamond Access is a wrapper for DAO not for ADO.

http://www.islamov.com/

"Diamond Access is a set of Delphi components that provide high-speed performance when working with Microsoft Access databases. Diamond Access uses Data Access Objects 3.5/3.6 (DAO) to work directly with a Jet engine, providing the fastest possible interface to the Access databases."

I'm sorry but Im looking for an expert on this field.
0
 
LVL 18

Expert Comment

by:Johnjces
Comment Utility
I made a bad assumption having played with Diamond Access stuff years ago and thought it was ONLY ADO. He, Islamov, makes both ADO and DAO components and in years past purchased from him.

I think that is where I made a mistake but there is a big difference between ADO and DAO and I realize that and I know that.

I have used ADO and DAO extensively for MS Access db access and I happen to prefer ADO.

If you want help, please provide a bit more information.

Are you using tables or queries?

What data do you want to bulk import?

Is it from another Access table? Or from some other source?

"I'm sorry but Im looking for an expert on this field." ... this won't get you far!

John
0
 

Author Comment

by:WebWolf1
Comment Utility
Hello,

Im using TTable to show data in a grid.
But I want to massively insert 1000 tickets using a form.
Right now the form doesn't have any database component.
I'm waiting someone to help me how to insert 1000 tickets with the fastest way.
Is it going to be TTable? Is it going to be TQuery? I'm not sure which is the best way.

Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 84 total points
Comment Utility
for Access i dunno but for Oracle with TQuery

SQL.LoadFromFile('script.sql');

script.sql :

INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X1', 1);
INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X2', 1);
INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X3', 1);
INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X4', 1);
INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X5', 1);

and then
SQL.ExecSQL;

the statement delimiter is ;
dunno for diamond access


0
 

Author Comment

by:WebWolf1
Comment Utility
This will call 1000 INSERT queries into MS Access database = VERY SLOW design.

Btw, in ORACLE you can use multi insert statement like

INSERT INTO TICKETS (NAME, AMOUNT) VALUES ('X1', 1), ('X2', 1), ('X3', 1), ('X4', 1), ('X5', 1);

Weird but It looks that I'm more expert here :S
0
 
LVL 18

Assisted Solution

by:Johnjces
Johnjces earned 83 total points
Comment Utility
Hmmm.. Access aint Oracle!

Access is SLOW! Limited in a lot of ways. You can also do the same as you point out in MySQL

My unexpert opinion would be to do a for loop with ACCESS and insert the items in that fashion.

For x := 1 to 1000 do
 begin
   SQL.Close;
   SQL.Clear;
   SQL.Add('Insert Into Tickets(Name, Amount) Values(QuotedStr(MyValue), MyInteger)');
   SQL.Open;
 end;

Bummer you're not using MS SQL Server as you could use 'BULK INSERT'
0
 

Author Comment

by:WebWolf1
Comment Utility
Access = flat file and my project is a very small software which cannot carry MS or MySQL
0
 
LVL 18

Expert Comment

by:Johnjces
Comment Utility
"Access = flat file" hmm... no...

You may be using an Access db "sort of" as a flat file table but in any event there is no method of bulk copying or inserting stuff into an Access db. You can do better from the MS Access application if your data was in a flat file table or CSV etc and importing it. A CSV is a great example of a flat file.

Again, Access has its limits and was designed for just what you are doing, a small desktop application\, but allowed relationships and SQL through its database management system, JET.

Back to flat files:

Flat files are text files stored in the computer file system. Humans can read flat files very easily by using the operating system commands that display files, such as cat in Linux and Unix or even notepad in windows! You can access and edit these files by using any text file editor, such as Notepad or vi. The information in the flat file is stored as strings, and the java, vb, DELPHI, c, c++ or whatever program or PHP, asp, jsp or whatever server side script that retrieves the data needs to know how the data is stored. For example, to retrieve a customer name from a flat file, your application needs to know that the customer name is stored in the first 20 characters of every line.

See;

http://en.wikipedia.org/wiki/Flat_file_database

for further info flat files.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 83 total points
Comment Utility
Fastest ways:
* Get your program as close to the actual MS Access database as possible, running on the same PC if possible.  Minimize the network path between your program and the database (number of switches and routers).

* Use a stored query rather than dynamic SQL.  In this case, you will probably create a parameterized query to pass your program's values.  This eliminates the dynamic SQL parsing and syntax checking overhead.

* Reuse your query object within the loop, changing the values of the parameters.

* BeginTransaction before starting your loop and CommitTransaction after ending your loop.  This will likely be the best bang-for-the-buck action.  However, you are trading the caching of the updates with the memory to hold the updates.  This eliminates the automatic Commit that takes place at the completion of every SQL statement you execute.  Make sure you have enough RAM or you will start paging and slow all PC processing.

* Minimize the number of indexes on the target table.

NOTE: DAO is not a terrible performer compared to ADO, but it can do things that ADO can not.  Thus, there is some overhead for using DAO.

Question: How are you measuring your performance?

================================
Of course, you can measure the persistence of your 1000 records in different ways.  If you want to return control to the user as quickly as possible, there are other options available.  If you want to get the 1000 records into the database as quickly as possible, you might have different options to consider.  In any event, you are looking at more complicated solutions (multi-tiered, cached, etc.).

Before you consider the more complicated options, try the bulleted actions above and let us know how close you are to satisfactory performance.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
While you're at it, please post the type of data you are trying to insert. (column data types)

How many users are simultaneously using your application?

The good news is that Access2003 gives you more latitude in the SQL solutions you can use than Access97.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@WebWolf1

What's happening?  You haven't posted a comment in several days.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now