Check if database exists

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..
BosanacAsked:
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.

ziolkoCommented:
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
FactorBCommented:
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
FactorBCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ziolkoCommented:
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
ziolkoCommented:
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
FactorBCommented:
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
ziolkoCommented:
>>If not, maybe some driver should be specified in the connection string?
that's driver specification:
Provider=Microsoft.Jet.OLEDB.4.0;

ziolko.
0
FactorBCommented:
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
ziolkoCommented:
really? didn't know that, haven't worked with access for some time

ziolko
0
FactorBCommented:
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
FactorBCommented:
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
BosanacAuthor Commented:
[Error] Unit1.pas(110): Undeclared identifier: 'RightStr'
0
BosanacAuthor Commented:
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
ziolkoCommented:
probably you need to add StrUtils to your uses clausule

ziolko.
0
FactorBCommented:
Try to declare it with other functions

function RightStr(const AString: AnsiString; const Count: Integer): AnsiString; overload;
0
FactorBCommented:
I think you are right about StrUtils
0
BosanacAuthor Commented:
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
BosanacAuthor Commented:
also when i rename the DB dont get no error msg...?!
0
BosanacAuthor Commented:
elso get error : could not find installable ISAM...
0
BosanacAuthor Commented:
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
FactorBCommented:
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
FactorBCommented:
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
FactorBCommented:
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
BosanacAuthor Commented:
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
FactorBCommented:
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
BosanacAuthor Commented:
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
FactorBCommented:
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
FactorBCommented:
Correction: if (FileExists(sDatabasePath)=false) then begin {returns false if file does not exist}
0
ziolkoCommented:
>>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
FactorBCommented:
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
BosanacAuthor Commented:
can you please post me the whole block of code using if fileexists for 2007 Access >DB?
0
FactorBCommented:
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

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
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.