asked on

How to get Column properties (type, size) in MS-ACCESS?

Hi!

Is there a way to issue a SQL query with ADO against an Access database so that I can get the size and the type of the column?

The goal is to understand the database structure so that i can create the same database somewhere else
(like a Mysql database) and transfer the data.

Thank you in advance!

(If there is not a query, an ADO property or something... I'm using Delphi 7.0 )

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dcrudo

ASKER

Hi Steve,

Actually i think that you are referring to a VB recordset... I cannot use it with delphi...
but since i found the solution in the JET using a query property... and you are the
only one who replied... I will accept your answer.

My Solution to the problem was to identify the width of the field using:

IntToStr(DataModule1.AdoQuery.Fields[i].DisplayWidth)

and identify the type of the field of a Function found on delphi.about.com: GetFieldInfo(i)

Function TForm3.GetFieldInfo(const i: Integer): String;

Var

ft : TFieldType;
sft : string;
fname : string;
begin
ft := DataModule1.AdoQuery.Fields[i].DataType;
sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
fname:= DataModule1.AdoQuery.Fields[i].FieldName;

Result := sft;

end;

********So my Program looks now like this **********

for i:= 0 to DataModule1.AdoQuery.Fields.Count - 1 do
begin
ColType := '';

if GetFieldInfo(i) = 'ftWideString' then ColType := 'varchar (' + IntToStr(DataModule1.AdoQuery.Fields[i].DisplayWidth)+ ')';
if GetFieldInfo(i) = 'ftFloat' then ColType := 'double NOT NULL';
if GetFieldInfo(i) = 'ftDateTime' then ColType := 'date';
if GetFieldInfo(i) = 'ftAutoInc' then ColType := 'INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ('+DataModule1.ADOQuery.Fields[i].DisplayName + '), INDEX idx.' + DataModule1.ADOQuery.Fields[i].DisplayName + ' (' + DataModule1.ADOQuery.Fields[i].DisplayName + ')';
if GetFieldInfo(i) = 'ftMemo' then ColType := 'text';
if GetFieldInfo(i) = 'ftInteger' then ColType := 'INT NOT NULL';

if ColType = '' then showmessage('Column Type Error for ' + DataModule1.ADOQuery.Fields[i].DisplayName);

Columns := columns + ', `' + DataModule1.ADOQuery.Fields[i].DisplayName + '` ' + Coltype;
PlainColumns := PlainColumns + ', `' + DataModule1.ADOQuery.Fields[i].DisplayName + '` '

end;

*************************

I hope it will help somebody anyway ;)