Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Export to Access

Posted on 2001-08-28
14
Medium Priority
?
360 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 400 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

Technology Partners: 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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline
Suggested Courses

916 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