SQL Transactions - Multi User Question

[gray]

Hi

Access 2007, .mdb file

I've a quick (I hope) architecture question. Eventually I want to splt my DB into FE and BE and go multi-user. My Forms are based on ADO RSs and I use a fair few SQL Transactions over ADO connections.

I've been trying to get my two WINXP m/cs to map drives to one another but without much luck so whilst I just nip off and do a 3 year degree course on WINS/DNS/Domians?TC/UDP NetBIOS and Firewalls to get my 2 PCs to talk to one another..... give me strength!

If I have one user using a form which opens a Transaction (begintrans) then modifies records in a table, does this really lock the records it's updating until there's either a commit or rollback trans?

In some cases, mid-transaction, I prompt users for info at which point they immediately go and put the kettle on. What actually happens if, in the meantime, another user uses the same form on another machine and attempts to update the same records? Does the second user's transaction fail with a meaningful error that can be trapped in the form module?...

I know the theory but I never believe anything in this industry until I've seen it with my own and a hundred others' eyes.... I have seen for example a situation when developing a form, where my form has failed mid-transaction (leaving it outstanding).... and i've run an update query in the QBE and it appears to work....or at least it does not fail.... but guess what it hasn't!

Any tips/pointers gratefully rec'd?... thnx

 

[RainLover]

Hi

Access 2007, .mdb file

I've a quick (I hope) architecture question. Eventually I want to splt my DB into FE and BE and go multi-user. My Forms are based on ADO RSs and I use a fair few SQL Transactions over ADO connections.

I've been trying to get my two WINXP m/cs to map drives to one another but without much luck so whilst I just nip off and do a 3 year degree course on WINS/DNS/Domians?TC/UDP NetBIOS and Firewalls to get my 2 PCs to talk to one another..... give me strength!

Have you split or not?

If not then do so now.

Place the BE in a Folder of its own and include "BE" as part of its name.

This new folder needs to be Shared. You can do this with Windows Explorer.

On this machine you should have a FE Linked to a BE and working properly.

Now go to the second Machine and MAP (Using Windows Explorer) the Drive on the original machine.

Once done copy the FE from the Original machine to the second machine.

Delete ALL tables.

Finally from the Second Machine link to the tables to the BE in the Mapped drive.

You should now have two Front Ends sharing the same Back End.

I will let someone else answer your second question. But I don't really think it is as big an issue as you think. I would do nothing until problems occur, but others may have other advice.

Hope I understood your question properly.

 

[RainLover]

I shouldn't have to say this, but just in case you forget.

BACKUP FIRST

Then

BACKUP when finished.

Just another thought.

Make another copy and use it for development and testing.

 

[gray]

All sound advice! ... thanks ... just got to get the mapping sorted out.... It's not relevant to this forum but I think it's since I went up to WinXP SP3 on one of my m/cs.... was working OK until then... as if there's not enough to tackle already eh?

Thanks

 

[dave_m]

Yes, you will get runtime error 3218 "Could not update; currently locked."

Can you do your user interaction outside of the transaction?

 

[gray]

Hi Dave .... Ah that's excellent news.... thanks I can work with that.... Mostly the user interactions are the "X and Y is going to happen , Continue Y/N?" prompts so they need to be inside the commit rollbacks....

Ah one thing I should have said earlier is that my Transactions are on ADO RSs opened with

.locktype=adLockOptimistic
.cursortype=adOpenKeySet
.cursorLocation=adUseClient

I assume tho that a Transaction still locks the records irrespective of those settings/ Do you happen to know if that assumtionp's correct?

 

[dave_m]

Yes, it will lock. With those parameters you will get a slightly different error (code 3197).

 

[gray]

Brilliant! thnx again..

 

[RainLover]

gray

Have you installed the Hot Fix for Service Pack 3. It may help.

 

[gray]

Hi Rain

I think I've sussed it... Not for this Forum I know but .... I think one of my PCs resided on what is now a defunct Domain... as there are no domain controllers now I can't logon to it from another PC anymore... time to start bashing around in the Admin account! ) thanks again....

 

[RainLover]

No worries.

 

[gray]

Hi

Actually a quick question of FE / BE Splits... I've split my Db now... all tables in one 'BE' mdb, the rest of the objects in the 'FE' mdb. However, I seem yo have lost a lot of the Access 'properties' , the appearance of forms for example.... how do I copy all those properties over please... t'would be a nightmere to do manually..... (i've just spent 10 mins adding back all the VB references to the new FE).... thanks

 

[RainLover]

New FE?
WHY

Lets try to make this simple.

There is nothing special about a FE BE situation.

The FE has Queries, Forms, Reports and Modules.
The BE has Tables.
The FE uses the Tables from the BE by linking.

This can be done by using the wizard to split the Database.

You could copy the Development DB which has both Tables and Queries etc.
Rename one FE and rename the other BE.
Delete all the Tables in the FE and delete all objects other than the Tables from the BE. Finally Link the FE to the BE Tables.

Other ways are exporting the Tables to the BE. Importing the Tables from the FE to the BE.
Developing the BE and the FE independently.
You can even have One FE and Two or More BEs.

Relationships are set in the BE which are automatically reflected in the FE.

Finally the BE should reside on the server so that each user has access to it.
Every user has their own copy of the FE which resides on their local machine. Usually the C:\Drive.

Have a separate FE BE for development. when you are ready for release every user should get a new copy of the FE which has been prelinked the to Live BE.

To alter the BE you need to have every one off line while you are working on it.

I hope this has taken some of the mystery out of the FE BE configuration.

 

[gray]

Hi

Ah I see... I followed MS' manual instructions which suggested creating two new empty mdbs and copying the tables to one , other objects to the other... your way would negate the properties problem...

Actually, I did use your method on my first attempt but I was just a bit concerned about the MS prefixed 'system' tables which Access would not let me delete from the FE copy. By the sounds of it, it's OK to delete all the tables out of the FE but to leave the MS prefixed system tables in the FE?

 

[RainLover]

Simple.

Why are you playing with System Objects. They should be hidden by unticking them in the Options.

Anyway I am glad the mystery has gone.

 

[RainLover]

Pat

I totally agree with your sentiments re Bound Forms.

Lately it appears as though we have a lot of people fresh out of School who have learnt differently and don't realise that there is an easier way of doing things.

Maybe that is why my SQL skills are not as good as they should be.

But I get by.