Create a MS Access database programmatically in perl

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

The following is a piece of code I have written to create an MS Access 97 database programmatically using PERL

# !/usr/bin/perl -w

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const;

my $dbname = 'c:/Perlscripts_Conv_Issues/mk-db/new.mdb';

if (-e $dbname) {
   unlink $dbname 
}

   my $catalog = Win32::OLE->new('ADOX.Catalog');
   $catalog->Create('Provider=Microsoft.Jet.OLEDB.3.51;'. 
                    'Data Source='.$dbname) || die "unable to create d
+atabase - 
                    ".Win32::OLE->LastError;
                    
  my $tblNEW = Win32::OLE->new('ADOX.Table');
  $tblNEW->{Name} = "Contacts";
  print "created table \n";
  
          
  $tblNEW->Columns->Append("FirstName", 'adVarWChar');
  $tblNEW->Columns->Append("LastName", 'adVarWChar');
 
  $catalog->Tables->Append($tblNEW);
  $tblNEW =  undef;
  $catalog = undef;
[download]

After executing this code, The Access DB and table is created, but the fields are not created. Can Anyone help me with this? What am i doing wrong?

jsprat - you outlined this solution for me in my previous message. Maybe you can figure this one out? Thanks!

Comment on Create a MS Access database programmatically in perl Download Code

Replies are listed 'Best First'.
Re: Create a MS Access database programmatically in perl by jsprat (Curate) on Jul 07, 2003 at 18:20 UTC
First of all, create an account! If you post regularly, you'll find all kinds of benefits. Most of all, it's fun. As for your question, you aren't importing the constants from the ADOX library - ADO doesn't know what you mean by 'adVarWChar'. Use the object browser (ships with ActiveState, usually installed at \Perl\html\OLE-Browser\Browser.html) to find out which version of ADOX is installed, and add it to the `use Win32::OLE::Const` line. It should look something like this: `use Win32::OLE::Const 'Microsoft ADO Ext. 2.5 for DDL and Security';` Also, check the Win32::OLE docs for `Win32::OLE->LastError()` and the `warn` option (specifically `Win32::OLE->Option(warn => 2); #or 3 or 4`). This should help your debugging. By the way, once the database is created, it would probably be much more straightforward to use the DBI to create your tables. Good luck...	[reply] [d/l] [select]
Re: Create a MS Access database programmatically in perl by Grygonos (Chaplain) on Jul 07, 2003 at 18:01 UTC
MSDN under the provider support for ADOX section of this document you will see that append and delete methods are not supported for ODBC. however, the DAO.TableDefs.Append method in DAO 3.5.1 or greater, will accomplish that task. I haven't tested it .. but its listed in access as a supported method. *UNTESTED* `use Win32::OLE my $app = Win32::OLE->new(Access.Application); my $table = Win32::OLE->new(DAO.TableDef); $app->OpenCurrentDatabase(xxx.mdb); #Returns the DAO database object $database = $app->CurrentDb(); $table->CreateField(myField,Text,255); $database->TableDefs->Append($table);` [download] Hope this helps.	[reply] [d/l]
Re: Re: Create a MS Access database programmatically in perl by jsprat (Curate) on Jul 07, 2003 at 23:01 UTC
under the provider support for ADOX section of this document you will see that append and delete methods are not supported for ODBC. But the OP is not using ODBC, he's using the JET provider for ADO. Look here for the working (Method 2 - tested!) example he's building from.	[reply]