1. Operate the ACCESS database in DELPHI (Create A. mdb file and compress the database)
The following code passes the test under WIN2K, D6, and MDAC2.6,
The compiled program runs successfully in the second version of WIN98 without ACCESS.
// 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 // ================================================ ======================================================== FunctionGetTempPathFileName ():String; // Get the temporary file name Var SPath, SFile:Array[0 .. 254]OfChar; 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 // ================================================ ======================================================== FunctionCreateAccessFile (FileName:String; PassWord:String= ''): Boolean; // Create an Access file. If the file exists, it 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: boolean // ================================================ ======================================================== FunctionCompactDatabase (AFileName, APassWord:String): Boolean; // Compress and fix the database to 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;
|
2. Notes and tips for using SQL statements in ACCESS
The following SQL statement passed the test in ACCESS XP Query
Table creation:
Create Table Tab1 (
ID Counter,
Name string,
Age integer,
[Date] DateTime );
TIPS:
Use Counter to declare the auto-increment field.
Fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names.
Index creation:
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 ACCESS field is displayed as-Yes (repeated ).
The following statement creates a non-repeated index on the Name column of Tab1
Create Unique Index iName ON Tab1 (Name );
After the ACCESS is complete, the field Name index attribute is displayed as-Yes (no duplicates ).
The following statement deletes the two indexes just created
Drop Index iDate ON Tab1;
Drop Index iName ON Tab1;
Comparison between ACCESS and SQL Server UPDATE statements:
UPDATE statements for updating 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 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 the FROM clause. All referenced tables are listed after the UPDATE keyword.
In the above example, if Tab2 can be 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 preceding SQL statement queries all the records associated with IDs in Tab1 and db2.mdb (in the current folder) in the current database.
Disadvantage-the external database cannot contain a password.
ACCESS other ODBC data sources in ACCESS
The following example shows how to query 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 attribute are:
[ODBC; DRIVER = driver; SERVER = server; DATABASE = database; UID = user; PWD = password;]
Where DRIVER = driver can be in the Registry
HKEY_LOCAL_MACHINE \ SOFTWARE \ ODBC \ ODBCINST. INI \
.
ACCESS supports subqueries
ACCESS supports external connections, but does not include complete external connections. For example, left join or right join is supported, but full outer join or full join is not supported.
Date query in ACCESS
Note: The Date and Time delimiter in ACCESS is # Instead of quotation marks.
Select * From Tab1 Where [Date]> #2002-1-1 #;
I used this in DELPHI.
SQL. Add (Format (
'Select * From Tab1 Where [Date]> # % s #;',
[DateToStr (Date)]);
The strings in ACCESS can be separated by double quotation marks, but SQLSERVER does not recognize them. To facilitate migration and compatibility,
We recommend that you use single quotes as the string separator.
Query tables in the ACCESS database in SQLSERVER
SELECT * FROM
OPENROWSET ('Microsoft. Jet. OLEDB.4.0 ',
'C: \ youfile. mdb '; 'admin'; 'youpwd', youtabname)
AS
The following SQL statement passed the test in ACCESS XP Query
Table creation:
Create Table Tab1 (
ID Counter,
Name string,
Age integer,
[Date] DateTime );
TIPS:
Use Counter to declare the auto-increment field.
Fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names.