Title: Building an ADO ConnectionString for an Access Database at Runtime
Question: How to change ADO provider and filename of a MS Access database at runtime
Answer:
ADO is an intersting way to work with Access database files with Delphi. The key object to link the database with the Delphi application is the TADOConnection class. Among other things, it's property "ConnectionString" defines the provider and the database file.
The Connectionstring is usually set up at design time within the Delphi IDE. But what if, at run time, the user needs an apportunity to change the name of the database file? Another complication occurs when the application needs to run on miscellaneous systems with different ADO versions. Therefore, there should be a possibility to select the appropriate provider at run time.
The following Delphi function "BuildAccessConnectionString" was written to overcome these limitations.
- The parameter "AFileName" is the name of the access database file (extension ".mdb") to connect to.
- The parameter "AccessVersion" specifies the required provider version: "access2000" needs the "Microsoft.Jet.OLEDB.4.0" provider for the Access2000 file format, "access97" needs the "...OLE.DB.3.51" for the Access97 format. "accessAny", finally, tries to read the version of the ADO provider from the registry and opens the connection for a test. Of course, the database must not be in the Access2000 format for this option to work.
- The parameter "CDROM", finally, inserts the phrase "Mode=Share Deny Write" into the connection string which is necessary to open a database on a CD-ROM.
If successful, the function returns a string which can be used for the "ConnectionString" property of the TADOConnection.
The function was tested with Delphi6, but should work also with Delphi 5.
Hope that this little routine is of any value for Delphi programmers working with ADO...
Werner Pamler
-------------------------------------------------------------------------
type
TAccessVersion = (accessAny, access2000, access97);
resourcestring
SFileMissing = 'Name of database file (*.mdb) not specified.';
SFileNotFound = 'Database file %s not found.';
SNotInstalled = 'ADO is not installed on this system.';
SProviderNotInstalled = 'ADO provider %s not installed on this system.';
function BuildAccessConnectionString(AFileName:string;
AccessVersion:TAccessVersion; CDROM:boolean) : string;
const
Providers : array[TAccessVersion] of string = (
'',
'Microsoft.Jet.OLEDB.4.0',
'Microsoft.Jet.OLEDB.3.51'
);
var
conn : TADOConnection;
provider : string;
function ReadProviderFromReg : string;
var
reg : TRegistry;
begin
result := '';
reg := TRegistry.Create;
try
reg.RootKey := HKEY_CLASSES_ROOT;
if reg.KeyExists(Providers[access2000])
then result := Providers[access2000]
else if reg.KeyExists(Providers[access97])
then result := Providers[access97];
finally
reg.Free;
end;
end;
function CheckConnection(const _Provider:string;
var ConnStr:string) : boolean;
var
ConnMask : string;
begin
if CDROM then
connMask := 'Provider=%s;Data Source=%s;Mode=Share Deny Write;'+
'Persist Security Info=False'
else
connMask := 'Provider=%s;Data Source=%s;'+
'Persist Security Info=False';
ConnStr := Format(connMask, [_Provider, AFileName]);
try
conn.ConnectionString := ConnStr;
conn.Connected := true;
conn.Connected := false;
result := true;
except
ConnStr := '';
result := false;
end;
end;
begin
result := '';
if AFileName='' then
raise Exception.Create(SFileMissing);
if not FileExists(AFileName) then
raise Exception.CreateFmt(SFileNotFound, [AFileName]);
conn := TADOConnection.Create(nil);
try
conn.LoginPrompt := false;
if AccessVersionaccessAny
then provider := Providers[AccessVersion]
else provider := ReadProviderFromReg;
if provider='' then
raise Exception.Create(SNotInstalled);
if not CheckConnection(provider, result)
then Exception.CreateFmt(SProviderNotInstalled, [provider]);
finally
conn.Free;
end;
end;