1. Operate access database in DELPHI (create .mdb file, compress database)
The following code passed the test under WIN2K, D6, MDAC2.6,
The compiled program runs successfully in the WIN98 second version without ACCESS environment.
//Before uses ComObj,ActiveX
//Declare the 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 the temporary file name
var
SPath,SFile: array [0..254] of char;
begin
GetTempPath(254,SPath);
GetTempFileName(SPath,'~SM',0,SFile);
result:=SFile;
DeleteFile(PChar(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 Access file, 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;
//Compress and repair database, overwrite 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;
//================================================ =============================
// Procedure: ChangeDatabasePassword
// Author: ysai
// Date: 2003-01-27
// Arguments: AFileName,AOldPassWord,ANewPassWord:string
// Result: boolean
//================================================ =============================
function ChangeDatabasePassword(AFileName,AOldPassWord,ANewPassWord:string):boolean;
//Modify ACCESS database password
var
STempFileName: string;
vJE:OleVariant;
begin
STempFileName:=GetTempPathFileName;
try
vJE:=CreateOleObject('JRO.JetEngine');
vJE.CompactDatabase(format(SConnectionString,[AFileName,AOldPassWord]),
format(SConnectionString,[STempFileName,ANewPassWord]));
result:=CopyFile(PChar(STempFileName),PChar(AFileName),false);
DeleteFile(STempFileName);
except
result:=false;
end;
end;
2. Things you should pay attention to and some tips when using SQL statements in ACCESS
The following SQL statement passed the query in ACCESS XP
Create table:
Create Table Tab1 (
ID Counter,
Name string,
Age integer,
[Date] DateTime);
Skill:
Auto-increment fields are declared with Counter.
Fields whose field names are keywords are enclosed in square brackets [], and numbers are also acceptable as field names.
Create index:
The following statement creates a repeatable index on the Date column of Tab1
Create Index iDate ON Tab1 ([Date]);
After completion, the Date index attribute of the field in ACCESS is displayed as - Yes (there are duplicates).
The following statement creates a non-repeatable index on the Name column of Tab1
Create Unique Index iName ON Tab1 (Name);
After completion, the index attribute of the field Name in ACCESS is displayed as - Yes (no duplication).
The following statement deletes the two indexes just created
Drop Index iDate ON Tab1;
Drop Index iName ON Tab1;
Comparison of ACCESS and UPDATE statements in SQLSERVER:
UPDATE statement to update multiple tables in SQLSERVER:
UPDATE Tab1
SET a.Name = b.Name
FROM Tab1 a,Tab2 b
WHERE a.ID = b.ID;
The SQL statement with the same function in ACCESS should be
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 above example, if Tab2 is 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;
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 associated with Tab2 in Tab1 and db2.mdb (in the current folder) in the current database.
Disadvantages - External databases cannot have passwords.
Supplement: I saw ugvanxk’s reply in a post and can use it
Select * from [c:/aa/a.mdb;pwd=1111].table1;
ACCESS XP test passed
Access other ODBC data sources in ACCESS
The following example queries the data in SQLSERVER in ACCESS
SELECT * FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
The complete parameters of the external data source connection property are:
[ODBC;DRIVER=driver;SERVER=server;DATABASE=database;UID=user;PWD=password;]
DRIVER=driver can be found in the registry
HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/
found in
For data import between heterogeneous databases, please refer to Blue Blood Sword
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966
ACCESS supports subqueries
ACCESS supports outer joins, but does not include complete outer joins. If supported,
LEFT JOIN or RIGHT JOIN
but not supported
FULL OUTER JOIN or FULL JOIN
Date query in ACCESS
Note: The date and time separator in ACCESS is # instead of quotation marks
Select * From Tab1 Where [Date]>#2002-1-1#;
In DELPHI I use this
SQL.Add(Format(
'Select * From Tab1 Where [Date]>#%s#;',
[DateToStr(Date)]));
Strings in ACCESS can be separated by double quotes, but SQLSERVER does not recognize them, so for the convenience of migration and compatibility,
It is recommended to use single quotes as string delimiters.