Export to Access

Hi all...

Is any routine or component that can export to Access database, build new database and write to it, using some export routines?

Who is Participating?
smurffConnect With a Mentor Commented:
Before you can have a "USES ADOX_TLB" clause, do the following:
Start Delphi 5
Import Type Library
Choose "Microsoft ADO Ext 2.1 for DDL and Security (Version 2.1)"
Change "TTable" to "TADOXTable"
Change "TColumn" to "TADOXColumn"
Change "TIndex" to "TADOXIndex"
Press Install button
Press OK once and Yes twice
File | Close All | Yes

ADOX: Creating new Access Database
With the help of ADOX (ADO Extensions for Data Definition and Security) we can create databases on the fly, using simple syntax (comparing with SQL DDL). All we need to do is to create an instance of a Catalog object, that is datastore in ADOX terms and use its Create method to create a new database. Next, we add a table to the Tables collection and required number of Columns (i.e. fields) into the Table. The following example shows how to create a new Access database and a table and two columns into it. It should be mentioned that the current version of ADOX works properly only with Jet OLE DB Provider - other providers have some limitations of functionality. This should be fixed in ADO 2.5 - the version that will come with Windows 2000.

Note: Add ADOX_TLB and ComObj units into USES clause.

procedure TForm1.Button1Click(Sender: TObject);
 Catalog  : _Catalog;
 Table    : _Table;
 BaseName : String;
 DS       : String;
// Name of the new database file
 BaseName := 'd:\data\demo.mdb';
// Create a Catalog Object
 Catalog := CreateCOMObject(StringToGUID('ADOX.Catalog')) as _Catalog;
// Set the Connection String
 DS := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+BaseName;
// Check if we already have such a file and delete it
 If FileExists(BaseName) Then DeleteFile(BaseName);
// Create new Access database
// Create a Table Object
 Table := CreateCOMObject(StringToGUID('ADOX.Table')) as _Table;
// Set the name of a table
 Table.Name := 'Customers';
// Append Table into the base
// Now add two columns (fields) into the table
// Both are Text fields up to 128 characters
 Table.Columns.Append('FIRSTNAME', adVarWChar, 128);
 Table.Columns.Append('LASTNAME', adVarWChar, 128);

Sometimes we need to build an ADO application without using delphi ADO components. To do that you have to use the Delphi 5 Enterprise. For these cases we made a CookBook to do that:

1) Create a database on MSAcess with one table named clients;
2) Insert four fields in your client table;
3) In the Control Panel, choose the ODBC Data Sources and create a System DSN named Adotest;
4) open Delphi;
5) On a new project, insert a Tbutton and a Tmemo in your form;
6) Insert the ?ComObj? in the uses;
7) In the Onclick event insert this code:

procedure TForm1.Button1Click(Sender: TObject);
  RecordSet: OleVariant;
  temp: string;
  DSN: string;
  SelectString : string;
  i : integer;

  // ODBC dsn from ACESS database?.
  DSN := 'dsn=AdoTest';

  // the SQL string?
  SelectString := 'SELECT * FROM clientes';

  // Create an empty recordset object
  RecordSet := CreateOleObject('ADODB.Recordset');

  // Fill the recordset
  RecordSet.Open(SELECTSTRING, DSN);

  // Display the data
     // the client table has four fields
     // this loop will put one record per line in the memo
     for i:= 0 to 3 do
       temp := temp + ' ' + RecordSet.Fields[i].Value;
     temp := '';
  until RecordSet.EOF;



  Sorry, but I've not seen any components out there that would do this (I looked quite a bit about a month ago).

Not to worry, all is not lost... You *could* create an ODBC link to the Access DB, connect to it and create all your needed tables with the CREATE TABLE SQL statement, etc.

In addition, you could open an ODBC link from your legacy data (using a TDatabase component) and then another to the Access Db to export your data but you may find that simply going into access and importing it via a table link might be better.

Hope this helps and good luck with your project!
Pegasus : Please see the official EE guidelines before propsing any more answers.

Karen : This link is not 100% what you want, but it will most definately help....


You cannot create an ODBC link to a database that does not exist.  Therefore the previous comment from Pegasus is irrelevant to your question as you want to create the DB yourself.  The link I gave you assumes that the DB is already created, but simply change the line....

          db := access.OpenDatabase(yourDatabaseName);


          db := access.CreateDatabase(yourDatabaseName);

....and you should be done.

Hope the link helps.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

theres quite a good series on access n delphi at www.about.delphi.com

Depending on what you want to export, connecting to an access db is quite easy.
Another example is at http://www.planet-source-code.com/xq/ASP/txtCodeId.229/lngWId.7/qx/vb/scripts/ShowCode.htm

heres a basic example of using ADO with Access:
ADO and Access Databases:

To use ADO for accessing Access databases do the followings:

- Drop ADOConnection from ADO page
- On ConnectionString property of the ADOConnection component, click (...) button and click Build.
- Select Microsoft Jet 4.0 OLE DB Provider
- Click on Connection tab and select your Access database file.
- Turn LoginPrompt to False if you write the login name and password in your connection
- Drop ADOTable or ADOQuery
- In ADOTable or ADOQuery Connection property select Connection1
- ADOTable and ADOQuery are datasets so that they can treated as any normal BDE TTable and TQuery. You
can drop a DataSource and any data controls to be linked with your Access table.

btw Pegasus, i think its an insult to try and claim points for that answer.
karen021897Author Commented:
smurff, how can i add data to my table as in Button1.Click?

Simply change the line...

        SelectString := 'SELECT * FROM clientes';

to something suitable like...

        SelectString := 'INSERT INTO clientes VALUES('blah', 'blah', 'blah');'

and bob's your uncle.  (Figuratively speaking.)


PS.  Not trying to steal Smurff's points.  He earned and deserves them fully.
karen021897Author Commented:
Can i not access the table using TADOConnection and put data in there using ADO components in Delphi 6?

That's how I'd do it, with the SQL statement, but I just prefer using SQL.  I think you'd be better waiting for Smurff to answer your question.


Theres a small example here http://www.it-informer.com/ADOMSAccess.zip

I havent had much time to play with it but you will have to change the connection string in the object inspector and change Active to True;
Where's Pegasus?
karen021897Author Commented:
thanks for all answers...
thx hope it helped, can I remove the example now?
Dear Smurff,

Removing that zip file completely contravenes the rulings of EE.

Just joking.

All Courses

From novice to tech pro — start learning today.