Step-By-Step Guidlines To Place My MS Access Back-End onto MY SQL Server (1 Viewer)

[Ashfaque]

Gentlemen,

We are planing to shift our MS Access backend of MYSQL server to get the benefit of multi-user environment.

I well remember once I use SQL server as back-end for my MS Access db but it was PC based SQL server I had place my all tbls and managed to get all the tbles linked from SQL server tbls using dbo_ method to MS Access FE.

Sometime it was working any sometime there was write conflict. Definitely there might have somewhere gone wrong but this happened with me in the past. Since then I kept Access back-end in simple file server and connected few users. But when it was on SQL Server personal version, sometime it produce write conflict and some of the fields or sometime whole row in table shows #deleted.....#deleted.

So this time I dont want this issue to appear if I move to MYSQL ( I hope so) because company asked me to make it multiuser.

I will be in need of Experts step-by-step guidelines from this forum so that no more write-conflict. Probably within few days I will start working on tbls to shift one-by-one and then will come for the rest topics....

 

[bob fitz]

We are planing to shift our MS Access backend of MYSQL server to get the benefit of multi-user environment.

You don't have to change the backend to MYSQL just because you want to have multiple users. You could use an Access file for the backend.

 

[Minty]

SQL Server Express installed on a dedicated Server should not have any issues with record locking.
I suspect the old personal edition(whatever that was possibly a developed edition) was limited to one concurrent user.

 

[CJ_London]

But when it was on SQL Server personal version, sometime it produce write conflict and some of the fields or sometime whole row in table shows #deleted.....#deleted.

So this time I dont want this issue to appear if I move to MYSQL

if it happened in sql server, chances are it will happen in mySql. Implication is there is a problem with how you are/were managing the data.

don't understand these conflicting statements

'Since then I kept Access back-end in simple file server and connected few users'

'because company asked me to make it multiuser.'

if you have a few users connected, it is already multi user, so what is different?

 

[isladogs]

A probable reason for write conflicts with SQL Server was having bit fields with no default value set and null values in one or more records. As Access can't handle null bit fields, it wrongly shows them as false (0) but update queries on those records fail with a write conflict.
The same issue is likely with MySQL.
Solution: Check ALL bit fields in SS. Assign a default value if not already done. Check ALL existing records and change all NULLS to the default value. Do that for all tables with bit fields. It shouldn't be necessary to relink the BE tables, but won't do any harm to do so.

 

[Ashfaque]

if it happened in sql server, chances are it will happen in mySql. Implication is there is a problem with how you are/were managing the data.

don't understand these conflicting statements

'Since then I kept Access back-end in simple file server and connected few users'

'because company asked me to make it multiuser.'

if you have a few users connected, it is already multi user, so what is different?

The major diff speed. Secondly, I have to arrange his manual back up each time or search/write another code lines to have BE back-up wherein SQL Server I believe auto scheduled back up so at least I am relaxed. Moreover, if an Access BE get corrupt or Crashes, which I am afraid of.

Also I am worried about its size...day by day increasing....

We have only 4-5 users at the moment that use Access db so most of the time we face speed issue. SQL Server BE will provide us full speed....I hope I clarified properly...

 

[Ashfaque]

A probable reason for write conflicts with SQL Server was having bit fields with no default value set and null values in one or more records. As Access can't handle null bit fields, it wrongly shows them as false (0) but update queries on those records fail with a write conflict.
The same issue is likely with MySQL.
Solution: Check ALL bit fields in SS. Assign a default value if not already done. Check ALL existing records and change all NULLS to the default value. Do that for all tables with bit fields. It shouldn't be necessary to relink the BE tables, but won't do any harm to do so.

Absolutely correct. This was one major reason of not having default value.

 

[Ashfaque]

You don't have to change the backend to MYSQL just because you want to have multiple users. You could use an Access file for the backend.

Bob, thanks for the advise. My FE is simply linked with tables in BE. And more importantly my BE is kept on FILE server that makes heavy traffic on local network. Therefore, we have decided to go with SQL Server as BE.

 

[isladogs]

Using SQL Server or MySQL as a BE instead of Access will NOT automatically make the Access FE faster. In fact in many cases performance will be worse until the FE is properly optimised for use with SS etc.
What it will do is provide additional stability and security if properly configured.

 

[Ashfaque]

Using SQL Server or MySQL as a BE instead of Access will NOT automatically make the Access FE faster. In fact in many cases performance will be worse until the FE is properly optimised for use with SS etc.
What it will do is provide additional stability and security if properly configured.

Thats another very imp pointed. As of now all my unbound forms are running as expected including my reports. Let us hope for the best and support from genius like you from this forum.