Connecting to data stores
How do I connect to a MS Access 2000 database?
ADOConnection.ConnectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyDatabase.mdb;Persist Security Info=False’;
How do I connect to a password protected MS Access 2000 database?
ADOConnection.ConnectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=XXXXXX;DataSource=C:MyDatabase.mdb;Persist Security Info=False’;
What provider should I use for MS Access
For MS Access 97 use Microsoft.Jet.OLEDB.3.51
For MS Access 2000 use Microsoft.Jet.OLEDB.4.0
How do I connect to a dBase database?
ADOConnection.ConnectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyDatabase.mdb;Extended Properties="dBase 5.0;"’;
How do I connect to a Paradox database?
ADOConnection.ConnectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyDatabase.mdb;Extended Properties="Paradox 7.X;"’;
How do I connect to a MS Access database on a CD (read only) drive?
ADOConnection.Mode := cmShareExclusive;
Data retrieving and manipulation
How do I use multiword table / field names (spaces in Table or Field name)?
Enclose multiword names in [ ] brackets:
ADOQuery1.SQL.Text := ‘SELECT [Last Name], [First Name] FROM [Address Book]’;
How do I use constant fields in an SQL query?
ADOQuery1.SQL.Text := ‘SELECT ”2002”, [First Name], Salary FROM Employess’;
How do I delete all records in a table?
ADOQuery1.SQL.Text := ‘DELETE * FROM TableName’;
Why do I keep getting a "-1" for the RecordCount property
If you need the RecordCount to be correct, set the CursorType to something other than ctOpenForwardOnly.
I’m using AutoNumber for Primary Key field to make every record unique. If I want to read or Edit some ADOTable record after one was appended (and Post-ed) I get en error: "The specified row could not be located for updating. Some values may have been changed since it was last read". Why?
After every new record you should use:
var bok: TBookmarkStr;
begin
bok:=adotable1.Bookmark;
adotable1.Requery();
adotable1.Bookmark:=bok;
end;
How do I create a disconnected ADO recordset? I want to run a query, pick the data and delete some records but not physically.
In order to create a disconnected ADO recordset, you must first set the ADODataSets CursorLocation property to "clUseClient". Then open the Recordset. Then set the ADODatasets Connection to Nil. Do not close the ADODataset.
How do I retrieve a system information, for example, list of tables, fields (columns), indexes from the database?
TADOConnection object has an OpenSchema method that retrieves system information like list of tables, list of columns, list of data types and so on. The following example shows how to fill an ADODataSet (DS) with a list of all indexes on a table (TableName):
var DS:TADODataSet;
…
ADOConnection.OpenSchema(siIndexes, VarArrayOf([Unassigned, Unassigned, Unassigned, Unassigned, TableName]), EmptyParam, DS);
How can I improve the performance of my Ado application (like speed up query data retrieval)?
. Avoid returning too many fields. ADO performance suffers as a larger number of fields are returned. For example using "SELECT * FROM TableName" when TableName has 40 fields, and you really need only 2 or 3 fields
. Choose your cursor location, cursor type, and lock type with care. There is no single cursor type you should always use. Your choice of cursor type would depend on the functionality you want like updatability, cursor membership, visibility and scrollability. Opening a keyset cursor may take time for building the key information if you have a lot of rows in the table whereas opening a dynamic cursor is much faster.
. Release your dynamically created ADO objects ASAP.
. Check your SQL expression: when joining tables with Where t1.f1 = t2.f1 and t2.f2 = t2.f2 it is important that f1 and f2 as fields are set to be indexed.