MS Access BE verse SQL Backend

[Johnrg]

Hi All,

We have had a database designed in MS Access 2010 that can "talk" to SQL server if required.

Our plan was to spend a month or so bug testing and finalizing tables and then transfer the DB backend over to SQL.

We have 6 users, with moderate data entry and manipulation and plan to bring another 6 users online later in the year via a remote desktop connection.

This will double the data entry and manipulations in the DB.

To give you an idea the biggest table in the DB after a month of running with 6 users has 13000 10 field records.

I am getting totally conflicting views on the benefits of this change over from the programmer, our IT providers and a couple of friends in the IT game.

The veiws go from no benefit at all in speed or reliabilty swapping to SQL to huge benefits, reliabilty and speed switching to SQL.

Can any of you guys shed some light on this for a guy that knows bugger all about these things but just wants a DB that is fast and does what I want it to do.

Also, we are storing the BE on an old termainl server at the moment and are having alot of and issues with speed. It seems that everytime a new user logs in the DB slows about 50%!. We are looking at a new standalone server for the BE; users and data requirements mentioned as above.

Again, can anyone guide me on the minimum requirements so I have some solid info for these IT guys that think i know nothing about nothing.

Thanks

JohnG

 

[RainLover]

Also, we are storing the BE on an old termainl server at the moment and are having alot of and issues with speed.

You need to sort out this problem first. From what you say the Database should be running at full speed. You can change Backends if you like but the problem you are currently having will not have any dramatic improvement.

I have run Access 97 with 100 users, (Not all using at the same time) and much bigger than what you are talking. Speed was never a problem.

Your SQL decision should be put on hold for the moment.

 

[gemma-the-husky]

there are a couple of issues

jet/ace backends are easy to use and administer. sql backends are less easy, because they cannot be opened in access, or even seen in windows file browsing. so you have to use and get used to a SQL DBS manager application. As a result however you get more security over your data, and who can acccess it.

connecting to SQL tables is virtually the same as jet/ace tables. you can either use the link table manger, or you can write code. it's just that if you write code the .connect string you construct is slightly different.

for jet/ace it's ;database=somepath
for sql it's ;odbc .... with other stuff

now in use, you can use your app mostly without change - but you may or may not see any benefit, as a result.

a couple of things to note.

if you use recordsets, you may need to add dbseechanges to get the code to work.

in jet/ace tables with autonumbers, i was able to "poke" in a record with an autonumber of zero, to use as a "default" value in various lookup tables - and I could not do that in SQL. some access default values in table fields cannot be used in SQL tables.

i understand a lot of the benefit comes from using the SQL backend in a different way, to take advantage of SQL facilities. - such as using "stored procedures". I am no expert on this side of thing though.

 

[ButtonMoon]

Our plan was to spend a month or so bug testing and finalizing tables and then transfer the DB backend over to SQL.

Why did you take that approach? If you intend to deploy on SQL Server anyway then it ought to be much, much easier to start developing and testing on SQL Server from the outset. Transaction isolation and security for example are done quite differently under a client-server architecture (SQL Server). Even fundamental stuff like unique constraints and referential integrity features work differently and have different limitations in Jet/ACE compared to SQL Server.

You cannot and should not expect to flip a SQL Server switch and get better performance just like that. What I suggest is that focus on migrating your app to SQL Server as soon as possible if that is still your intended goal. SQL Server has plenty of performance tools to help troubleshoot and optimise performance. Given where you are though, you should expect to have to do a certain amount of redesign and rework to get everything right on a new platform.