Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Access to DBISAM
Mon, Mar 4 2013 12:24 PMPermanent Link

Gerald J. Clancy, Jr.

Because of a switch forced upon us by one of our key sources of information
we have to start using an Access database and tables in lieu of the dBase
tables that have been used for over a decade (we convert the data to DBISAM
tables). I never used Access but, so far, have successfully smoked out how
to set connection strings, open the individual ADOTables and even create
indexes (below). However, when switching indexes on an ADOTable with the
normal "IndexName := 'someindex';" I get an exception that says, "Current
provider does not support the necessary interface for Index functionality".
Chasing down this error message has not been particularly useful but, in
doing so, my suspicion is that you have to close the table to switch
indexes. Any edification on this issue would be most welcome, as I suspect
some of you have used Access beflore.

The second curiosity I have noticed is that after creating multiple
secondary indexes they don't seem to be accounted for by the table. First,
some code:

{ Now create the ADO indexes we need }

   { Try creating an new index -- THIS WORKS ...}

   with DMNJ.adoMainbill, DMNJ.ADOCommand1 do begin

     if Active then Close;

     ConnectionString := DMNJ.cNJConnectStr;

     Open;

     { PrimaryKey is [BillType; BillNumber] }

     try

       GetIndexNames(lbxIndexes.Items);

       cs := 'CREATE INDEX LDOA ON Mainbill (LDOA)';

       CommandText := cs;

       Execute;



       cs := 'CREATE INDEX ModDate ON Mainbill (ModDate)';

       CommandText := cs;

       Execute;



       cs := 'CREATE INDEX RevModDate ON Mainbill (ModDate DESC)';

       CommandText := cs;

       Execute;



       lbxIndexes.Clear;

       GetIndexNames(lbxIndexes.Items);

       Close;

     except

       WebApplication.ShowMessage('Index creation failed for MainBill');

       if Active then Close;

       Exit;

     end;

   end; {with}


The GetIndexNames procedure stuffs the names of the indexes for the table in
the listbox. In the first instance the result is, as expecte, only the
PrimaryKey index. I then create three secondary indexes, clear the listbox
and execute GetIndexNames again. Only the PrimaryKey and LDOA (the first
secondary) are listed.

What am I missing? Do I have to close to reset the index? Do I have to use
ADOCommand and an SQL command instead to switch indexes (instead of
IndexName :=)?

Jerry

Mon, Mar 4 2013 6:26 PMPermanent Link

Gerald J. Clancy, Jr.

Still playing with this. Have it working but apparently you have to close
the table before resetting the index. Additionally, I find, you apparently
can't close it with either BOF or EOF set, so I had to go to First before
closing.

I'm beginning to see why no one uses MS Access anymore.

Jerry

"Jerry Clancy" <jclancy@billtrak.com> wrote in message
news:7F718C8C-48A9-4F24-9592-71A9E384E9A4@news.elevatesoft.com...
> Because of a switch forced upon us by one of our key sources of
> information we have to start using an Access database and tables in lieu
> of the dBase
.....
> The GetIndexNames procedure stuffs the names of the indexes for the table
> in the listbox. In the first instance the result is, as expecte, only the
> PrimaryKey index. I then create three secondary indexes, clear the listbox
> and execute GetIndexNames again. Only the PrimaryKey and LDOA (the first
> secondary) are listed.
>
> What am I missing? Do I have to close to reset the index? Do I have to use
> ADOCommand and an SQL command instead to switch indexes (instead of
> IndexName :=)?
>
> Jerry
>
>
Tue, Mar 5 2013 4:51 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jerry,

<< What am I missing? Do I have to close to reset the index? Do I have to
use ADOCommand and an SQL command instead to switch indexes (instead of
IndexName :=)? >>

You're probably going to get better results by asking on the Embarcadero
newsgroups/forums.  I use the TADO* components very infrequently, and then
only in a very superficial manner and usually *not* with MS Access.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Mar 5 2013 9:40 PMPermanent Link

Gerald J. Clancy, Jr.

Thanks, Tim. Actually, I'm getting along quite well and have a major section
of the app working.

Having said that, however, I just hit a snag which on reflection I think is
an SQL issue (I don't "do" SQL). I have a perfectly good indexing operation
that is choking:

 with ADOCommand1 do begin
     cs := 'CREATE INDEX Action ON BillHist (ACTION, BILLTYPE,
BILLNUMBER)';
     CommandText := cs;
     Execute;
  end;

All the three of the necessary fields are there and I suspect the error is
due to the use of the index name 'Action', which may be a reserved keyword,
If anyone can confirm that, I'd appreciate it. Changing the index name to
TheAction didn't help.

Jerry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:AA0D8679-82C9-40AE-8440-6E74CAB75CE2@news.elevatesoft.com...
> Jerry,
>
> You're probably going to get better results by asking on the Embarcadero
> newsgroups/forums.  I use the TADO* components very infrequently, and then
> only in a very superficial manner and usually *not* with MS Access.
>
Tue, Mar 5 2013 10:37 PMPermanent Link

Gerald J. Clancy, Jr.

Solved: Yes, Action is a keyword. This is the escape in Access (brackets):

       cs := 'CREATE INDEX [Action] ON BillHist ([ACTION], BILLTYPE,
BILLNUMBER)';


"Jerry Clancy" <jclancy@billtrak.com> wrote in message
news:BC4430F4-2DA8-4D28-AB51-5FEB646599A5@news.elevatesoft.com...
> Thanks, Tim. Actually, I'm getting along quite well and have a major
> section of the app working.
>
> Having said that, however, I just hit a snag which on reflection I think
> is an SQL issue (I don't "do" SQL). I have a perfectly good indexing
> operation that is choking:
>
>  with ADOCommand1 do begin
>      cs := 'CREATE INDEX Action ON BillHist (ACTION, BILLTYPE,
> BILLNUMBER)';
>      CommandText := cs;
>      Execute;
>   end;
>
> All the three of the necessary fields are there and I suspect the error is
> due to the use of the index name 'Action', which may be a reserved
> keyword, If anyone can confirm that, I'd appreciate it. Changing the index
> name to TheAction didn't help.
>
> Jerry
Image