Re: FW: Create database

[Simon Oliver]

"Moulder, Glen" wrote:
> 
> I'm still having a problem with creating the Access database using OLE and wondered 
>if you might have some insight.
> 
> The following code snippet
> 
> $obj = Win32::OLE->new('Access.Application','Quit');
> print "connected to Access...\n"; $obj->NewCurrentDatabase($someDB);
> print "created database $someDB\n";
> $obj->Quit();
> 
The second argument to Win32::OLE->new should be "a CODE reference or a
string containing an OLE method name" - the problem is with calling Quit,
instead use `undef $obj` - this will only Quit if necessary, i.e., ifyour
code should work, but 

Your other problem is that you need to 'CloseCurrentDatabase' before doing
any work on it.

Actually, there is an alternative to using NewCurrentDatabase - we can use
the DBEngine->CreateDatabase method instead (included in the code as
commented lines) - we can even bypass the Access Application object
altogether and use DAO directly:

$dbe = Win32::OLE->new('DAO.DBEngine.36', 'Quit') 
  or die "Oops, cannot start DAO.DBEngine.36";

Attached is some sample code (and a little test to prove it).

--
  Simon Oliver

#!perl
#<!-- create_mdb.pl - [EMAIL PROTECTED], 2002-02-11 -->

use warnings;
use strict;

my $file='c:\test.mdb';
unlink $file if (-e $file);

use Win32::OLE;
my ($app, $dbe, $wrk, $locale);

$app = Win32::OLE->new('Access.Application', 'Quit')
        or die "Oops, cannot start Access!\n";

$app->NewCurrentDatabase($file);
$app->CloseCurrentDatabase;

#$dbe = $app->DBEngine;
#$wrk = $dbe->Workspaces(0);
#$locale = ';LANGID=0x0409;CP=1252;COUNTRY=0';
#$wrk->CreateDatabase($file, $locale);

undef $app;

print -e $file ? "Database created\n" : "Error\n";

use DBI;
my ($driver, $dsn, $dbh, $sth, $attr, $sql);

$driver = 'Microsoft Access Driver (*.mdb)';
$dsn = "driver=$driver;dbq=$file";
$attr = {PrintError=>0, RaiseError=>1};

$dbh = DBI->connect("dbi:ODBC:$dsn", , , $attr);

$sql = q{CREATE TABLE TestTable (id INTEGER, val VARCHAR(64), CONSTRAINT pk
PRIMARY KEY (id))};
$dbh->do($sql);

$sql = q{INSERT INTO TestTABLE VALUES (?, ?)};
$sth = $dbh->prepare($sql);

my %data = (1=>'Albinoni', 2=>'Bach', 3=>'Chopin', 4=>'Dvorjak',
5=>'Elgar');

while (my @data = each %data) { $sth->execute(@data) }

$sql = q{SELECT * FROM TestTABLE};
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->dump_results;

$dbh->disconnect;