Using MS Access/VB6 & MS ACCESS

Advertisement


Question
QUESTION: 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.

ANSWER: Hi Raj,

Although you are so close to your solution you still are so far from it cause you are moving in the wrong direction with a perfectly working compass.

Lets take it point by point:

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

Answer: I see no problems with this, its a Good combination over a Multi-User LAN.

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.

Answer: I feel the front-end (The .EXE file should be on each client PC and not on a shared folder with a short cut unless you are running a Virtualized Server Infrastructure.Only the Access Backend files should be on the shared network folder)

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?

Answer: Seriously a BIG YES, this will improve performance and reduce record locking issues.

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.

Answer: This used to be a problem in Access 2000 and earlier but now I feel you must have all your tables sitting in the backend .MDB/.ACCDB file. Your performance won't be effected cause the record set which gets fetched is reasonably smaller in size.

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.

Answer: Keep all your Access backend databases on a single Network Shared Folder. Map this Network folder under a specific drive letter such as T: or X: and have all your front-ends be installed on your user's PC and connect to the mapped network drive. Your performance will gain like crazy. I have 180 users connected this way, but my front-end is MS-Access 2010 and Backend too is MS-Access 2010.

Hope this helps you. It does for my 2000+ users worldwide.

Do let me know.
Best regards
Manish Batola




---------- FOLLOW-UP ----------

QUESTION: It is very very useful advice. Thanks very much. Still one query is left if you can answer. Although it is much related to vb6 but if you can answer I will be thankful. Is for creating this client server sort of set up (i.e. vb6 exe on each client m/c and Access DB in a network share) beside splitting db , is System DSN of file DSN Can work ? It is for connection to db and while creating DSN we have to set up db path and login credential along with selecting a suitable  provider to connect database. But can it help under scenario where we put exe on each client m/c ? If possible , in that case how path to db gets resolved from each client exe ?

Regards

Raj

Answer
Hi Raj,

how path to db gets resolved from each client exe ?
- In my case I usually share the folder having my backend data on the server and create a Virtual Map Driver such as T:
- I then code my front-end to point to the date sitting in the folder in the map driver, this way the data is stable and faster.


Best regards
Manish Batola

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Manish Batola

Expertise

ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.

Experience

29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Organizations
Worked in various multinationals catering from Aerospace, Oil & Gas etc.

Education/Credentials
-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 About.com. All rights reserved.