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;';
/ / =========================================================================================================================================================================================== ===================================== // 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 ", sfile); Result: = sfile; deletefile (Pchar (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 ;
/ / =========================================================================================================================================================================================== ============================= // Procedure: ChangeDatabasePassword // Author: ysai // Date: 2003-01-27 // Arguments: AFILENAME, AOLDPASSWORD, ANEWPASSWORD: STRING // Result: Boolean / / ================================== ====================================================================================================================================00 ANewPassWord: string): boolean; // compressed ACCESS database and modify the 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 to the SQL statement to pay attention to the following SQL statements In the Access XP query test through the table: Create Table Tab1 (ID Counter, Name String, Age Integer, [DATE] DateTime ); Skill: Self-adding field with coun Ter declaration. 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 All records associated with the Tab1 and DB2.mdb (current folder) in the current database. Disadvantages - External databases cannot with password. Supplement: Seeing Ugvanxk replies in a post, you can use SELECT * FROM [c: /a/a.mdb; pwd = 1111] .table1; Access XP test
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/ Find the heterogeneous database between 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 and multi-external connections, 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.
Access Constraints In Jet SQL Reference About constraints about constraints, you can refer to the Name field of SQL A table below SQL Server, add non-empty constraints ALTER TABLE A Add Constraint A_CHECKNAME CHECK (NOT [Name] is Null) Note: Every constraint is an object, there is a name
To set the statement below the primary key to set the ID column as the primary button Alter Table [Table] Add primary key (id) Change the ID column to the automatic number type, and set to the primary key alter table [Table] ALTER [ID] Counter Constraint [Table _P] Primary Key
Modify the password of the Access database / / Reference COMOBJ unit
/ / =========================================================================================================================================================================================== ================================================================================================================================================ # Arguments: const AOLDPASSWORD: STRING; const anewpassword: string // result: boolean // =========================== ============================================================================================================================================================================================================= function ChangeAccessPassword (const aFileName: string; const AOldPassword: string; const ANewPassword: string): Boolean; // modify ACCESS database password, you must open the database exclusively before use make sure that no other programs that use the database const SAlterDatabasePassword = 'ALTER dATABASE pASSWORD% s% s'; var ACN: Olevariant; sold: string; snew: string; begin if aoldpassword = '' Then Sold: = 'NULL' Else Sold: = '[' AOLDPASSWORD ']'; ifwpassword = '' THEN SNEW: = 'NULL' Else SNEW: = '[' AnewPassword ']'; Try AcN: = CreateoleObject ('AdoDb.Connection'); // Delphi CMShareExClusive, Ado's AdmodeshareExClusive // A Row Way open, direct use of numbers can not reference the ADO unit acn.mode: =
