Data Control Using an Index 
Author Message
 Data Control Using an Index

Quote:

>Howdy Team!  I sure could use some quick assistance.  
>I've done several projects with VB3.0 and database access.  I'd really
>like to use the data control but I haven't found a way to set the index
>of the table.  I end up coding the stuff which is fine and dandy and it  
>works but I'd like to use the Data Control if possible and eliminate some
>hefty typing.
>The VB Help gives the following example which, of course, doesn't work:
>Data1.RecordSource = "Publishers"
>Data1.Database.TableDefs("Publishers").Index = "PrimaryKey"
>Data1.Refresh
>When I use this exact code (substituting Customer for Publishers and
>Cust_Idx1 for PrimaryKey), I get an error on the Index, telling me I have
>an invalid property.  ARRRGH!
>The idea is to set the Index to Alpha at Form Load time and allow the user
>to switch back and forth from Alpha to Customer Number order.
>If anybody can give me a clue, I'd sure appreciate it!!  Thanks.

I think you have to requery the data for each sort method as long as
you want to use the datacontrol.

'Sort by Customer Number...
Data1.RecordSource = "SELECT * FROM [Customer] ORDER BY [Cust_Idx1]"
Data1.Refresh

'Sort by Alpha...
Data1.RecordSource = "SELECT * FROM [Customer] ORDER BY [Alpha]"
Data1.Refresh

It isn't THAT much work...

Dag.



Wed, 24 Dec 1997 03:00:00 GMT  
 Data Control Using an Index
Howdy Team!  I sure could use some quick assistance.  

I've done several projects with VB3.0 and database access.  I'd really
like to use the data control but I haven't found a way to set the index
of the table.  I end up coding the stuff which is fine and dandy and it  
works but I'd like to use the Data Control if possible and eliminate some
hefty typing.

The VB Help gives the following example which, of course, doesn't work:

Data1.RecordSource = "Publishers"
Data1.Database.TableDefs("Publishers").Index = "PrimaryKey"
Data1.Refresh

When I use this exact code (substituting Customer for Publishers and
Cust_Idx1 for PrimaryKey), I get an error on the Index, telling me I have
an invalid property.  ARRRGH!

The idea is to set the Index to Alpha at Form Load time and allow the user
to switch back and forth from Alpha to Customer Number order.

If anybody can give me a clue, I'd sure appreciate it!!  Thanks.

Bob



Wed, 24 Dec 1997 03:00:00 GMT  
 Data Control Using an Index
Bob,

I don't have the exact syntax in front of me, but here's
how it works. Despite what the manual says, there is no
index property for a data control.  The only way use the
index (indirectly) is to use an sql query for the
underlying dynaset (the recordset property) of the data
control.  In otherwords, instead of "Customers" as the
recordset, use "select * from Customers order by custid".  
If you have an index on custid, the jet engine will use the
index to sort the records.  If not it will do a normal
sort.



Wed, 24 Dec 1997 03:00:00 GMT  
 Data Control Using an Index

Quote:
>I've done several projects with VB3.0 and database access.  I'd really
>like to use the data control but I haven't found a way to set the index
>of the table.  I end up coding the stuff which is fine and dandy and it  
>works but I'd like to use the Data Control if possible and eliminate some
>hefty typing.
>The VB Help gives the following example which, of course, doesn't work:
>Data1.RecordSource = "Publishers"
>Data1.Database.TableDefs("Publishers").Index = "PrimaryKey"
>Data1.Refresh
>When I use this exact code (substituting Customer for Publishers and
>Cust_Idx1 for PrimaryKey), I get an error on the Index, telling me I have
>an invalid property.  ARRRGH!

The Data Control is a special kind of Dynaset. You cannot set an Index
for a Dynaset with the syntax above (despite what we think
the manual says). You can however set the Index like this if you open
the Database with the OpenDataBase & OpenTable commands (not using
the data control).

To set the Index with the data control, use 'Order By'in an SQL statement.
Something like:
  Select * from MyData Order by MyIndex

--

Dennis Gellert                                  |\      _,,,---,,_
Telecom Research                          ZZZzz /,`.-'`'    -.  ;-;;,_
Melbourne, Australia                           |,4-  ) )-,_. ,\ (  `'-'
                                              '---''(_/--'  `-'\_)   fL



Fri, 26 Dec 1997 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. selecting index with data control using vb3 standard

2. Using Indexes with Data Control

3. Using index with adodc data control.

4. Using VB5 to import data to access without using the Data control

5. Using ADO Data Control on User Control as Data Source

6. Create User Control as Data Source Using ADO Data Control

7. How to add index without using the Data Manager

8. Using indexes with the Data Manager with VB 3.0 Pro

9. How to insert data from un-indexed tabel to indexed table

10. updating Clipper indexes via data control ??

11. Updating indexes of Clipper files with data control

12. Data control locks Access tables with indexes

 

 
Powered by phpBB® Forum Software