delphi里动态创建Access的交叉表视图

const

dbUseJet = $00000002;

var

connection: OleVariant;

DBEngine : OleVariant;

DataBase : OleVariant;

QueryDef : OleVariant;

accessfile: string;

sql: string;

begin

accessfile := 'C:/db1.mdb';

connection := CreateOleObject('Access.Application');

DBEngine := connection.DBEngine;

DBEngine.DefaultType := DBUseJet;

DataBase := DBEngine.OpenDatabase(accessfile);

sql := ' TRANSFORM first(FieldValue) '

+ ' SELECT ContactID FROM [SELECT a.FieldName, b.ContactID, b.FieldID, '

+ ' b.FieldValue FROM CustomField AS a, ContactFieldValue AS b WHERE '

+ ' a.RecordID = b.FieldID]. AS TmpTable GROUP BY ContactID PIVOT FieldName';

QueryDef := Database.CreateQueryDef('V_CustomValue',sql);

connection.Close();

end;

推荐分享