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

I'm pretty sure this is on-topic. I'm working with an MS Access database, using DBI and DBD::ODBC. I've got a VisualBasic function, SortThis(), in a VisualBasic module, MediaSort, that sorts strings and ignores leading words like "A", "An", and "The". The function is recognized inside MS Access, and the query runs perfectly there, but my Perl code fails to recognize the function. 
my $dbh = DBI->connect("DBI:ODBC:Acacia");

my $media = $dbh->prepare(q{
  select *
  from   MEDIA
  order
      by SortThis(NAME)
});

[download]
When I execute the query, I get DBD::ODBC::st execute failed: (Microsoft)(ODBC Microsoft Access Driver) Undefined function 'SortThis' in expression. (SQL-42000)(DBD: st_execute/SQLExecute err=-1). Does anyone know how to deal with this error? I need the Perl DBI to recognize the module and function defined within the Acacia database.
_____________________________________________________
Jeff[japhy]Pinyan: Perl, regex, and perl hacker, who'd like a job (NYC-area)
s++=END;++y(;-P)}y js++=;shajsj<++y(p-q)}?print:??;

Replies are listed 'Best First'.
Re: DBD::ODBC, Access DB, calling a VB function
by Grygonos (Chaplain) on Feb 19, 2004 at 19:02 UTC
    You can Access it via OLE. But you cannot directly call module code to my knowledge. You can open the module via OLE but you need to call the RunCode action which is native to Access. This is a method of running code from a module using a macro... you must use the RunCode action in the macro and specify your code as the one to run. How ever you cannot pass args to it or get a return value I believe. This is tested code that works 
    #!/perl -w

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

my $accessConst = Win32::OLE::Const->Load('Microsoft Access 8.0 Object
+ Library');


my $app =  Win32::OLE->new('Access.Application') or die "$!";

$app->OpenCurrentDatabase('C:/test.mdb');

$app->DoCmd->RunMacro('Macro1');
$app->DoCmd->Quit($accessConst->{'acQuitSaveNone'});

    [download]
    This works... I had my code run by my macro simply write to a file on my C:\ drive and it worked fine... just poke around the Object browser some more and see if you can find a way to invoke RunCode however I don' think you can.
    Grygonos
[reply]
[d/l]
Re: DBD::ODBC, Access DB, calling a VB function
by rchiav (Deacon) on Feb 19, 2004 at 18:03 UTC
    Something is telling me that you have to declare the function as "Public", as in..

    Public Function SortThis ()

    [download]
    It's been forever since I've written anything in Access though.
[reply]
[d/l]
      Already did that... I'm not sure if there's something I need to tell Perl to use functions from a specific VB module?
      _____________________________________________________
      Jeff[japhy]Pinyan: Perl, regex, and perl hacker, who'd like a job (NYC-area)
      s++=END;++y(;-P)}y js++=;shajsj<++y(p-q)}?print:??;
[reply]

Back to Seekers of Perl Wisdom