1. Delphi operations ACCESS database (established .mdb file, compressed database) The following code is passed under win2k, d6, mdac2.6, compiled, compiled programs run in the Win98 second edition without an Access environment. Uses Comobj, ActiveX // Declaration Connection Strings const sconnectionstring = 'provider = microsoft.jet.OLEDb.4.0; data source =% s;' 'Jet OLEDB: Database Password =% s;'; // ==== ============================================================================================================================================================================================================= ========================================================383-01-27 // arguments: (none) / / Result: string / / ============================================ ========================================== function getTemppathFileName (): string; // get 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 // ============================================================================================================================================================================================================= ======================================================================================================================================================================= # If the file exists fails 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 results: = false; end; end; // ================ ============================================================================================================================================================================================================= =========== // Procedure: CompactDatabase // Author: ysai // Date: 2003-01-27 // arguments: AFILENAME, APASSWORD: STRING // Result: Boolean / / ==== ===============================
==================================================== function compactDatabase (AfileName, APASSWORD: string): boolean; // compression and repair the database, covering 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; / / ===================================================================================================================== ============================================================================================================================================================================================================ 27 // Arguments: AfileName, Aoldpassword, AnewPassword: String // Result: Boolean / / =============================== ======================================================= in Function ChangeDatabasePassword (AfileName , AOLDPASSWORD, ANEWPASSWORD: STRING: Boolean;
// modify the 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.ACCESS using SQL Where the statement should pay attention to and some SQL statements are tested in the query of Access XP through the table: CREATE TABLE TAB1 (ID Counter, Name String, Age Integer, [Date] DateTime); Skill: Self-adding Declaration The field name is the field name [] encloses, the number is also possible. Create an index: The following statement establishes repeatable index Crete Index IDate on Tab1 ([Date]) on Tab1's DATE column. After completion, the field date index attribute is displayed as - there is (have repetitive). The following statement creates a non-repeatable index Create Unique Index INAME on Tab1 (Name) on the Name column of Tab1; after completion of the field name index attribute display as - Have (no repetition). The following statement deletes the two index Drop Index IDate on Tab1 established; Drop Index INAME ON TAB1; Access and Update statement in SQLServer compare: Update Tab1 Set A.Name = B.NA ME from Tab1 a, tab2 b where A.id = B.ID; SQL statement of the same function should be Update Tab1 a, Tab2 B Set a.name = B.Name where A.Id = B.ID; ie : The UPDATE statement in Access does not have an 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, 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 TAB1 and DB2.MDB (current folder) in the current database. Disadvantages - External databases cannot With password. Supplement: See the reply in a post, you can use Select * from [c: /a/a.mdb; pwd = 1111] .table1; Access XP test By accessing other ODBC data in Access Example Query data in SQL Server in Access Select * from Tab1 In [ODBC] [ODBC; Driver = SQL Server; UID = SA; PWD =; Server =
