Perl: the Markov chain saw PerlMonks

### accessing MS Access databases

by memo.garciasir (Acolyte)
 on Oct 15, 2010 at 21:04 UTC Need Help??
memo.garciasir has asked for the wisdom of the Perl Monks concerning the following question:

Hi

I'm programming an interface that can manipulate data in a MS Access database. For that, I'm using use Win32::OLE.

It works fine until I try to insert a quite large number of records, more than 100K. It takes a 2-3 minutes inserting them one by one (foreach loop). The same action take a couple of seconds using the import table from file option in the MS Access program

Someone can teach me the way of doing a bulk insert?

thanks

memo

Replies are listed 'Best First'.
Re: accessing MS Access databases
by BrowserUk (Pope) on Oct 16, 2010 at 01:28 UTC

Warning: This is completely untested. I don't have MS Access, and I never use Win32:OLE, but at least I know what it is.

I found the VBA script (emdeded in POD in the program below), to do this here, and I've made an attempt to convert is to Perl/OLE to give you a starting point. (But don't ask me questions cos I won't know the answers. Well, you can try, but you've been warned:)

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

=VBA
Dim accessApp as Access.Application
Dim strDB as string
Dim strImport as string

set accessApp = new Access.Application
strDB = "C:\Test\MyDatabase.mdb"
accessApp.OpenCurrentDatabase strDB
accessApp.DoCmd.TransferText acImportDelim, , "TableX", strImport, -1
accessApp.CloseCurrentDatabase
=cut

## connect to Access
my $access = Win32::OLE->new 'Access.Application' ) or die "Cannot create Access object:$!\n";

## connect to the database
$access->OpenCurrentDatabase( 'x:\The\path\to\your\database.mdb'); ## run the import command$access->DoCmd->TransferText(
',',                                            ## The delimiter
0,                ## First line contains field names. (1 if yes)
'YourTableName',           ## The name of the table to import to
'x:/the/path/to/the/file.csv',                 ## file to import
-1                       ########### Haven't a clue what this is,
## but you should be able to work it out by looking at the
) or die $^E; ## and done$access->Quit();
[download]

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.

I tested this script and — surprise! — it works.

#!perl

use strict;
use warnings;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Access';

my $database = 'D:\Database.accdb'; my$csv_file = 'D:\Data.csv';
my $tbl_name = 'Data'; my$db = Win32::OLE->new('Access.Application')
or die "Cannot create Microsoft Access object\n";

$db->OpenCurrentDatabase($database);

$db->DoCmd->TransferText(acImportDelim, '',$tbl_name, $csv_file, -1);$db->Quit();

exit 0;
[download]

It's remarkably fast, too.

The fifth argument to DoCmd->TransferText is the HasFieldNames parameter. -1 is True, which means the first line of the CSV file is a header.

The missing error checking is on-account-of-because I haven't figured it out yet. But I swear this script is slurping data into an Access database — quickly!

++BrowserUk doesn't have to get on a plane like I do.

Re: accessing MS Access databases
by sundialsvc4 (Abbot) on Oct 16, 2010 at 00:02 UTC

Granted, I do not understand in detail (yet...) the approach that you are taking, but when you refer to “OLE” instead of a DBI (ODBC...) database driver, well, I rather immediately become nervous.   You do not need to approach MS-Access, the Application, when you can directly approach Jet, the Database Engine.

Nevertheless, your experience is not atypical.   Many database engines (and I do not recall if Jet is of them) provide “fast track” methods for importing large amounts of data from predictably-formatted files.   If they do so, they probably provide a non-standard way to do this in SQL.   (Microsoft, on the other hand, provides a “flat file” database driver, which is a very nifty trick.)

While the completion-times that you are experiencing with your present approach are by no means “a show-stopper,” I agree that they suggest that you have not yet found the most appropriate strategy yet.   “Think outside the box,” and even (if appropriate), “think outside the Perl.”

Please give us all more details: of what you are doing, and of what you find.

I thought it was pretty clear sundialsvc4 knows what OLE is. Hence the advice about addressing the engine via DBI rather than the application via OLE.
Re: accessing MS Access databases
by Jim (Curate) on Oct 16, 2010 at 01:25 UTC

(I'm about to get on a plane, so I have to be brief. Sorry.)

Search Google for the keywords DoCmd and TransferText. Hopefully, this will get you closer to your objective than you are now.

Create A New User
Node Status?
node history
Node Type: perlquestion [id://865566]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2017-05-22 22:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
My favorite model of computation is ...

Results (174 votes). Check out past polls.