1. ACCESS database in Delphi (establish a .mdb file, compressed database)
The following code is passed under Win2K, D6, MDAC2.6,
Compiled good programs run in the Win98 second edition without an Access environment.
// Uses Comobj, ActiveX
// Declare 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;
// 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;
// Establish an Access file, if the file exists, failed
VAR
STEMPFILENAME: STRING;
vcatalog: olevariant;
Begin
StempFileName: = GetTemppathFileName;
Try
Vcatalog: = CREATEOLOBJECT ('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;
// Compressed and repair the database, overwrite the source file
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. Places that use the SQL statement to pay attention to and several techniques in the Access
The following SQL statements are tested in the query of Access XP
Construction form:
Create Table Tab1
ID Counter,
Name String,
Age INTEGER,
[DATE] DATETIME);
skill:
Self-add field declaration with Counter.
The field named field name is enclosed in square brackets [], and the number is also valued as a field name.
Establish an index:
The following statement establishes repeatable index on the Date column of TAB1
CREATE INDEX Idate on Tab1 ([DATE]);
After completion, the field Date index attribute is displayed as - there is (have repetition). The following statement creates a non-repeatable index on the Name column of TAB1.
CREATE UNIQUE INDEX INAME ON TAB1 (NAME);
After completion, the field name index attribute is displayed as - there is (no repetition).
The following statement deletes the two indexes established.
Drop Index Idate on Tab1;
Drop Index Iname on Tab1;
ACCESS comparison with UPDATE statements in SQLServer:
Update the Update statement of multi table in SQLServer:
Update Tab1
Set a.name = B.Name
From tab1 a, tab2 b
WHERE A.ID = B.ID;
The same functionality SQL statement 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 an from clause, and all references are listed after the UPDATE keyword.
In the above example, if Tab2 is not a table, but a query, an 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 in clauses 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 the ID in the current database in the current database (current folder).
Disadvantages - External databases cannot with password.
Supplement: See the reply of Ugvanxk in a post, you can use
Select * from [C: /AA/A.mdb; PWD = 1111] .table1;
Access XP test passed
Access other ODBC data sources in Access
The following example query 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 of external data source connection properties are:
[Odbc; driver = driver; server = server; database = data; uid = user; pwd = password;]
Where driver = driver can be in the registry
HKEY_LOCAL_MACHINE / SOFTWARE / ODBC / ODBCINST.INI /
Found
Data between heterogeneous databases see the blood sword
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966
Access support child inquiry
Access supports external connections, but does not include complete external join, such as support
Left Join or Right Join
But not support
Full Outer Join or Full Join
Date in ACCESS
Note: Date 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)]));
The strings in Access can be separated by double quotation marks, but SQL Server does not recognize, so in order to migrate convenient and compatible.
It is recommended to use single quotes as a string separator.
