Connect a VB FrontEnd ot Access 2000 (jet4.0) BackEnd

[KirkJewell]

I have a large Server database (157,000+ records (about 200mb at present) on a Network - I want to replace the Access 97 FrontEnd with a VB6 one and the BackEnd with an Access 2000 one.

My biggest concern is data retrieval speed from the BackEnd(The FrontEnd contains very few sets of data - mostly local information).

At the moment under Access 97 I have to run a query on linked tables which means that the SQL is run on the FrontEnd and takes too long (for me anyway).

I would like to request the information from the server and have the resulting recordset set from the server to the FrontEnd to only included the 20 or so records that are actually needed (which even under access 97 would take half a second at most).

In short I want to reduce the amount of data actually sent across the Network to a the absolute minimum.

Does anyone out there now how fast Access 2000 is a BackEnd Server?

 

[swilliams]

Have you considered SQL Server 7??

Simon

 

[culshaja]

Access 2000 will response will depend on the power and load on your server. If you use the Jet 4 OLEDB driver then you will have higher performance than using the corresponding ODBC driver, and will be better than using DAO.

The use of ADO will remove the problems you have with linked tables etc, and you will be able to call queries that are stored on the server database meaning less overhead that way.

Using ADO performance can be improved by changing your cursor location, cusror type, and lock type. This is one advantage over using VB with ADO - you have greater control over these types of things which you don't get using linked tables in a 2-tier Access App.

As Simon said above, if you do get problems then look at SQL Server 7.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[KirkJewell]

Thanks for the suggestions - I have considered SQL Server but I don't have time at present to learn that as well as VB EntEdt. So I shall Use vb + access 2000 first and then updgrade to sql server at a later time.

Thanks for the help.

Kirk