Solved

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

Posted on 2004-08-17
2
374 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now