Well, I don't know whether you're using ODBC or ADO to connect from DBI to MS Access, but I can tell you from experience with ODBC: MS Access recognizes a different SQL dialect over ODBC, than its own bastardized SQL in for example Query Builder. In the latter, you quote field names and table names by putting square brackets around them; in ODBC, you're expected to use the SQL-92 standard double quotes.

select * from "desc"
[download]

ok, there's progress thanks to you. I'm using ODBC. Quoting field names does work indeed.

There still remain one problem; the query returns only a handfull of rows instead of the 2000 expected. Which brings me back to this 'illegal character' thing I was refering to in my first post.

As the data fetching occurs, something must go wrong somewhere and stop the process. I'll have to identify the offending column.

Can you explain why trying to order the result set fails?

$query = $dbh->column_info('','',$table,'');
$column_info = $query->fetchall_arrayref();
for $i ( 0 .. $#{$column_info} ) {
   $fields .= qq| "$column_info->[$i][3]",|
}
chop $fields;
$sql = qq|SELECT $fields FROM $table ORDER BY "REF"|;
$query = $dbh->prepare($sql) or die "could not prepare $sql : $DBI::er
+rstr\n";
$query->execute  or die "could not execute $sql  : $DBI::errstr\n";
$result = $query->fetchall_arrayref();
[download]

This returns nothing. And does not produce any error neither.

H

I fail to see the point of using column_info to obtain all column names then using them in the select - surely that is "select *". Try to simplify the sql and code you are using to locate the problem. Start by changing the SQL to qq/select * from $table ORDER by "REF"/ and if that does not work check the $table and quoting of $table etc. Try to find the smallest uncomplicated SQL which works and the smallest change that stops it working.

I don't really know what's going on, but I do get the impression that something is crashing: be it MS Access, ODBC itself, or DBD::ODBC.

This appears to be a bit out of our league, perhaps you should take this to the DBI-users mailing list.

Didn't you say just select * from mytable order by "REF" is also problematic? Doesn't that imply that the column REF is, at the very least, part of the problem?

To test the other columns, I wouldn't try them fetching them all at once, but instead, one at a time, each in its own query, and see which queries produce problems.

On MS Access 2002/2003 I type a request like this : SELECT Table.field FROM Table; You may try it...Good luck

Using desc for the field name is not a good idea because this is SQL reserved keyword. But i have no idea why SELECT * FROM table doesn't work. Can you post your code here?

Actually the field name is desc1. ok, here's the code:

use CGI qw(:all);
use DBI;
use Data::Dumper;
use strict;
use warnings;

my $dbh = DBI->connect('dbi:ODBC:boutique', {RaiseError => 1, PrintErr
+or => 1} ); # data source set up in /etc/odbc.ini
my $fields = "";
my $table = "articles";
my $sql;
my $query;
my $column_info;
my $result;
my ($i, $j);

# here i'm getting all field names
# leaving out the ones that seem to be
# incompatible

$query = $dbh->column_info('','',$table,'');
$column_info = $query->fetchall_arrayref();
for $i ( 0 .. $#{$column_info} ) {
   if ($column_info->[$i][3]  !~ /[\s-]/) {
      $fields .= " $column_info->[$i][3],";
   }
}
chop $fields;

#$sql = "SELECT $fields FROM $table";
$sql = "SELECT * FROM $table";
$query = $dbh->prepare($sql);
$query->execute;
$result = $query->fetchall_arrayref();
#print Dumper($result);
for $i ( 0 .. $#{$result} ) {
   for $j ( 0 .. $#{$result->[$i]} ) {
      if ($result->[$i][$j] eq ""){
         print qq~<b>NULL</b>~;
      } else {
         print qq~$result->[$i][$j]~;
      }
   }
   print qq~\n~;
}

$query->finish();
$dbh->disconnect();
[download]