Using MS Access/VB6 + MS ACCESS & Splitting DB Advice
We have deployed a VB6 (FRONT END including sql queries in VB6 Coding) with DB as MS Access 2003 project in multiuser operation under LAN network (having OS as windows xp professional in all computer) at our client. For reports we have used crystal report x1
Currently both db and vb6 (executable) is located in shared folder of a LAN Computer. and all network users has shortcut path of vb6 application. so this is network sharing set up.
My query is do we need to split the MS ACCESS Database (which currently has only tables) and deploy vb6 exe on each client along with front end DB of ms access having all tables link to Back end shared DB after splitting database?
In our project we have some master tables which are rarely updated so can we include this master tables in each user's front end DB ? Will it provide performance gain ? However in sql queries master tables has to refer other transaction tables via joins. Transaction tables has to be reside on back end part of DB.
So can anybody advice for our project is splitting ACCESS DB will help in performance or we should keep it as network share set up.
What is right way of implementing VB6 as front end & MS access as back end under multiuser environment.
I'm a bit confused here. Why would you have BOTH a VB6 app and an Access front end? If you are using Access as the database engine that's fine. If it has only tables in it, then it is, essentially, split already. Splitting simply means separating the FE from the BE.
I'm not sure about the VB front end. I think it should be installed locally because it needs to write to the registry of each local machine.
As far as putting static tables in the FE, you can't do that. VB programs don't store data, they just link to an external data source.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA