Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Create a MS Access database programmatically in perl

by Anonymous Monk
on Jul 07, 2003 at 16:31 UTC ( #272019=perlquestion: print w/ replies, xml ) Need Help??
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;
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
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);
    Hope this helps.
      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.

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...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://272019]
Approved by TStanley
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2014-12-18 02:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (41 votes), past polls