Solved

Export to Access

Posted on 2001-08-28
14
341 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:smurff
Comment Utility
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
Comment Utility
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
Comment Utility
smurff, how can i add data to my table as in Button1.Click?
0
 
LVL 6

Expert Comment

by:Jaymol
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:karen021897
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Where's Pegasus?
0
 

Author Comment

by:karen021897
Comment Utility
thanks for all answers...
0
 
LVL 3

Expert Comment

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

Expert Comment

by:Jaymol
Comment Utility
Dear Smurff,

Removing that zip file completely contravenes the rulings of EE.

Just joking.

John.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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 tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now