Mega Code Archive
Exporting a dataset to Access using ADO
<![CDATA[
Title: Exporting a dataset to Access using ADO
Question: Many times it is necessary to make databases available in a different format. In particular, exporting to Access is a very common request. How to easily export a TDataset to Access?
Answer:
Exporting a TDataset to ADO is very easy, and requires no deployment if you can assume the user has MDAC 2.1 or later installed (Windows 2000 does, for example).
If the user does not have it, it can be downloaded from
http://www.microsoft.com/data.
(Note that in MDAC 2.6 the Jet drivers are no longer included and must be downloaded separately - there is a MS KB article about this at http://support.microsoft.com/support/kb/articles/Q271/9/08.ASP)
Essentially, you can divide the process in three parts:
1) Creating the MDB
That is very easy to do using the ADOX.Catalog object, for example:
cat:=CreateOleObject('ADOX.Catalog');
cat.Create(ConnectionString,[filename]));
2) Creating the tables
That can be done using ADOX.Table, ADOX.Table.Columns.Append and ADOX.Catalog.Tables.Append.
Note that the included unit and demo has less than optimal fields - I still need to work on this. Note also that usable fields vary from ADO provider to ADO provider and between Jet Versions. The unit does work for the simplest field types.
3) Exporting the data. That is trivial for simple data types, you just have to open a connection, a recordset and loop through the source data.
The following unit works on JET 4.0 drivers - note that you could also export to DBASE and Paradox (levels 3,4 and 5 on MDAC 2.5), for example, by using the proper ODBC drivers through ADO.
======================UNIT ExportAdo=======================
unit exportado;
interface
uses
sysutils,ComObj,db;
Procedure CreateTable(tbName,DBName:string;DSFrom:TDataset);
procedure ExportTable(tbName,DBName:string;DSFrom:TDataset);
procedure CreateDatabase(FName:string);
implementation
const
adCmdUnspecified = $FFFFFFFF;
adCmdUnknown = $00000008;
adCmdText = $00000001;
adCmdTable = $00000002;
adCmdStoredProc = $00000004;
adCmdFile = $00000100;
adCmdTableDirect = $00000200;
adLockUnspecified = $FFFFFFFF;
adLockReadOnly = $00000001;
adLockPessimistic = $00000002;
adLockOptimistic = $00000003;
adLockBatchOptimistic = $00000004;
adOpenUnspecified = $FFFFFFFF;
adOpenForwardOnly = $00000000;
adOpenKeyset = $00000001;
adOpenDynamic = $00000002;
adOpenStatic = $00000003;
adEmpty = $00000000;
adTinyInt = $00000010;
adSmallInt = $00000002;
adInteger = $00000003;
adBigInt = $00000014;
adUnsignedTinyInt = $00000011;
adUnsignedSmallInt = $00000012;
adUnsignedInt = $00000013;
adUnsignedBigInt = $00000015;
adSingle = $00000004;
adDouble = $00000005;
adCurrency = $00000006;
adDecimal = $0000000E;
adNumeric = $00000083;
adBoolean = $0000000B;
adError = $0000000A;
adUserDefined = $00000084;
adVariant = $0000000C;
adIDispatch = $00000009;
adIUnknown = $0000000D;
adGUID = $00000048;
adDate = $00000007;
adDBDate = $00000085;
adDBTime = $00000086;
adDBTimeStamp = $00000087;
adBSTR = $00000008;
adChar = $00000081;
adVarChar = $000000C8;
adLongVarChar = $000000C9;
adWChar = $00000082;
adVarWChar = $000000CA;
adLongVarWChar = $000000CB;
adBinary = $00000080;
adVarBinary = $000000CC;
adLongVarBinary = $000000CD;
adChapter = $00000088;
adFileTime = $00000040;
adDBFileTime = $00000089;
adPropVariant = $0000008A;
adVarNumeric = $0000008B;
CCreateMDB='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s';
Procedure CreateTable(tbName,DBName:string;DSFrom:TDataset);
var
tb:Variant;
procedure AppendAllFields(oldtb:TDataset;newtb:Variant);
var
i:Integer;
nome:string;
fieldtype:Variant;
datasize:Integer;
begin
oldtb.Open;
for i:=0 to oldtb.FieldCount-1 do begin
datasize:=0;
nome:=oldtb.Fields[i].FieldName;
case oldtb.Fields[i].DataType of
ftString : begin
FieldType:= adWChar;
datasize:=oldtb.Fields[i].Size;
end;
ftSmallInt: begin
FieldType:= adSmallInt;
end;
ftInteger : begin
FieldType:= adInteger;
end;
ftWord : begin
FieldType:= adUnsignedSmallInt;
end;
ftBoolean : begin
FieldType:= adBoolean;
end;
ftFloat : begin
FieldType:= adSingle;
end;
ftCurrency: begin
FieldType:= adCurrency;
end;
ftBCD : begin
FieldType:= adDecimal;
end;
ftDate : begin
FieldType:= adDate;
end;
ftTime : begin
FieldType:= adDBTime;
end;
ftDateTime: begin
FieldType:= adDate;
end;
ftBlob : begin
FieldType:= adLongVarBinary;
end;
ftMemo : begin
FieldType:= adLongVarChar;
DataSize:= 1000;
end;
ftAutoInc : fieldtype:= adInteger;
else
Continue;
end;
if datasize0 then
tb.Columns.Append(nome,fieldType,datasize)
else
tb.Columns.Append(nome,fieldType);
end;
end;
var
cat:variant;
begin
Cat:=CreateOleObject('ADOX.Catalog');
cat.ActiveConnection:=Format(CCreateMDB,[DBName]);
tb:=CreateOleObject('ADOX.Table');
tb.Name:=TbName;
AppendAllFields(dsFrom,tb);
cat.Tables.Append(tb);
tb:=Unassigned;
end;
procedure CreateDatabase(FName:string);
var
cat:Variant;
begin
cat:=CreateOleObject('ADOX.Catalog');
cat.Create(Format(CCreateMDB,[fname]));
end;
procedure ExportTable(tbName,DBName:string;DSFrom:TDataset);
var
i:Integer;
Con,Rs:OleVariant;
begin
Con:=CreateOleObject('ADODB.Connection');
Con.Open(Format(CCreateMDB,[Dbname]));
Rs:=CreateOleObject('ADODB.RecordSet');
Rs.Open(tbName,con,adOpenKeyset,adLockOptimistic,adCmdTableDirect);
DsFrom.Open;
Dsfrom.First;
while not dsFrom.Eof do begin
Rs.AddNew;
for i:=0 to dsFrom.FieldCount-1 do begin
Rs.Fields.Item[i].Value:=dsFrom.Fields[i].AsString;
end;
Rs.Update;
DsFrom.Next;
end;
Rs.Close;
Con.Close;
end;
end.
======================END UNIT ExportAdo=======================
The following exports some of the DBDEMOS tables:
===============================Sample Export=======================
procedure TForm1.Button1Click(Sender: TObject);
var
dbname:string;
begin
table1.open;
table2.open;
table3.open;
begin
dbname:='teste.mdb';
if FileExists(dbname) then
DeleteFile(dbname);
CreateDatabase(dbname);
CreateTable('country',dbname,table1);
CreateTable('customer',dbname,table2);
CreateTable('employee',dbname,table3);
ExportTable('country',dbname,table1);
ExportTable('customer',dbname,table2);
ExportTable('employee',dbname,table3);
Beep;
ShowMessage('Database exported to '+dbname);
end;
end;
=============================End Sample Export=======================
You can also get the sample project at http://www.stgsys.com, Downloads-Programming page, which includes both ExportAdo and a sample project.
This method adds just a few K to your project (on my last one, only 4K), and it doesn't require MDAC if you don't export. I'd also like to point out that I don't use ADO a lot, so this is probably not the fastest way, but it is fast enough for most uses.
]]>