ADO and Access 97

[petermeachem]

I'm trying to convery part of a programme that uses Access 97 as the fron and SQL 7 as the back to ADO cos everyone says it is much faster.
I am however stuck

I have:-
csql = "SELECT DISTINCT RouteNumber FROM [Order Entry Table] WHERE Transmit = #" & Format(vSetDate, "dd/mmm/yyyy&quot & "#"
rs1.Open csql, cnn, adOpenDynamic, adLockOptimistic

lstRoutes.RowSource = rs1.???

lstRoutes is a listbox. How can I make the rowsource some function of the ado recordset?

Or alternatively, how do I populate a listbox/form with data using ado?

 

[Dodgey]

Hi Peter,

Is performance really a necessary issue for the product. If the application is already in production, my advice would be not to migrate it's data acess technology unless it was imperative. Hence the saying.."If the horse ain't broke, don't fix it!"

Active Data Objects are faster but they require a bit more manual manipulation. I don't know how Access 2000 confronts this (with all it's internet drive) but I'm sure Bill would have though about underlying ADO recordsets.

 

[petermeachem]

It did use an Access 97 backend which was fine until they decided to roll it out over the whole company. So now it has a SQL Server backend. The continuous form draw extremely slowly, you can watch the records drawing maybe every .3 to .5 second. A VB copy of one of the forms does not do this (Janus grid, ado) So I thought I'd try ADO in the Access version.

So how do I do it? Peter Meachem
peter@accuflight.com

 

[MichaelRed]

Peter,

ADO may be 'faster', but the question is - Is it FAST enough. From your statement, ny thought/guess is a resounding NO!. I expect that even w/ ADO, you will not get acceptabl performance w/ ADO alone. Something else is going on in the process which is causing the majority of the slow down.

I suggest that you back up three paces, sit down and review the process and consider the whole situation. Proceede from the perspective of understanding, don't just react to the instantaneous "It is to SLOW".

Areas to look at include:

[tab]recordlocking
[tab]network settings
[tab]stored procedures vs. local procedures
[tab]network traffic
[tab]location of database, server and users
[tab]security settings and permissions (Where? SQL Server or MDB?)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over