[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Check if database exists

Posted on 2008-01-25
32
Medium Priority
?
277 Views
Last Modified: 2013-11-23
What would be the best practice to check if my Access database exists in my app. folder,
if exists then connected=true if not display msg and close the application?
How would the code look like if I used access 2007 ?Since the only way to conect seeems to be to supply full database path in the connection string...Thank's..
0
Comment
Question by:Bosanac
  • 16
  • 9
  • 7
32 Comments
 
LVL 21

Assisted Solution

by:ziolko
ziolko earned 100 total points
ID: 20745899
if you have path to database file firstly use FileExists() if it returns true then you can try to conenct to it

pseudo code:

if FileExists() then
  try
    .Connected := True;
  except
    on E: Exception do begin
      ShowMessage(Format('unable to connect to database (%s). application will now terminate', [E.Message]));
      Application.Terminate;
    end;
  end;
else begin
   ShowMessage('unable to locate database. application will now terminate');
   Application.Terminate;
end;
 
ziolko.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20746134
I would solve it like this:

var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.mdb'; {Access 2003}
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
ADOConnection.ConnectionString='Data Source='+sDatabasePath;
ADOConnection.Connected=True;
end;
if (FileExists(sDatabasePath)=false) then begin
ShowMessage ('Database does not exist, program will now terminate!');
Abort;
end;
end;
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20746179
Change
ADOConnection.ConnectionString='Data Source='+sDatabasePath;
ADOConnection.Connected=True;

With
ADOConnection.ConnectionString:='Data Source='+sDatabasePath;
ADOConnection.Connected:=True;

I was working in Visual Basic too much lately, syntax error. :)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 21

Expert Comment

by:ziolko
ID: 20746225
FactorB's code will work if database file is in same folder as your .exe

@Factorb
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';

this can be done simpler way:
sApplicationPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName));

also
if (FileExists(sDatabasePath)=true) then begin
can be simply
if (FileExists(sDatabasePath)) then begin

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20746252
also connectin string for ADO should be like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path_to_access_db;Persist Security Info=False

ziolko.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20746313
Simple sting like
Data Source=path_to_access_db
will work with Access 2003 if LoginPrompt is set to false,
but I have never tried connecting Officce 2007 Database.accdb
and I am not sure if above will work for it.
If not, maybe some driver should be specified in the connection string?

Regards
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20746337
>>If not, maybe some driver should be specified in the connection string?
that's driver specification:
Provider=Microsoft.Jet.OLEDB.4.0;

ziolko.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20746409
I know that,
but try using it for Access 2007 .accdb and you will get the message
Error
Unrecognized database format 'C:\Database.accdb'
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20746504
really? didn't know that, haven't worked with access for some time

ziolko
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20746633
You should change the provider, this will work:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database.accdb;Persist Security Info=False

Regards
FactorB
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20748901
Bosanac you can combine above conversation and put couple if ... thens and you will get functional code that will work with both types of databases, Access 2003 and below (.mdb) and Acces 2007 (.accdb) and make your executable more safe by wrapping ADOConnection.Connect in try ... except.

var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb'; {Access 2007, use sDatabaseName:='Database.mdb'; for Access 2003 and below}
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
{or you can replace above two lines with Ziolko's sApplicationPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName));}
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection.ConnectionString:='Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2007}
if UpperCase(RightStr(sDatabasePath,4)) = UpperCase('.mdb') then ADOConnection.ConnectionString:='Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2003 and below}
try
ADOConnection.Connected:=True;
except
ShowMessage ('There was a problem connecting to the database, program will now terminate!');
Abort;
end;
end;
if (FileExists(sDatabasePath)=false) then begin
ShowMessage ('Database does not exist, program will now terminate!');
Abort;
end;
end;
0
 

Author Comment

by:Bosanac
ID: 20749088
[Error] Unit1.pas(110): Undeclared identifier: 'RightStr'
0
 

Author Comment

by:Bosanac
ID: 20749109
This is what I was trying to compile :

procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb';
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then
ADOConnection1.ConnectionString:='Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False';
try
ADOConnection1.Connected:=True;
except
on E: Exception do begin
      ShowMessage(Format('unable to connect to database (%s). application will now terminate', [E.Message]));
      Application.Terminate;
    end;
  end;
else begin
   ShowMessage('unable to locate database. application will now terminate');
   Application.Terminate;
end;

0
 
LVL 21

Expert Comment

by:ziolko
ID: 20749117
probably you need to add StrUtils to your uses clausule

ziolko.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749123
Try to declare it with other functions

function RightStr(const AString: AnsiString; const Count: Integer): AnsiString; overload;
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749129
I think you are right about StrUtils
0
 

Author Comment

by:Bosanac
ID: 20749221
I get error : eole exception authentication failed !
tried this:
procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb';
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then
ADOConnection1.ConnectionString:='Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False';
try
ADOConnection1.Connected:=True;
ADOTable1.Active:=True;
except
on E: Exception do begin
      ShowMessage(Format('unable to connect to database (%s). application will now terminate', [E.Message]));
      Application.Terminate;
    end;
     end;
     end;
       end;
0
 

Author Comment

by:Bosanac
ID: 20749225
also when i rename the DB dont get no error msg...?!
0
 

Author Comment

by:Bosanac
ID: 20749232
elso get error : could not find installable ISAM...
0
 

Author Comment

by:Bosanac
ID: 20749261
I can get it to work on Access 2003 :

procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.mdb'; {Access 2003}
sApplicationPath := IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName));
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath))
then begin
ADOConnection1.ConnectionString :='Data Source='+sDatabasePath;
ADOConnection1.Connected :=True;
ADOTable1.Open;
end;
if (FileExists(sDatabasePath)=false) then begin
ShowMessage ('Database does not exist, program will now terminate!');
Application.Terminate;
end;
end;

but not on Access 2007 ...
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749327
Eole exception authentication failed : Is your database password protected? If it is you need to change ConnectionString and include authentication or manually remove protection from the database.

Could not find installable ISAM: Do you have Access 2007 installed. Driver comes by default with it. If not, try installing/reinstalling Office 2007 ot reinstalling ISAM (indexed sequential access method) driver.
see: http://support.microsoft.com/default.aspx/kb/283881 
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749342
You are missing a driver, this will solve your problem

2007 Office System Driver: Data Connectivity Components

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Regards
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749370
I think I found the error. To connect to Access 2007 your connection string must have a correct provider included. We were missing "Provider=" in the ConnectionSting above

CHANGE

if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection.ConnectionString:='Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2007}
if UpperCase(RightStr(sDatabasePath,4)) = UpperCase('.mdb') then ADOConnection.ConnectionString:='Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2003 and below}

WITH

if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2007}
if UpperCase(RightStr(sDatabasePath,4)) = UpperCase('.mdb') then ADOConnection.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False'; {Connection String for Access 2003 and below}

if not then try installing 2007 Office System Driver: Data Connectivity Components
0
 

Author Comment

by:Bosanac
ID: 20749659
Did it with 2007:

procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb';
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False';
try
ADOConnection1.Connected:=True;
ADOTable1.Active:=True;
except
on E: Exception do begin
      ShowMessage(Format('unable to connect to database (%s). application will now terminate', [E.Message]));
      Application.Terminate;
    end;
     end;
connection works ok but I dont get any warning msg if db does not exist ?
     end;
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749752
Put this code before the final "end;"

if (FileExists(sDatabasePath)=false) then begin
 ShowMessage('Database does not exist, application will now terminate');
 Application.Terminate;
end;
0
 

Author Comment

by:Bosanac
ID: 20749924
procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb';
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)=true) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False';
ADOConnection1.Connected:=True;
ADOTable1.Active:=True;
end
else
if (FileExists(sDatabasePath)=false) then begin
 ShowMessage('Database does not exist, application will now terminate');
 Application.Terminate;

   end;
   end;
This works,however I get back to ziolkos 1st sugg.
Would it not be better ti use if FileExists in the first place ?
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749972
I can't understand you completely,
but I guess you want to change

if (FileExists(sDatabasePath)=true) then begin {returns true if file exist}
and
if (FileExists(sDatabasePath)=false) then begin {returns true if file does not exist}

with

if (FileExists(sDatabasePath)) then begin

?
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20749977
Correction: if (FileExists(sDatabasePath)=false) then begin {returns false if file does not exist}
0
 
LVL 21

Expert Comment

by:ziolko
ID: 20750248
>>Would it not be better ti use if FileExists in the first place ?


absolutelly, what's the point of trying to connect to access db if you don't even know if file exists?

and my personal advice... try to avoid constructs like this:
if ... = true then
or
if ... = false then

ziolko.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 20750424
Well if you like it more that way use this pattern, and do not forget to make a backup of your working code. I have similar code as the one I wrote above on a more than two years heavily exploited database application and noone reported any errors connecting. If you like other solutions be free to experiment. And if "Would it not be better ti use if FileExists in the first place ?" means that if FileExists is on the wrong place in the code, that is not true, it is just after full database path string is generated, highest possible position in the code. My advice is not to worry about this anymore, I will sugest you to move on with the application and work on other issues if you have them.

if (FileExists(sDatabasePath)) then
  begin
{Try to connect to the database}
  end
  else
  begin
{Show message and terminate application}
  end;
0
 

Author Comment

by:Bosanac
ID: 20750765
can you please post me the whole block of code using if fileexists for 2007 Access >DB?
0
 
LVL 6

Accepted Solution

by:
FactorB earned 400 total points
ID: 20751290
Ok, here it is for both types of databases

procedure TForm1.FormCreate(Sender: TObject);
var
sDatabaseName: String;
sApplicationPath: String;
sDatabasePath: String;
begin
sDatabaseName:='Database.accdb';
sApplicationPath:=ExtractFilePath(Application.exename);
if sApplicationPath[Length(sApplicationPath)]<>'\' then sApplicationPath:=sApplicationPath+'\';
sDatabasePath:=sApplicationPath+sDatabaseName;
if (FileExists(sDatabasePath)) then begin
if UpperCase(RightStr(sDatabasePath,6)) = UpperCase('.accdb') then ADOConnection1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+sDatabasePath+';Persist Security Info=False';
if UpperCase(RightStr(sDatabasePath,4)) = UpperCase('.mdb') then ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False';
try
ADOConnection1.Connected:=True;
ADOTable1.Active:=True;
except
ShowMessage ('There was a problem connecting to the database, application will now terminate');
Application.Terminate;
end;
end
else begin
ShowMessage('Database does not exist, application will now terminate');
Application.Terminate;
end;
end;
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

591 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