PLEASE HELP BROTHERS
I have been requested to write a Perl script that inserts new tables or values into an MS Access 2000 database, the object is to have a database containing servers and their drive information (for eg: Server1001 has 4 drives – C$, D$, E$ and F$, information in regards to the size and free space for each drive are required). I started in MS-Access by creating a table, which I called servers_tbl, using the tables wizard. The table contains 2 field only, the index key field and the server name. Next, I created drives_tbl, which contains the following fields: drv_letter, total_disk_size, free_space_remaining. By means of the wizard I created a relationship with servers_tbl in a one-to-many relationship (i.e based on my understanding that one server entry can have a number related drives with their size and space information). The following code works only as far as inserting entries into the servers_tbl and inserts drive information into the drives_tbl also but as a separate non-related table. When I checked back in MS-Access, in the servers_tbl, I can saw a plus sign ‘+’ in the very first column (before the Index key ‘auto counter’ column) which indicates to me that each servers_tbl has a sub table associated with it which contains the drive information. My question is : How can I insert data into those sub record/tables while still maintaining the relationships? Your enlightenment is highly appreciated and a simple example can go a long way brothers. And I have looked around faq section and did a search on ms access and read most of the stuff there (this is how I managed to get this far)
use strict;
use warnings 'all';
use Win32;
use Win32::ODBC;
use Win32::NetAdmin;
my $dsn = "SRV_DRV";
my $srv_tbl = "servers_tbl";
my $drives_tbl = "drives_tbl";
my $perms_db = new Win32::ODBC($dsn) || die "\nError=>\t$^E : $!\n";
print "\nError:  $^E\n" if (! $perms_db);
if ( $perms_db->Sql("SELECT * FROM $srv_tbl")){
print "Error!\n";}
else{
    my $srv = shift @ARGV || Win32::NodeName();
    print "\nProcessing Server: '$srv'\n";
    $perms_db->Sql ("INSERT INTO $srv_tbl (Server_Name) VALUES ('$srv'
+)");
    if (Win32::NetAdmin::GetServerDisks($srv, \ my @disks))    {
        print "\nAvailable logical drives\n";
        foreach my $disk (@disks){
            next if ($disk =~ /a|z/i );
            $disk =~ s/:/\$/;
            print "Drive\t$disk\n";
            if ( $perms_db->Sql("SELECT * FROM $drives_tbl")){
                print "Error!\n"}
            else{
$perms_db->Sql ("INSERT INTO $drives_tbl (drive_letter) VALUES ('$disk
+')");    }
        }}
    else{
        print "\n\nError! $^E:$!.\n"; }}
$perms_db->Close( );

[download]

In reply to Inserting values into MS Access sub tables by blackadder

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.