ms-access project

[vbgladiator]

Hey,

I have a client who is running an access application on about 50 computers. The backend file is located on a workstation and all the other computers have an .mde file with table links. Recently, they have been complaining about the performance of teh application, mainly that it is slow, go figure he he.
I want to upsize the datafile to a SQL Server. This is what I want to do:

Convert the data file to SQL Server.
Have a MDB file with links to teh SQL SErver tables.
Each workstation will still have the .mde files linking to teh mdb file which in turn links to SQL Server.

The reason that I want to do this is that there is a lot of SQL code in the application file and I don't want to redo all of that to directly link to SQL Server.
Would they see a performance boost with that setup?

Also, would the data corruptions be eliminated? I mean, the data is handled by SQL Server, right?

Thanks,

[BrailleSchool]

if you have 50 computers and using access, you need to know that access only supports 5 concurrent connections maximum. so using access is definately a bad idea. if i am not mistaken, you can convert an access db to SQL Server from within MS Access.

In regard to the SQL, it is very similar so there shouldnt be much alteration to do. but if youre going to switch from Access to SQL Server, then it should be plainly SQL Server, especially due to the fact of having so many computers using the app.

[Static]

data coruption would be gone...

but as far as performance... I would say they would see little if no gain...
if there is a lot of SQL and coding going on.. thats probably part of the slowdown.

I would go through and see what u can do to improve the code and the queries...


then why not create and MDE that links directly to the Server... if all the code is in it... each one will run independantly of each other and not have to rely on a "middle man"

[vbgladiator]

I thought about creating a project file as oppose to teh .mde file.
However, that would require a lot code changes as there are a lot of queries using vba functions which will no longer be supported.
Also, there a few utility applications that connect to the mdb file as well and they would have to be converted as well.
So the middle man mdb would slow down the operations, right?
But I thought all the processing would be done by teh SQL Server.

[Static]

yes.. thats true... BUT, if the DB uses lots of VBA, then u wont see much of a performance boost

I would:

1) Create a maind DB linked to the SQL DB for the other apps that use it..
2) create new mde files for the users... basically using the main db as a base for the mde...put as much as u can into the users version.. so the they dont have to share anything...

just my 2 cents