Board index » cppbuilder » MS Access without ODBC

MS Access without ODBC


2005-06-15 11:24:13 PM
cppbuilder72
Hi I am using a ODBC connection to a MS Access Database. Using a piece of
code like this to store more than 100.000 posts - I know that MS Access is
not the best solution, but it is my task to do so:-( ..
..
OobjectTable = new TTable(this);
OobjectTable->DatabaseName ="Broker"; // The ODBC name
OobjectTable->TableName = "Oobjekter";
OobjectTable->Active = false;
OobjectTable->EmptyTable();
OobjectTable->CachedUpdates = true;
OobjectTable->Active = true;
..
..
OobjectTable->Append();
OobjectTable->Fields->Fields[0]->AsInteger = KeyID;
KeyID++;
OobjectTable->Fields->Fields[1]->AsInteger = DataToBeStored;
..
..
OobjectTable->ApplyUpdates();
..
..
This solution is slooooooow. Can I store data in an Access Database
directly - without using the ODBC interface?
The alternative is to write data to *.txt files, and import these into MS
Access, this is fast, but not very userfriedly.
Any sugestions?
/mikkel
 
 

Re:MS Access without ODBC

Jens Jensen wrote:
Quote
Hi I am using a ODBC connection to a MS Access Database. Using a piece of
code like this to store more than 100.000 posts - I know that MS Access is
not the best solution, but it is my task to do so:-( ..

I use MSAccess Databases all the time!
I admit they can appear slow.. but I have found that alot of my 'slow'
complaints were due to how I was implementing my usage of the database.
After I cleaned that up, things went extremely fast.
There are a few hints in Google.. but here is basically what I remember:
TTable is an extremely slow way to interface with a table (Especially if
you are adding records!) The reason, if I remember correctly, is
because the TTable component has to load the whole table each time it
does anything (that process is long!)
A better way is to use a TQuery object.. and I recommend the ADO stuff
(TADOQuery).. Now, to use the TQuery object, you have to get good with SQL.
Use SQL appends to add data:
ADOSQLObject->Active=false;
ADOSQLObject->SQL->Text = "Insert into [tablename]
(field1,field2,...) VALUES (value1,value2,...)";
ADOSQLObject->ExecSQL();
Use SQL queries to inquire about specific data
ADOSQLObject->Active=false;
ADOSQLObject->SQL->Text = "Select [whatever fields] from
[whatevertable] where [this that and the otherthing match what you want]";
ADOSQLObject->Active = true;
You will find that the SQL Appends are MUCH MUCH MUCH faster than
TTable->Append operations!
HTHs
 

Re:MS Access without ODBC

Hi Daniel, thanks a lot.
/mikkel
"Daniel Mayo" < XXXX@XXXXX.COM >skrev i en meddelelse
Quote
Jens Jensen wrote:
>Hi I am using a ODBC connection to a MS Access Database. Using a piece
of
>code like this to store more than 100.000 posts - I know that MS Access
is
>not the best solution, but it is my task to do so:-( ..
>

I use MSAccess Databases all the time!
I admit they can appear slow.. but I have found that alot of my 'slow'
complaints were due to how I was implementing my usage of the database.
After I cleaned that up, things went extremely fast.

There are a few hints in Google.. but here is basically what I remember:

TTable is an extremely slow way to interface with a table (Especially if
you are adding records!) The reason, if I remember correctly, is
because the TTable component has to load the whole table each time it
does anything (that process is long!)

A better way is to use a TQuery object.. and I recommend the ADO stuff
(TADOQuery).. Now, to use the TQuery object, you have to get good with
SQL.

Use SQL appends to add data:

ADOSQLObject->Active=false;
ADOSQLObject->SQL->Text = "Insert into [tablename]
(field1,field2,...) VALUES (value1,value2,...)";
ADOSQLObject->ExecSQL();

Use SQL queries to inquire about specific data

ADOSQLObject->Active=false;
ADOSQLObject->SQL->Text = "Select [whatever fields] from
[whatevertable] where [this that and the otherthing match what you want]";
ADOSQLObject->Active = true;

You will find that the SQL Appends are MUCH MUCH MUCH faster than
TTable->Append operations!

HTHs
 

{smallsort}