Inserting records in MS Access

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

Replies are listed 'Best First'.
Re: Inserting records in MS Access by BigJoe (Curate) on Dec 06, 2000 at 02:47 UTC
This really has no place here but: You can do an insert using <a href=http://search.cpan.org/search?dist=DBI">DBI and a DBD driver form (CPAN) by creating your connection like normal then `#connection creation goes here $dbh->do("INSERT INTO TABLE (COL1, COL2) VALUES('Val1', 'Val2')")or di +e "I failed to insert";` [download] There are more short cuts but this is standard SQL and if you use the DBI module as your interface it should work. (The reason I say should is because when I started in Perl I began to use databases that could handle my scripts ie. MySQL and Oracle). Please go and read the online documentation about DBI and DBD::ADO. There are examples of almost anything that you can do with DBI in there. --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply] [d/l]
Re: Inserting records in MS Access by MeowChow (Vicar) on Dec 06, 2000 at 08:36 UTC
If you're looking for a particularly Microsoft-centric solution, you can connect to the Access database through ADO with Win32::OLE. Here's a snippet from some of my own code that was done using this technique, to give you a feel for what coding in this manner is like: use Win32::OLE; use Win32::ADO; # just a set of constants for ADO my $conn = Win32::OLE->new('ADODB.Connection'); $conn->Open('database'); my $rs = Win32::OLE->new('ADODB.Recordset'); $rs->Open("table", $conn, adOpenDynamic, adCmdTable); for my $item (@items) { $rs->Movefirst; $rs->Find("ItemID = ".$$item{'ItemID'}); if ($rs->{EOF}) { $rs->AddNew(["ItemID", "EndDate", "Relisted", "RelistClass", " +RelistKey"], [$$item{'ItemID'}, $item_endtime, 0, $$item{'Relis +tClass'}, $$item{'Key'}]); } else { $rs->Fields('Relisted')->{Value} = 0; $rs->Update } } [download] You may find that ADO is actually quite a solid and well-designed database access API. The specification for it can be found in MSDN.	[reply] [d/l]
Re: Inserting records in MS Access by 2501 (Pilgrim) on Dec 06, 2000 at 05:23 UTC
I'd recommend connecting with Win32::ODBC and then it is just a matter of knowing your SQL. examples would be: `update <tablename> set <fieldname>=<value> where <conditions> delete from <tablename> where <conditions> or insert into <tablename> (<field1>,<field2>) values(<value1>,<value2>)` [download] that should be enough to get you tinkering with it. A helpful trick for learning is to build your SQL statements with the query generator and choose to view the raw SQL. Also here are a couple of quicky subs you can use to goof with. `sub db_connect { $DSN = "foo"; if (!($db = new Win32::ODBC("dsn=bar;uid=user;pwd=pass"))) { print "Error connecting to $DSN\n"; print "Error: " . Win32::ODBC::Error() . "\n"; exit; } } sub do_query { # reads from a var called $SQL if ($db->Sql($SQL)) { print "SQL failed. \n"; print "Error: " . $db->Error() . "\n"; $db->Close(); exit; } }` [download]	[reply] [d/l] [select]
Re: Inserting records in MS Access by OeufMayo (Curate) on Dec 06, 2000 at 14:13 UTC
I may be wrong, but I recall that there's some difference in the string quoting between Oracle and Access. IIRC, Oracle uses single quotes, as in your code snippet, but Access uses double quotes. <kbd>-- PerlMonger::Paris(http => 'paris.pm.org');</kbd>	[reply]
Re: Inserting records in MS Access by Fastolfe (Vicar) on Dec 06, 2000 at 02:56 UTC
You're probably going to find more information in your database's SQL documentation rather than anything you might get out of Perl or Perl documentation.	[reply]