Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create access database if it does not already exist?

Posted on 2008-11-10
8
Medium Priority
?
232 Views
Last Modified: 2012-05-05
Hi Folks,

I'm using ADO and MSACCESS and would like my application to create a database if it doesnt already exist. Currently I use this in my formcreate event to prevent my application from crashing. Now is it possible to have it create a new database.mdb if it doesnt find one?

If you need more info please ask.

Kind regards,

Peter


if FileExists('c:\db\database.mdb')
  then begin
  connMain.Close;
  connMain.ConnectionString := 'FILE NAME=' + ChangeFileExt(Application.ExeName, '.udl');
  connMain.Open;
  tblKlanten.Open;
end
else
// blahdieblahdieblah

Open in new window

0
Comment
Question by:PeterdeB
  • 5
  • 3
8 Comments
 

Author Comment

by:PeterdeB
ID: 22927268
Ah I should have gone thorugh my own database on EE's, I asked a similara question a while ago oeps ;)
0
 

Author Comment

by:PeterdeB
ID: 22927388
I attached the database I need tocreate and also attached a code snippet which takes care of that, partially that is ;)


procedure TForm1.btnNewDatabaseClick(Sender: TObject);
var
 DataSource : string;
 dbName     : string;
begin
 dbName:='c:\db\database.mdb';
 
 DataSource :=
    'Provider=Microsoft.Jet.OLEDB.4.0' +
    ';Data Source=' + dbName +
    ';Jet OLEDB:Engine Type=4';
 
  ADOXCatalog.Create1(DataSource);
 
 
end;
 
procedure TForm1.btnAddTablesClick(Sender: TObject);
var
  DataSource : string;
  cs         : string;
begin
 DataSource :=
     'Provider=Microsoft.Jet.OLEDB.4.0'+
     ';Data Source=c:\db\database.mdb'+
     ';Persist Security Info=False';
 
 Con1.ConnectionString := DataSource;
 Con1.LoginPrompt := False;
 cmd1.Connection := Con1;
 
  cs:='CREATE TABLE Klanten ('+
     '  KlantID TEXT(50),'+ // this should be the primary //key         autonumber and autoincrement
     '  Nummer NUMBER,'+
     '  Datum DATETIME,'+
     '  Naam TEXT(50),'+
     '  Adres TEXT(50),'+
     '  Postcode TEXT(50),'+
     '  Woonplaats TEXT(50),'+
     '  GebDatum DATETIME,'+
     '  Telefoon TEXT(50),'+
     '  Mobiel TEXT(50),'+
     '  Email TEXT(50),'+
     '  Opmerkingen MEMO)';
 
 cmd1.CommandText := cs;
 cmd1.Execute;
 
 
end;
 
// 

Open in new window

database.mdb
0
 
LVL 28

Expert Comment

by:2266180
ID: 22928663
in this case, do you still need help with this? if so, what?
0
Industry Leaders: 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!

 

Author Comment

by:PeterdeB
ID: 22929135
Hi ciuly, yes I still need help to create  the database I attached. This part:

cs:='CREATE TABLE Klanten ('+
     '  KlantID TEXT(50),'+ // this should be the primary //key         autonumber and autoincrement
     '  Nummer NUMBER,'+
     '  Datum DATETIME,'+
     '  Naam TEXT(50),'+
     '  Adres TEXT(50),'+
     '  Postcode TEXT(50),'+
     '  Woonplaats TEXT(50),'+
     '  GebDatum DATETIME,'+
     '  Telefoon TEXT(50),'+
     '  Mobiel TEXT(50),'+
     '  Email TEXT(50),'+
     '  Opmerkingen MEMO)';

will not result in the database I need. It does create a database but when I run my app it complaints about typemismatch starting the first field klantid. I'm working on it but cannot find much documentation about it thats why I attached the database.mdb, because that is the database I need for my application.

Back then I used a sample application and modified its database in access to get the database I needed for my application. Therefore I never had to create one myself from scratch as Im trying with the code above.

Peter
0
 

Author Comment

by:PeterdeB
ID: 22929244

// build new table
 
  cs:='CREATE TABLE Klanten (KlantID AUTOINC)';
  cmd1.commandtext := cs;
  cmd1.Execute;
 
  // add index, primary option designates index as primary key
 
  cs:='CREATE INDEX idxPrimary '+
     'ON Klanten (KlantID) WITH PRIMARY';
 Cmd1.CommandText := cs;
 cmd1.Execute;
 
 // add fields and field defintions here?
 
     '  KlantID AUTOINC,'+
     '  Nummer NUMBER,'+
     '  Datum DATETIME,'+
     '  Naam TEXT(50),'+
     '  Adres TEXT(50),'+
     '  Postcode TEXT(50),'+
     '  Woonplaats TEXT(50),'+
     '  GebDatum DATETIME,'+
     '  Telefoon TEXT(50),'+
     '  Mobiel TEXT(50),'+
     '  Email TEXT(50),'+
     '  Opmerkingen MEMO)';
 
 cmd1.CommandText := cs;
 cmd1.Execute;

Open in new window

0
 
LVL 28

Accepted Solution

by:
2266180 earned 2000 total points
ID: 22939680
the sql string is (written directly in browser, so not tested, but should work)

I didn't find any way of programatically creting teh input masks.
so, as last resort, you can save this database in empty form (should be a few KB only) and save it as a resource. then, when you want to create the database, just extarct it from the resource and save it to file. this workaround will work in all cases, but it will make your exe size grow with maybe 30-50 KB depending on what's in the mdb file.
  cs:='CREATE TABLE Klanten ('
         'KlantID AUTONUMBER NOT NULL,'+
         'Nummer INTEGER NOT NULL,'+
         'Datum DATETIME NOT NULL,'+
         'Naam TEXT(50),'+
         'Adres TEXT(50),'+
         'Postcode TEXT(50),'+
         'Woonplaats TEXT(50),'+
         'GebDatum DATETIME,'+
         'Telefoon TEXT(12),'+
         'Mobiel TEXT(12),'+
         'Email TEXT(30),'+
         'Opmerkingen MEMO'+
      ')';
  cmd1.commandtext := cs;
  cmd1.Execute;

Open in new window

0
 

Author Comment

by:PeterdeB
ID: 22948117
Hi ciuly, tnx for your replies. I can't get it to work, the database I create with your code crashes my application and several attempts to solve that were unsuccesful. However, I'm currently using an empty database as you suggested so based on your idea, now it works.

Thanks,

Peter
0
 
LVL 28

Expert Comment

by:2266180
ID: 22948316
crashes are as expected since I didn't create any of the indexes (figured you'll do that by default :P ) plus the input masks were missing and maybe some other proeprties of the fields. pretty weird that MS didn't think about this when they designed the sql query for creating a table in an access DB. nothing on msdn about programatically creating input masks or any other way of setting all those properties a field can have. just another stupid thing from MS.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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