Solved

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

Posted on 2004-08-17
2
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

751 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