Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export to Access

Posted on 2001-08-28
14
Medium Priority
?
357 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

722 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