We help IT Professionals succeed at work.

Check if database exists

Bosanac
Bosanac asked
on
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..
Comment
Watch Question

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
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.

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

Commented:
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. :)
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

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

Commented:
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
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

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

ziolko.

Commented:
I know that,
but try using it for Access 2007 .accdb and you will get the message
Error
Unrecognized database format 'C:\Database.accdb'
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
really? didn't know that, haven't worked with access for some time

ziolko

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

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

Author

Commented:
[Error] Unit1.pas(110): Undeclared identifier: 'RightStr'

Author

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;

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
probably you need to add StrUtils to your uses clausule

ziolko.

Commented:
Try to declare it with other functions

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

Commented:
I think you are right about StrUtils

Author

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;

Author

Commented:
also when i rename the DB dont get no error msg...?!

Author

Commented:
elso get error : could not find installable ISAM...

Author

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

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

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

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

Author

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;

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

Author

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 ?

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

?

Commented:
Correction: if (FileExists(sDatabasePath)=false) then begin {returns false if file does not exist}
Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

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

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

Author

Commented:
can you please post me the whole block of code using if fileexists for 2007 Access >DB?
Commented:
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;

Explore More ContentExplore courses, solutions, and other research materials related to this topic.