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

Accessing other ODBC data sources in Access
The following example queries the data in SQL Server in Access
SELECT * from TAB1 in [ODBC]
[Odbc;driver=sql Server; Uid=sa; pwd=; Server=127.0.0.1;database=demo;]
The complete parameters for the external data source connection properties are:
[Odbc;driver=driver; Server=server;database=database; Uid=user; Pwd=password;]
The Driver=driver in the registry can be
Hkey_local_machine/software/odbc/odbcinst. ini/
Found in

Access supports subqueries

Access supports external joins, but does not include full outer joins, such as support
Left JOIN or RIGHT Join
But does not support
Full OUTER join or full join

Date Query in Access
Note: The date time separator in Access is # instead of quotes
Select * from Tab1 Where [date]> #2002 -1-1#;
I use it in Delphi.
Sql. Add Format (
' Select * from Tab1 Where [date]>#%s#; ',
[Datetostr (Date)]);

Strings in Access can be delimited by double quotes, but SQL Server is not recognized, so for migration convenience and compatibility,
It is recommended that single quotes be used as string delimiters.

Querying tables in an Access database in SQL Server
SELECT * FROM
OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ',
' C:/youfile.mdb '; ' Admin '; ' Youpwd ', youtabname)
As a

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.