When creating (ADO - dbGO) database applications using Delphi, you almost always connect to the data store by using the TADOConnection object - or any other TCustomConnection descendant like TDatabase (BDE) or TSQLConnection (dbExpress).
The TADOConnection let's you specify the database connection string at design-time and by setting the Connected property to true, you can even work with the live data at design-time.
In most cases (99.99%) the location of the data store (SQL Server, Access database, etc.) will differ from a user to a user - thus you will have to construct the database connection string at run-time.
"SQL Server Does Not Exist Or Access Denied", "Database.MDB does NOT exist", "DBNETLIB exception"
Obviously, when creating database applications that are to be run on various machines, the connection to the data source should not be hard-coded in the executable.Unfortunatelly, no matter how much I (guess this refers to all of you) try not to leave the Connected property to true before building for the last time - in most cases I simply forget to close the connection before saving and closing the project.
When the application is started, if the Connected property is left to "True", the application will try to connect to the database using the design-time hard coded ConnectionString property!
If the TADOConnection is placed on a DataModule - when the data module is created the connection object will try to connect to the hard coded database - and will fail!
What's even worst - you cannot use the OnCreate event to stop Delphi from trying to connect to the non-existing database. Once OnCreate is processed you will probably want to read the database connection string parameters from some user dependant storage like Registry or INI files.
Canceling the Design-Time Connection at Run-time, or Don't Connect before I Tell you How and Where!
Take a look at the following program's source code - the application firstly creates the DataModule where the TADOConnection (and other TDataset descendant objects) are located.A custom DatabaseConnect method sets the connection string and tries to connect to the database. If succesfull, the main form of the application is created and the application is run. If not, an error is displayed to the user.
While "constructing" the DataModule (even *before* the OnCreate event is fired), if the Connected property was left to "True", the application will try to connect to the database specified in the ConnectionString property - the one that points to the database on your development machine.
Depending on the type of the database - various "weird" errors will be displayed to the user, stating that the database cannot be located. I think there's no user who will like to see those error messages.
program DBConnDesignTime;
...
begin
Application.Initialize;
Application.CreateForm(TMainDataModule, MainDataModule) ;
if MainDataModule.DatabaseConnect then
begin
Application.CreateForm(TMainForm, MainForm) ;
Application.Run;
end
else
begin
"User friendly" warning - not the unfriendly error message!
Application.MessageBox('Cound not connect to the database','Connection Error') ;
end;
end.
When the Data Module's Create constructor is processed, Delphi will try to connect to the non-existing database even before the OnCreate event is fired (as the first even you can handle for a TComponent)
The DatabaseConnect method responsible for setting the connection string will be called *after* the unsucessfull connection dialog is displayed to the user.
One way to handle this scenario is to add a custom boolean property to the data module, and set its initial value on the overriden constructor - *before* the original inherited constructor is called.
Next, in the BeforeConnect event, which occurs immediately before establishing a connection, call Abort to stop the connection object trying to connect to the database using the design-time specified connection string.
Here's the source code:
unit dm;
interface
uses
SysUtils, Classes, DB, ADODB;
type
TMainDataModule = class(TDataModule)
ADOConnection1: TADOConnection;
procedure ADOConnection1BeforeConnect(Sender: TObject) ;
private
fRunTimeConnStringSet: boolean;
property RunTimeConnStringSet : boolean read fRunTimeConnStringSet default false;
public
function DatabaseConnect : boolean;
constructor Create(AOwner: TComponent) ; override;
end;
var
MainDataModule: TMainDataModule;
implementation
{$R *.dfm}
//BeforeConnect
procedure TMainDataModule.ADOConnection1BeforeConnect(Sender: TObject) ;
begin
//do not try to connect if ConnectionString was
//assigned in design-time and Connected was left to "True"
if (NOT RunTimeConnStringSet) then Abort;
end;
//overriden constructor
constructor TMainDataModule.Create(AOwner: TComponent) ;
begin
fRunTimeConnStringSet := false;
inherited;
end;
function TMainDataModule.DatabaseConnect: boolean;
begin
ADOConnection1.Close;
//get database location/name from some
//storage like Registry, INI, etc...
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbdemos1.mdb;';
ADOConnection1.LoginPrompt := false;
fRunTimeConnStringSet := true;
ADOConnection1.Open;
result := ADOConnection1.Connected;
end;
end.
