Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: Anyone know how to loop through column names

    I am trying to use a script to connect to an access database, take all filed names and create a new mdb with the field names in a column and another column that if the fields in table1 contain nulls or zeros then "N" otherwise "Y". I have quite a few tables with a lot of fields, so i would like to be able to do this without writing out each field name.

    So basically i need to connect to a .mdb, scan through and fetch the field names and put them in field1 in table2. then go back to table one and if there is a 0 or null then assign a N to tables2 in field2 otherwise Y
    Last edited by bosewicht1; 09-08-04 at 20:58.

  2. #2
    Join Date
    Mar 2004
    Posts
    114
    The script below helps you to fetch the fields required.
    Code:
    use DBI;
    
    # Connection ADO
    my $dsn = 'Driver={Microsoft Access driver (*.mdb)};dbq=D:\\Folder\\database.mdb';
    my $dbh = DBI->connect("dbi:ADO:$dsn")
            or die "Impossible connection: $DBI::errstr";
    
    # Execute simple query on table
    my $sth = $dbh->prepare("SELECT * FROM table")
            or die "Can't prepare statement: $DBI::errstr";
    
    my $rc = $sth->execute
            or die "Can't execute statement: $DBI::errstr";
    
    # fetch result in hash ref
    while ( $ref_hash = $sth->fetchrow_hashref ) {
    
    ...
    
    }
    
    # Print the fields
    foreach $key (%$ref_hash) {
             print "$key\n";
    }
    
    # Deconnection
    $rc  = $dbh->disconnect;
    After, you just have to create your new mdb, and create the table with values fetched.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •