http://www.perlmonks.org?node_id=957566

Limbic~Region has asked for the wisdom of the Perl Monks concerning the following question:

All,
Normally when I need to interact with an Access DB, I use DBD::ODBC. Unfortunately, $dbh->column_info() doesn't have enough information to recreate the DDL for the table definition and Access doesn't have system tables containing that information.

According to this article, you can get at the information you want if you Use the ADO and ADOX libraries, or the DAO library in VBA instead.

Is that possible from Win32::OLE? If so, how would you go about starting to create a DDL from an existing table? I never shy away from lots of reading but having some sample code would be greatly appreciated.

Cheers - L~R

Replies are listed 'Best First'.
Re: Generate A DDL From Access
by remiah (Hermit) on Mar 03, 2012 at 11:58 UTC

    $dbh->column_info() doesn't provide index, pk information.
    But $dbh->statistics_info() will return it.
    I am using it through DBD::ADO and it seems working.

    ddl like 
    create table test1 (test1 varchar(14));
create index test1_pk on test1 (test1);

    [download]
    creates "tabel1" with primary key and column_info() returns information like this 
    ###Table: test1,
          test1,   VarChar,   14,

    [download]
    And statistics_info() will return index information like this. 
    ###Indeces:
  test1_pk( btree,non unique)
    columns=1:test1

    [download]
    I hope this helps you.

[reply]
[d/l]
[select]
      remiah,
      Grrr. I had a nice long response and lost it.

      Would it be possible to share what you are using for a connection string and a complete copy/paste of what you see for table_info(), column_info() and statistics_info(). I am trying to determine if things like collate, constraints, etc will be available and I am having problems getting a connection working.

      Cheers - L~R

[reply]

        I am not sure about collate. If you find one, please tell me. Here is my code. Method "describe" calls collumn_info() and statistics_info().

        Read more... (6 kB)

        And my quistion: I want to make a package which treats MS access dialect (they treat '*' for '%' and no limit, offset keyword). Package which can do like this. 

        my $db=connect(\%dbinfo) or die $!;
my $sql ="select * from test1 where fld1 like 'monk%' offset 10 limit 
+10";
foreach my $r ( $db->fetch($sql) ){
    print "$r->{fld1}\n";
}
$db->disconnect;

        [download]
        I want to make a package which can easily switch MS Access and other database like SQLite. I am interested in DBIX::Class but I am not sure it is the way to go... If you have some advice for me, I am glad. Any comments, advice, welcome.

[reply]
[d/l]
[select]
Re: Generate A DDL From Access
by Gangabass (Vicar) on Mar 03, 2012 at 00:08 UTC
    I don't know but this may be helpfull.

    Thanks.
    Roman

[reply]

Back to Seekers of Perl Wisdom