I use two procedures, 1 to create tables if they don't exist
(CreateTables), and the second to make the Connection (in FormShow). I
also use an ADO-Component for the connection, and ADOXCatalog (you will
have to import that one) for creating the datasource (uses ADOX_TLB).
See both procedures.
procedure TMainForm.CreateTables;
var
cCommand: String;
begin
// Create Tables
With MainDataModule do
begin
cCommand := 'Create table MainGroups (MainGroupID integer,
Description Text(25), CatImage Text(150))';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create table SubGroups (SubGroupID Integer not null,
MainGroupID integer not null, Description Text(25), CatImage
Text(150))';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create table SubSubGroups (SubSubGroupID Integer,
SubGroupID integer not null, Description Text(25), CatImage Text(150),
WebShopCategorieID Integer)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create table Articles (ArticleID Text(9), RefNr
Text(14),' +
'SellingPrice Money, ArticleNr Text
(14), ProductName Text (64),' +
'Description Memo, OnStock Integer,
ManufacturerID Integer,' +
'ImageBlob OleObject, ThumbNailBlob
OleObject, Discount YesNo)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create table ArticlesInGroups (ArticleInGroupID
integer, ArticleID Text (9), SubSubGroupID integer, Date Date)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create table Manufacturers (ManufacturerID integer,
Manufacturer Text(32))';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
// Create Indexes
cCommand := 'Create Index idxPrimary on MainGroups (MainGroupID)
with Primary';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxPrimary on SubGroups (SubGroupID) with
Primary';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxSubGroupID on SubGroups (SubGroupID,
MainGroupID)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxPrimary on SubSubGroups (SubSubGroupID)
with Primary';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxSubGroupID on SubSubGroups
(SubSubGroupID, SubGroupID)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxPrimary on Articles (ArticleID) with
Primary';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxPrimary on ArticlesInGroups
(ArticleInGroupID)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxArticleID on ArticlesInGroups
(ArticleID)';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxArticles on ArticlesInGroups
(ArticleID, SubSubGroupID) with Primary ';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Create Index idxManufacturerID on Manufacturers
(ManufacturerID) with Primary ';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
// Create Constraints
cCommand := 'Alter table SubGroups add constraint MainGroupID
Foreign Key (MainGroupID) references MainGroups (MainGroupID) on Delete
cascade';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Alter table SubSubGroups add constraint SubGroupID
Foreign Key (SubGroupID) references SubGroups (SubGroupID) on Delete
cascade';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Alter table ArticlesInGroups add constraint
SubSubGroupID Foreign Key (SubSubGroupID) references SubSubGroups
(SubSubGroupID) on Delete cascade';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
cCommand := 'Alter table Articles add constraint ManufacturerID
Foreign Key (ManufacturerID) references Manufacturers(ManufacturerID) on
Delete cascade';
UpdateAccessADOCommand.CommandText := cCommand;
UpdateAccessADOCommand.Execute;
end;
end;
procedure TMainForm.FormShow(Sender: TObject);
var
cDbName, cDataSource: String;
bCreateTables: Boolean;
begin
cDbName := ChangeFileExt(Application.ExeName, '.mdb');
bCreateTables := not FileExists(cDbName);
with MainDataModule do
begin
if bCreateTables then
begin
cDataSource := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source=' + cDbName + ';' +
'Jet OLEDB:Engine Type=4';
AccessADOXCatalog.Create1(cDataSource);
end;
cDataSource := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source=' + cDbName + ';' +
'Persist Security Info=False';
AccessADOConnection.ConnectionString := cDataSource;
AccessADOConnection.LoginPrompt := False;
UpdateAccessADOCommand.Connection := AccessADOConnection;
end;
if bCreateTables then
CreateTables;
SetConnections;
LoadGrids;
OpenQueries(5);
OpenQueries(0);
OpenQueries(3);
OpenQueries(4);
end;
-----Oorspronkelijk bericht-----
Van: delphi-db-boun...@elists.org [mailto:delphi-db-boun...@elists.org]
Namens Bill Krause
Verzonden: donderdag 21 juli 2011 17:05
Aan: 'Moderated discussion list about database programming with
Borland'sDelphi'
Onderwerp: RE: How to connect to a MS Access Database
Delphi 5.1 needs a third party component. I used ADO database
connection.
There are several but do not know if still available.
The About Delphi has a tutorial on how to connect to Access.
Bill Krause
_______________________________________________
Delphi-DB mailing list
Delphi-DB@elists.org
http://lists.elists.org/cgi-bin/mailman/listinfo/delphi-db
Paul van Mil
Senior Developer
MAD-Systems
Burg. van Suchtelenstr. 26
6641 XN Beuningen (Gld)
The Netherlands
Url: www.madsystems.nl
Tel.: +31 (0)24-366 1501
Fax.: +31 (0)24-366 1502
Mob.: +31 (0)6 2251 4084
Email: p...@madsystems.nl
*************************************************************************
Disclaimer: MAD-Systems is de enige opdrachtnemer van alle werkzaamheden.
Nederlands recht is van toepassing. De aansprakelijkheid van MAD-Systems is
beperkt tot de uitkering die plaatsvindt onder de verzekering van MAD-Systems
tegen beroepsaansprakelijkheid.
Disclaimer: MAD-Systems is the exclusive contractual party with respect to all
work and services undertaken.
Dutch laws shall apply. The aggregate liability of MAD-Systems shall be limited
to the proceeds available under MAD-Systems professional liability insurance.
This e-mail message is intended exclusively for the addressee(s). If the e-mail
was sent to you by mistake, would you please contact us immediately by e-mail
through "i...@madsystems.nl".
In that case, we also request that you destroy the e-mail and that you neither
use the contents nor disclose them in any manner to third parties, because the
message may contain confidential information which is protected by professional
secrecy.
Any addressee should be aware that internet e-mail is subject to risks;
MAD-Systems therefore denies any responsibility for damages resulting from the
use of Internet e-mail.
*************************************************************************
This email is checked on viruses through Norton Antivirus, updated
28-07-2011
_______________________________________________
Delphi-DB mailing list
Delphi-DB@elists.org
http://lists.elists.org/cgi-bin/mailman/listinfo/delphi-db
