VB 6 & Access Connection for multiuser

[me-keyspetclub]

Hello,

Hope you can help...I am running an app that works fine if it is run by one person at a time...but when multiple users try to access, the pc stops responding...I don't know what I need to do to make it multi-user accessible.

The pcs have windows vista...Using VB 6 and Access 2000. We have a windows 2003 server..

I am using ADODB
Microsoft.jet.OLEDB.4.0
adLockOptimistic
persistsecurityinfo = false
adOpenDynamic

I read here before about ´database split´ but don't know what that is..can someone explain

thanks

 

[stopher]

Hi and welcome to the forum

As you summise, you do need to split your database if you are working with multiple users.

The split database always sounds more technical than it really is. All that is really happening is that you are splitting the database into two databases. The "front end" database has all the reports, queries, forms macros etc and the "back end" database has only the tables and relationships. The "front end" then has links to all the tables in the "back end". So your "front end" still works as if it had all the data.

The "back end" is usually located on a central server. And a copy of the "front end" is put on the local PC of each user. More explanation here.

The easiest way to split your database is just to use the wizard (Tools->Database utilities->Database Splitter). Although for small databases it's easy to do it manually by exporting your tables to a blank database and then creating the links manually.

Do take a backup of your database before you begin though.

hth
Chris

EDIT:
Sorry, I assumed you where in Access despite all the clues See Bob's reply.

 

[boblarson]

If you are using VB (truly using Visual Basic 6, not VBA) to create a program that is using Access as the storage location, then you already have a split situation (frontend/backend) and you should open the backend in Access to check the properties to make sure that Open Exclusive is not checked and Open Shared is.

 

[me-keyspetclub]

Thank you both for answering.

Boblarson, I am using VB 6 and have created an exe that I dump on each pc. But I don't have to place it on each pc because it can also be run from the server through a mapped drive.

I have opened the database to ensure its not set to excluvise it doesn't appear to be to me. I went to set the users and groups rights and exclusive mode its not checked.

We had a similar app running in a diff company, but the machines were windows xp as opposed to here which they are vista. I am not sure if this may be the issue.

I can't think of anything else...can you guys?

thanks

 

[MSAccessRookie]

Thank you both for answering.

Boblarson, I am using VB 6 and have created an exe that I dump on each pc. But I don't have to place it on each pc because it can also be run from the server through a mapped drive.

I have opened the database to ensure its not set to excluvise it doesn't appear to be to me. I went to set the users and groups rights and exclusive mode its not checked.

We had a similar app running in a diff company, but the machines were windows xp as opposed to here which they are vista. I am not sure if this may be the issue.

I can't think of anything else...can you guys?

thanks

I suspect that the Highlighted code above is related to your issue. In a Split Access Database, Each user should have their own copy of the Front End, as opposed to sharing a single copy of the Front End that is located on a Common Server. This often causes problems in an Access Only Application, and although I am not 100% sure, it might be causing problems for you as well.

 

[me-keyspetclub]

Good Morning...

I am still having this issue which is driving me insane.

I have created a VB 6 exe and a package which is then installed on each pc.
I run the exe and it loads. It asks for the user id and password and it works. It goes to the database to verify the user and password exist and it allows entry to the system. Tested with 3 users login in at the same minute and they do.

It also loads the forms where the data is updated, very slowly, but then the screen takes time outs with (not responding) when updating and then it comes back. It allows one user to modify then it freezes and it allows the second user, then freezes the second user and allows the third.

This is a lot better because before only 1 person could login at a time.

I've checked the access database to ensure it is not exclusive, under the area where you define the users and groups.

At the server side, all users have full access to database folder and files.

Programatically, I am using:
ADODB
Microsoft.jet.OLEDB.4.0
adLockOptimistic
persistsecurityinfo = false
adOpenDynamic

I am not sure if there is anything else I need to do. I have crated ODBC connections via the control pannel at each pc.

does anyone have any ideas? From what I've read, I don't have to split the database because I am using Access as the back end only.

I appreciate any and all suggestions...

thanks

Me-keys

 

[datAdrenaline]

A simply point of clarification ...

>> I don't have to split the database because I am using Access as the back end only. <<

Your back end is a JET database, NOT an Access one. Access uses a JET database as its default file format and storage facility, but ... you can have a JET (.MDB) database WITH OUT Microsoft Access, they are two separate things.... just felt the need to to share that. Plus, as boblarson indicated, you are already split --- code, forms, reports, queries (ie: your vb .exe application) ... separated from data tables in the JET .mdb file.

>> I have created ODBC connections via the control pannel at each pc. <<

Unless you are using the DSN you have created, there is no need to do as you have stated. You are connecting via OLEDB, not ODBC, so really no need for the DSN's, which is a desirable situation to be in as your app is more portable since you are NOT dependant upon the DSN's.

>> does anyone have any ideas? <<

- In your Front End .EXE, do you invoke any design changes on the Back end datafile? ... if you do, you SHOULD NOT be doing so.

- In your Front End, I would suggest that you open an ADODB connection to the Back End and you KEEP IT OPEN (ie: have a Global Object Variable typed as an ADODB connection). Then set that variable to a connection on your Back End JET database, then use that variable as the ActiveConnection in all your rst.Open calls. By keeping a connection alive, you allow JET to bypass the creation/deletion of the .LDB file with every query against the database, thus improving your performance. A JET database can handle 255 concurrent connections, but a practical limit, if your tables are designed well, would be around 20 or so, I have seen a load of 50, but things slow down a bit..

- There is no need to set the persist security info parameter in the connection string simply because the default value is False, and I would recommend keeping the value as false.

- use of adOpenDynamic should really be adOpenKeyset since JET will silently force that upon you anyway since JET does not support a dynamic cursor.

- I personally like to use adLockPessimistic over adLockOptimistic simply because once a record is opened, I want it locked (adOpenPessimistic = Lock on record open). I don't want to try to save the record, only to find out that someone else was editing the record too! (adLockOptimistic = Lock on record is attempted prior to Save operation)

- By the way ... are are the permission levels of all those using the app for the folder the data is stored in ... I beleive all users of the app need to be set to "Full Control" since JET will add/delete the .LDB file when appropriate.

.....

 

[me-keyspetclub]

Hello all..

This is my THIRD attempt at Thanking you all for your answers, suggestions and ideas.

My stupid laptop keeps throwing me back to the previous page

Anyway, what I have been typing and re-typing is that I applied all your suggestions and now I have a multi-user app being used by a few people at the same time.

After someone suggested I go over the code to ensure all the recordsets were being closed when not in used, I did just that. I have two forms with flexgrids, and yes you guessed it, the recordsets were not being closed after loading the flexgrids!..big no no…I also found some other places, so a total clean up of the code was performed.

And now we are a happy go lucky bunch …that is when the server is up

Thanks all again