Access Tips Set

Access Tips Set
Author: Ysai
Reprint please keep the article complete and indicate the source

1.DELPHI Operation Access database (create. mdb file, compress database)
The following code is tested under win2k,d6,mdac2.6,
The compiled program was successfully run in the WIN98 second edition without an Access environment.
Declaring a connection string
Const
sConnectionString = ' Provider=Microsoft.Jet.OLEDB.4.0;Data source=%s; '
+ ' Jet oledb:database password=%s; ';

//=============================================================================
//Procedure: Gettemppathfilename
//author  : Ysai
//date    : 2003-01-27
//Arguments: ( None)
//result  : String
//==================================================================== =========
Function Gettemppathfilename (): string;
//Get temporary filename
var
  Spath,sfile:array [0..254] of char;
Begin
  GetTempPath (254,spath);
  GetTempFileName (spath, ' ~sm ', 0,sfile);
  Result:=sfile;
  DeleteFile (result);
End;

//=============================================================================
//Procedure: Createaccessfile
//author  : Ysai
//date    : 2003-01-27
//Arguments: filename:string; Password:string= '
//result  : Boolean
//====================================================== =======================
Function Createaccessfile (filename:string; Password:string= '): boolean;
//Create an Access file and fail if the file exists
Var
  stempfilename:string
  Vcatalog:olevariant;
Begin
  Stempfilename:=gettemppathfilename
  Try
    vcatalog:=createoleobject (' ADOX. Catalog ');
    vcatalog.create (Format (Sconnectionstring,[stempfilename,password]));
    result:=copyfile (Pchar (sTempFilename), Pchar (FileName), True);
    DeleteFile (stempfilename);
  except
    result:=false
  END;
End;

//=============================================================================
Procedure:compactdatabase
Author:ysai
Date:2003-01-27
Arguments:afilename,apassword:string
Result:boolean
//=============================================================================
function CompactDatabase (afilename,apassword:string): boolean;
compressing and repairing databases, overwriting source files
Var
stempfilename:string;
Vje:olevariant;
Begin
Stempfilename:=gettemppathfilename;
Try
Vje:=createoleobject (' JRO. JetEngine ');
Vje.compactdatabase (Format (Sconnectionstring,[afilename,apassword]),
Format (Sconnectionstring,[stempfilename,apassword]));
Result:=copyfile (Pchar (sTempFilename), Pchar (Afilename), false);
DeleteFile (sTempFilename);
Except
Result:=false;
End
End

The points to be noted in using SQL statements in 2.ACCESS and some techniques
The following SQL statement tests through the query in Access XP
Build table:
Create Table TAB1 (
ID Counter,
Name String,
Age integer,
[Date] DateTime);
Skills:
Self-added fields are declared with Counter.
fields with fields named keywords are enclosed in square brackets [], and numbers are also available as field names.

To establish an index:
The following statement establishes a repeatable index on the Tab1 date column
Create Index idate on TAB1 ([Date]);
When you are finished, the field Date index properties in Access are displayed as-there (duplicates).
The following statement establishes a non-repeatable index on the TAB1 name column
Create Unique Index iname on TAB1 (Name);
When you are done, the field name index properties in Access are displayed as-there (no duplicates).
The following statement deletes the two indexes that have just been established
Drop Index idate on TAB1;
Drop Index iname on TAB1;

Access is contrasted with the UPDATE statement in SQL Server:
Update statements for multiple tables are updated in SQL Server:
UPDATE TAB1
SET A.name = B.name
From TAB1 a,tab2 b
WHERE a.id = b.id;
SQL statements with the same functionality should be in Access
UPDATE TAB1 a,tab2 b
SET A.name = B.name
WHERE a.id = b.id;
That is, the UPDATE statement in Access does not have a FROM clause, and all referenced tables are listed after the UPDATE keyword.
In the example above, if TAB2 can be not a table, but a query, for example:
UPDATE Tab1 A, (Select id,name from TAB2) b
SET A.name = B.name
WHERE a.id = b.id;

To access multiple different Access databases-use the IN clause in SQL:
Select a.*,b.* from Tab1 a,tab2 b in ' Db2.mdb ' Where a.id=b.id;
The above SQL statement queries all records in the current database Tab1 and Db2.mdb (in the current folder) Tab2 that are associated with IDs.
Disadvantage-The external database cannot be with a password.

Access other ODBC data sources in Access
The following example queries in access for data in SQL Server
    SELECT * from TAB1 in [ODBC]
     [Odbc;driver=sql Server; Uid=sa; pwd=; Server=127.0.0.1;dat