Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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 )
0
dcrudo
Asked:
dcrudo
1 Solution
 
stevbeCommented:
the system tables in Access will not provide enough information for you to recreate your database tables/ queries.

You can use the ADOX Library which provides objects like Catalog, Tables, Columns, Index, etc.
You can also get Read information directly with ADODB by opening a schema type recordset ...

Set rst = mcnn.OpenSchema(adSchemaTables)

where mcnn is your connection object.

see Enum SchemaEnum for the 45 schema types.

You could also read/write schema information using DAO.

Steve
0
 
dcrudoAuthor Commented:
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 ;)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now