Solved

Export to Access

Posted on 2001-08-28
14
347 Views
Last Modified: 2010-04-06
Hi all...

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

Karen
0
Comment
Question by:karen021897
[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
  • 5
  • 3
  • +1
14 Comments
 
LVL 3

Expert Comment

by:Pegasus100397
ID: 6432414
Karen,

  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
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6435654
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....

          http://www.scalabium.com/faq/dct0111.htm

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);

....to....

          db := access.CreateDatabase(yourDatabaseName);

....and you should be done.

Hope the link helps.

John.
0
 
LVL 3

Expert Comment

by:smurff
ID: 6435733
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.

regards
Smurff
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:smurff
ID: 6435743
btw Pegasus, i think its an insult to try and claim points for that answer.
0
 
LVL 3

Accepted Solution

by:
smurff earned 100 total points
ID: 6435827
Before you can have a "USES ADOX_TLB" clause, do the following:
Start Delphi 5
Project
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);
var
 Catalog  : _Catalog;
 Table    : _Table;
 BaseName : String;
 DS       : String;
begin
// 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
 Catalog.Create(DS);
// 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
 Catalog.Tables.Append(Table);
// 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);
end;
 



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);
var
  RecordSet: OleVariant;
  temp: string;
  DSN: string;
  SelectString : string;
  i : integer;
begin

  // 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
  repeat
     // 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;
     Memo1.Lines.Add(temp);
     RecordSet.MoveNext;
     temp := '';
  until RecordSet.EOF;

end;


regards
Smurff
0
 

Author Comment

by:karen021897
ID: 6436590
smurff, how can i add data to my table as in Button1.Click?
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6436630
Karen,

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.)

John.

PS.  Not trying to steal Smurff's points.  He earned and deserves them fully.
0
 

Author Comment

by:karen021897
ID: 6436714
Can i not access the table using TADOConnection and put data in there using ADO components in Delphi 6?
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6436738
Karen,

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.

John.
0
 
LVL 3

Expert Comment

by:smurff
ID: 6439641
Karen

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;
regards
Smurff
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6439654
Where's Pegasus?
0
 

Author Comment

by:karen021897
ID: 6450100
thanks for all answers...
0
 
LVL 3

Expert Comment

by:smurff
ID: 6450153
thx hope it helped, can I remove the example now?
regards
Smurff
0
 
LVL 6

Expert Comment

by:Jaymol
ID: 6450180
Dear Smurff,

Removing that zip file completely contravenes the rulings of EE.

Just joking.

John.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10 Round Image 2 226
Firemonkey allowing RTL on android 6 77
migrate this code to work on android 1 45
Firemonkey Tcameracomponent resultion 1 47
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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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