Sql server as backend for Access application

[ponneri]

Hi All.

I have a simple application in Ms-Access 2007 with 10 forms - each having its own underlying table, about 20 queries and some reports based on them. It's being used by a single user.

However, I have recently been asked to 'share' this application to 4 users so that data is input faster into the database.

We are on a LAN with mostly Win 7 PC's having Access 2007 installed on all. And we have access to an SQL server personal edition / sql express on a network share.

I plan to retain the Ms-Access application with forms, queries and reports as it is and move just the tables to the sql server; so that no multi user issues crop up once the application is used by all 4 users.

Can someone please guide me clearly ?

Will be truly grateful.

 

[Ranman256]

This should work. Link in your tables from SQL and access should run as normal.
Just remember to give every user a copy of the front end. (forms,rpts,etc)

 

[ponneri]

Thanks a lot Ranman256 !

I'll remember the instructions and hope it really works well !!

 

[kevlray]

By the way Microsoft has a nice tool to move existing Access tables to MS-SQL. It is called Microsoft SQL Server Migration Assistant. I have only used it to move tables to MS-SQL. But it appears it can do much more. And it is free.

 

[Rx_]

Just a suggestion from someone else who has done this many times.
Instead of making the total move all at once and risk being off line a day or more figuring out why...
Break it up into manageable tasks with a fall-back option.

1. Split your existing Access DB into a front-end / back end. Test it well.
there are tools to help with this.
2, Move the MS Access Back end onto a network location then relink your single front end. Test this out.
Once in a while, a network has some issues with this or it might be the network drive. The network might be 1GB bandwidth for an average task, but it could run in spurts or have a delay in the initial hookup.
3. Use Code to Link the tables. Yes, the manual way works, but code in a multi-user environment will provide more consistency. (find examples on this forum).
3. Now make copies of your front-end to put on different workstations and connect to the common Access Back end. Test it.
4. Convert the Access DB to SQL Server.
Install the free SQL Server Native Client on each workstation and modify the table lining code to use SQL Server Native Client. (find code on this forum).
5. Put your latest version of the Access front end on a network folder.
Create a shortcut to a bat file on each workstation for the user to launch your application. The Bat file copies the Front End from the network location onto the workstation and starts it.
This lets you update the Network Front-end copy (e.g. new version) and allow each workstation to always copy and start this latest new version.
Bat file also on this forum.

It isn't much of a plan, but at least its some kind of a plan.