Art Delphi Automation History Home Politics Email me Default Colours Printable Colours
Contents
| Web sites | Easy
Access, article by Bill Todd Delphi sites Non-Delphi sites |
Both methods check to see if Access is already running before starting a new instance.
Using the D5 components to start Access
If you've got the D5 patch, you can use the TAccessApplication component to start Access. Drop one one a form, and if its AutoConnect property is true, Access will start automatically when your program starts; if it's false, just call
AccessApplication1.Connect;
when you want to start. To use a running instance of Access, if there is one, set the ConnectKind property of TAccessApplication to ckRunningOrNew, or to ckRunningInstance if you don't want to start a new instance if Access isn't running.
Once Access has started, you can connect other components, such as TAccessReport, using their ConnectTo methods:
AccessApplication1.Connect;
AccessApplication1.Visible := True;
AccessApplication1.OpenCurrentDatabase('C:\Office\Samples\Northwind.mdb', True);
AccessApplication1.DoCmd.OpenReport('Sales by Year', acViewDesign, EmptyParam, EmptyParam);
AccessReport1.ConnectTo(AccessApplication1.Reports['Sales by Year']);
AccessReport1.Caption := 'Annual sales - from bad to worse';
Note that a workbook or worksheet must be open before you can connect to it.
If you haven't got the patch for D5, starting Access is a bit different, because there is no TAccessApplication component. (This is because Microsoft declared the Application object as hidden in the type library.) However you can create an Application object in the same way as it was done in D4 (see below), and then connect the Access components to it. See Access- common problems for an example.
Opening Access (early binding)
Before you can use this method, you must have imported the type library (MSAcc8.olb for Access 97).
One way of starting Access is Excelto try the GetActiveObject call, to get a running instance of Access, but put a call to CoApplication.Create in an except clause. But except clauses are slow, and can cause problems within the IDE for people who like Break On Exceptions set to True. The following code removes the need for a try...except clause, by avoiding using OleCheck on GetActiveObject in the case when Access is not running.
uses Windows, ComObj, ActiveX, Access_TLB;
var
Access: _Application;
AppWasRunning: boolean; // tells you if you can close Access when you've finished
Unknown: IUnknown;
Result: HResult;
begin
AppWasRunning := False;
{$IFDEF VER120} // Delphi 4
Result := GetActiveObject(CLASS_Application_, nil, Unknown);
if (Result = MK_E_UNAVAILABLE) then
Access := CoApplication_.Create
{$ELSE} // Delphi 5
Result := GetActiveObject(CLASS_AccessApplication, nil, Unknown);
if (Result = MK_E_UNAVAILABLE) then
Access := CoAccessApplication.Create
{$ENDIF}
else begin
{ make sure no other error occurred during GetActiveObject }
OleCheck(Result);
OleCheck(Unknown.QueryInterface(_Application, Access));
AppWasRunning := True;
end;
Access.Visible := True;
...
Without using the type library
Automation is so much easier and faster using type libraries (early binding) that you should avoid managing without if at all possible. But if you really can't, here's how to get started:
var
Access: Variant;
begin
try
Access := GetActiveOleObject('Access.Application');
except
Access := CreateOleObject('Access.Application');
end;
Access.Visible := True;
Assuming an application variable, Access:
Access.Quit(acQuitSaveAll); Access := nil;
The Quit method's parameter is of type TOleEnum, and should be one of acQuitSaveNone, acQuitSaveAll, or acQuitPrompt. If you're not using the type library, you could declare these like this:
const
acQuitPrompt = $00000000;
acQuitSaveAll = $00000001;
acQuitSaveNone = $00000002;
Assuming an application variable, Access:
Access.OpenCurrentDatabase('C:\My Documents\Books.mdb', True);
The second parameter specifies whether you want to open the database in
Exclusive mode.
Assuming an application variable, Access:
Access.CloseCurrentDatabase;
Assuming an application variable, Access:
Access.DoCmd.OpenReport('Titles by Author', acViewPreview, EmptyParam, EmptyParam);
The value for the second parameter should be one of acViewDesign, acViewNormal, or acViewPreview. acViewNormal, which is the default, prints the report immediately. If you are not using the type library, you can define these values like this:
const
acViewNormal = $00000000;
acViewDesign = $00000001;
acViewPreview = $00000002;
The third parameter is for the name of a query in the current database. The fourth parameter is for a SQL WHERE clause - the string must be valid SQL, minus the WHERE.
Once you've opened a database, you can use the Access application's Reports and Forms properties to list the open reports and forms:
for i := 0 to Access.Reports.Count - 1 do
Memo1.Lines.Add(Access.Reports[i].Name);
for i := 0 to Access.Forms.Count - 1 do
Memo1.Lines.Add(Access.Forms[i].Name);
But note that these properties return only the open reports and forms in a database. To get the closed ones you have to use DAO97.pas (or DAO_TLB.pas for Delphi 4) and access the Documents collection:
uses DAO97;
var
i: integer;
Cont: Container;
begin
Cont := Access.CurrentDB.Containers.Item['Reports'];
for i := 0 to Cont.Documents.Count - 1 do
Memo1.Lines.Add(Cont.Documents[i].Name);
Cont := Access.CurrentDB.Containers.Item['Forms'];
for i := 0 to Cont.Documents.Count - 1 do
Memo1.Lines.Add(Cont.Documents[i].Name);