Create access database if it does not already exist?

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

PeterdeBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeterdeBAuthor Commented:
Ah I should have gone thorugh my own database on EE's, I asked a similara question a while ago oeps ;)
0
PeterdeBAuthor Commented:
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
2266180Commented:
in this case, do you still need help with this? if so, what?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

PeterdeBAuthor Commented:
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
PeterdeBAuthor Commented:

// 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
2266180Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterdeBAuthor Commented:
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
2266180Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.