When the app was originally an access application then it was probably using ADO to access the database. That is a layer you are missing with sqldb.
When you open a table in Access (view a table), on the ODBC layer you will see that it first loads the keys and then executes small selects (typically 10 rows) to fill up the view. It will do it with 'SELECT fields FROM table WHERE key in (key1,....,key10)' queries which is understood by all DB's. Even when you click 'go to last record', iirc, it will only load those records to fill up the screen. This allows to view large tables without loading the complete table. All the keys have to fit in memory though.
When you want to do the same in sqldb, you have to program this algorithm yourself. Since you are new to this I suggest you take an ODBC trace of your access program to better understand what is happening on the ODBC layer. The ODBC trace can be started from the ODBC manager (control panel on windows).
Hi, Ludob,
Thanks for your help. My program is running good after I have used the LIMIT option on the Select command for the WorkSheetGrid. I understand that the concept what you are mentioning regarding the Access on opening a huge DB. However, I am not an experienced SQL guy that I have some questions regarding that technical issue:
1. You said Access will do "select .......where key in (key 1, key 2....key n)". However, on my DB, we don't use any key on it -- can I use the same way to limit the displayed records by Record Number {like an imagine command "Select .... where RecNum in (Rec n.... Rec n + (Max Rec Shown))"}? (assume "Max Rec Shown" is 30, then, it will show the records from current Rec # to next 30 Rec )
2. At the same time, do I need to rewrite the event for the DB Navigator (button events of [Prev Rec], [Next Rec], [First Rec] and [Last Rec])?
Thanks & Regards