Steven,
I would be interested to know why changing the back end is not an
option. Also, I think that returning all of the records to an array is a
bit wasteful from a memory usage perspective. I personally would convert
the DB to MySQL. One of the interesting things about returning a
recordset from MySQL say from a query like:
Dim rs As RecordSet
rs.SQLSelect("SELECT * FROM tablename)
Is that if you did rs.RecordCount it would return -1
MySQL has cached the returned records and provides them as they are
requested from the RB app keeping network traffic and memory usage at a
minimum and it is extremely fast.
For example, your desire to use Navigation buttons (first, prev, next &
last) would actually get the
exact record you want from the recordset cached by MySQL as long as the
connection is open and the recordset is not closed using:
rs.MoveFirst
rs.MovePrevious
rs.MoveNext
rs.MoveLast
There are a lot more advantages to using MySQL or another more powerful
DBMS than Access via ODBC. Upsizing an Access DB to MySQL is very easy
with some of the tools like Intelligent Converter's Access-to-MySQL
which will directly convert an Access DB directly into MySQL.
If you would like more info on converting Access to MySQL, some of the
tools available and some of the configuration issues, you can email me
ether on or off list and I will give more details. I also have some
MySQL Database Methods predefined that are generic so they can be used
over and over for connecting and to populate controls, which I have in a
Demo App that I can make available as well.
My 2 cents
Tom
> This question is one of those "How would you guys do it". I'm
> attempting to replace a MS Access front-end application with
> RB, though
> for the time-being the mdb backend will have to remain. I'm
> using the
> ODBCDatabase class to access the mdb data files.
>
> This application, I suppose is a typical database application
> that has numerous tables (people, companies, exhibits,
> journal, statements etc)
> and numerous linking tables. In accessing and displaying
> the data I'm
> using code (not the data controls). In order to display detail
> records, this is the general process. Upon window opening, retrieve
> all primary key (ie _RowID) fields and store in an array
> (have to do it this way as move first, previous & last
> recordset methods aren't
> available). Upon window opening, by default, the first record is
> displayed. Navigation buttons (first, prev, next & last)
> move in the
> required direction of the array. From the array the primary key is
> retrieved, added to a where clause of an SQL Select statement
> and that particular record retrieved and displayed.
>
> Other than the SQL statements (table & field names) being
> different, nearly everything else is generic, the process of
> navigating, retrieving
> and displaying records. Positions of various editboxes will vary.
> They're are numerous windows to create which all basically do
> the same thing, display data for viewing.
>
> What is the most efficient way to approach this, any advice
> would be appreciated.
>
> Steven Taylor
> Melbourne, Australia.
> _______________________________________________
> Unsubscribe or switch delivery mode:
> <http://www.realsoftware.com/support/listmanager/>
>
> Search the archives of this list here:
> <http://support.realsoftware.com/listarchives/lists.html>
>
>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
|