Solved

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

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

632 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