Read MS Access Memos with Win32::OLE ADODB

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

I am needing to read Memo fields from an MS Access database. Previous research has recommended that I not use ODBC as there is a 255 character limit. In a discussion on writing memo fields Simon.Proctor (Jan 6, 2002) recommended using OLE as the perferred method to write. I'm trying his OLE approach for reading. I don't get very far. My code is:

#!perl -w
use strict;
use Win32::OLE;

use constant adUseClient => 3;
use constant adOpenKeySet => 1;

my $db_connection = new Win32::OLE('ADODB.Connection');
my $rs = new Win32::OLE("ADODB.Recordset");
my $db_datasource = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=I:\g
+arbage\quarterback.mdb;';

# Connect to the database and tie the recordset object to the database
print "Data Source = $db_datasource\n";
$db_connection->open($db_datasource);

# Set the connection doohicky
$rs->CursorLocation(adUseClient);

$rs->Open('SELECT Item_ID, Item_Text, Answer_Text FROM Quarterback',$d
+b_connection, 0, adOpenKeySet);

while ( ! $rs->Eof )
{
   $rs->MoveNext;
} ## end of while (! $rs-Eof )

$rs->close;
[download]

I have fixed my dumb mistakes and got to the place where I was getting an error message 'error 0x8002000e: 'Invalid number of parameters" ... CursorLocation' How many parameters is CursorLocation supposed to have? I thought there was only 1 parameter. I know there is only one in the VBScript implementation of ADODB. I can live with that error as it is not fatal. However, once inside the loop I can progress through the database using $rs->MoveNext, but I can't seem to find a way to get the data out of the record set. In VBScript I use rs.Fields("<field name>") (or in extended syntax rs.Fields("<field name>").value) to get my value of the field from the record set. I can seem to figure out the parallel syntax in Perl. Help! I've got a Monday morning deadline and child care that I'm doing all weekend. Thanks so very much

Comment on Read MS Access Memos with Win32::OLE ADODB Select or Download Code

Replies are listed 'Best First'.
Re: Read MS Access Memos with Win32::OLE ADODB by Grygonos (Chaplain) on Sep 05, 2003 at 20:12 UTC
Here is an example of reading a recordset via ADODB. I got this from a geosh*tties cite Read more... (1009 Bytes) It uses the fields method you mentioned from VBScript.	[reply] [d/l]
Re: Read MS Access Memos with Win32::OLE ADODB by jand (Friar) on Sep 06, 2003 at 05:33 UTC
CursorLocation is a property, not a method. You should set it like this: $rs->{CursorLocation} = adUseClient; Accessing fields would look like print $rs->Fields("<field name>")->Value, "\n"; With Win32::OLE you always have to call the default method explicitly; it is not called automatically like VBScript does it.	[reply]