ACCESS skill sets of: ysai reprint please indicate the source and keep the article intact
1.Delphi ACCESS database (established .mdb file, compressed database) The following code is tested under Win2K, D6, MDAC2.6, compiled, compiled programs run in the Win98 second edition without Access Environment. // declaration 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 / / ======================================== ======================================= ======================================================================================================= File Name 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 // =============================== ================================================================================================================================================================================================================== # : String; PassWord: string = ''): boolean; // build Access file, 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 result: = false; end; end ;
/ / =========================================================================================================================================================================================== ===================================== // procedure: CompactDatabase // Author: ysai // Date: 2003-01-27 // Arguments: AFILENAME, APASSWORD: STRING / / RESULT: BOOLLAN / / ==================================== ================================================== 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 2. The following SQL statements should be considered in the SQL statement in the Access XP in the query of Access XP. : The self-adding field is declared with a Counter. The field name is the field of the keyword, which is enclosed in square brackets, and the numbers are also feasible as a field name.
Establish an index: The following statement establishes repeatable index Crete index idate on tab1 on the DATE column of Tab1; after completion of the field Date index attribute displayed as - there is (have repetition). The following statement is in Tab1 The Name column creates non-repeatable index Create Unique Index INDEX INDEX INDEX INDEX INDEX INDET INTENT NAME Index Attributes in ACCESS (None Repeat). The following statement deletes the two indexed Drop Index Idate on Tab1 established; DROP INDEX INAME ON TAB1; Access and SQLServer comparison: Update statement of multi table in SQL Server: Update Tab1 set a.name = B.Name from Tab1 A, Tab2 B Where A.Id = B.ID; Function SQL statement should be Update Tab1 a, tab2 b set a.name = B.Name where A.Id = B.ID; ie: 即: 即Date statement does not have from clauses, all references 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 .
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; SQL statement query Tab2 in the Tab1 and DB2.mdb (current folder) in the current database is associated with the ID. Disadvantages - External databases cannot with password.
Accessing other ODBC Data Source in Access Squate in Access SQL Server Data 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 properties are: [ODBC; Driver = Driver; Server = Server; Database = Database; UID = USER; PWD = user; pwd = password;] where driver = driver can be in the registry hkey_local_machine / software / Software /Odbc/odbcinst.ini/ found
Access support child inquiry
Access supports external connections, but does not include complete external join, such as supporting Left Join or Right Join but does 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 #; I use Sql.Add in Delphi. * From Tab1 where [Date]> #% s #; ', [DateTostr (date)]));
The strings in Access can be separated by dual quotation marks, but SQL Server does not recognize, so it is recommended to use single quotes as a string separator in order to migrate convenient and compatible.
