ACCESS skill set in DELPHI

Source: Internet
Author: User
Tags idate

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.