1

Currently, I'm developing a C#-based program for a small rental company (3 locations). Right now, they use MS Access 97 (Jet SQL based) as database and I wish to upgrade this. However, I do want to keep Access as Front-end, since I will be gone after the development, and the local personnel knows how to use Access (some changes require direct editing in the database).

I am doubting between two options:

  • Upgrade to Access 2013, therefore using MS ACE as DB engine
  • Use SQL Server Express with Access as front-end, therefore using MS SQL Server as DB engine

The system will have one shared database and one for each location. They are using a shared drive for this (they work on MS Server 2008). Their databases are pretty small (< 1 GB combined), so I won't need the extra performance e.g. MySQL provides. I know the difference between ACE and SQL Server in terms of design (File-sharing vs client/server), but I still don't know what would be better suitable for this situation.

What is the better option here when looking at performance, reliability, security and connection to the application?

Thanks in advance.

JeroenM
  • 117
  • 1
  • 9
  • Probably a discussion to have with the company. Lay out the pros/cons of each approach and have them decide. There will be trade-offs as far as cost, performance, complexity, maintenance, ect. are concerned. – Fink Aug 01 '14 at 15:48

2 Answers2

1

As @granadaCoder points out, the security, performance, reliability of using SQLExpress is far better than Jet and ACE and is just as easy to connect/link to your Access 97 front-end. Microsoft provides a free migration tool that is very powerful and easy to use.

Converting an MS-Access 97 application to 2013 may present some real challenges as Cwell. onverting from Access 97 to 2013 is a two step process. You must first convert it to 2002-2003 and then to 2007/2013. You will also need to purchase licenses for all users and the back-end database.

In addition, if your 97 application references external objects, they may not work with later versions of Access.

As @granadaCoder also suggests, a good medium to long term plan would be to convert the front-end to .NET.

ron tornambe
  • 10,452
  • 7
  • 33
  • 60
0

Microsoft Jet is just a file sitting on a network drive. So when you do queries......the Jet-Runtime (on the local PC) has to bring large chunks of data (entire tables) across the network. Thus it is brutal.

Sql Server (Express or Other)....runs as a service on a host computer. And when a query is executed, it does processing on the Server and returns "smaller buckets of information".

(Which you mention knowing the difference between file-sharing vs client-server).

If you cannot give up your Access(the program) front end...then doing link-tables to Sql-Server would be you best bet, IMHO. Well, I'm talking from performance.

Security, you have more options for different users and passwords. And you can slice up which logins/db-users are allowed to do what.

IIRC, a Jet database allows one password. Aka, all or nothing.

https://www.connectionstrings.com/ace-oledb-12-0/with-database-password/

That alone would make me go with SqlExpress.

..

The big early design decision was to use Microsoft-Access-Forms. You're paying the price for that early decision.

Even when people use a Jet-Database, I would only use it for basic data storage. And put a Layer .Net application on top of it. Then a swap out to a different data-store isn't as drastic.

Good luck dude.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146