trouble creating table with a field of type MEMO i

Question

perl 5.8.9

Hello folks,

I am successful in using DBI/DBD/ODBC to create a new table in an existing Access 2003 (.mdb) database with the following string: $q = "CREATE TABLE memoTestA (name TEXT, addr TEXT)"; # works

but I can't figure out how to add a field of type MEMO ie $q = "CREATE TABLE memoTestB (name TEXT, addr TEXT, desc MEMO)"; # throws syntax error

There must be a way - what is the trick?

switching form using DBD::ODBC to Win32::OLE gets me past the "bad syntax" error thrown by the ->Execute stmt, but I do not see the table actually created within the Access database. Hm.

#-------------------------------
sub setUpDatasourceLocalAccessC($)
{ my $debug = 0;
  my ( $dbFile) = @_;

  #Choose appropriate version of Jet for your system
  my $Jet = Win32::OLE->CreateObject('DAO.DBEngine.36') or die "Can't create Jet database engine.";

  my $dbh = $Jet->OpenDatabase( $dbFile);
  return $dbh;
 }
#-------------------------------
sub createMemoTablePrepareC($)
{
  my ($dbh_vdms) = @_;

  print "entering createMemoTablePrepareC\n" if $debug;

  my $errorHit = 0;
  my @table_arry = ();
  my @goodtable_arry = ();

  my $q   = undef;
  if( !defined( $dbh_vdms)) {
    _pushErrorMsg("db connection failed - ".$DBI::errstr);
    $errorHit = 1;
  } else {
    $q = "CREATE TABLE memoTestC (name TEXT, addr TEXT)";    # works
    $q = "CREATE TABLE memoTestC (name TEXT, addr TEXT, desc MEMO)";  #seems to work, no errmsg but dont see table appear in database
    $dbh_vdms->Execute( $q);
    if( my $errorMsg = $dbh_vdms->errstr ) {
      _pushErrorMsg("create statement failed - ".$errorMsg);
      $errorHit = 1;
    }
    $dbh_vdms->close();
    $dbh_vdms->disconnect();
  }

  print "exiting  createMemoTablePrepareC with errorHit=".$errorHit."\n" if $debug;
  return $errorHit;
 }

TIA,

Still-learning Steve

Answer 1

Do not call a column "desc" as it is a reserved word. If you want to keep the column name you'll have to quote it e.g., create table fred ([desc] MEMO).