Solved

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

Posted on 2004-08-17
2
398 Views
Last Modified: 2012-06-22
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
Comment
Question by:dcrudo
2 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 200 total points
ID: 11819008
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
 
LVL 1

Author Comment

by:dcrudo
ID: 11879295
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question