0

I'm part of a small team that currently uses an Access database for scheduling a larger team's availability. This has presented some issues with corruption of the Access database. Additionally, I want to implement additional functionality over time.

I've set out to create an application for the 4-5 of us to use that will solve the concurrent database issue, as well as give the team more functionality.

Since this is a shared network drive, I won't have access to SQL Server (from my guess). I thought maybe a web service would be the way to go, but I don't really want to front the bill for this. Additionally, when I eventually leave the team I don't want to maintain this.

Some ideas I've come up with is an application written in C# that acts as the front-end with SQLite embedded as the back-end. However, I've spent days trying to get the Entity Framework to work with SQLite and am at the point of giving up.

I'm trying to decide what else I can do to solve this issue. Is there another technology I can use?

  • 4-5 users is not that much. You could split the data to its own file and put that on the share drive. Everyone would have a local copy of all the other objects and the tables would be linked to the shared file. There is record locking as well. You may want the admin here to move this to stack overflow. – JeffO Feb 6 '18 at 1:59
  • Surely the correct approach, if Access it not capable of working in the way you require of it, is to move to SQL Server or something similar. I see no reason you couldn't get SQL Server set up on some machine and use it instead of using a file on a network drive. Just because you're using a network drive at the moment there's no reason you can't change to something more suitable. – Sean Burton Feb 6 '18 at 13:37
  • Is the data structure complex to the point that you need Entity Framework? While it's great to have, is there any chance you could complete the project with writing raw SQL? – neilsimp1 Feb 7 '18 at 13:27
  • We’ll have concurrent operations happening and it was my understanding that Entity would help handle the optimistic concurrency and eviction strategies. – keelerjr12 Feb 7 '18 at 14:36
  • I am 100% sure there is an existing product that meets this requirement, which would free up the 4-5 of you to be more productive elsewhere. – user22815 Feb 8 '18 at 3:10
1

Splitting the Access database is an officially-supported thing for precisely this scenario. It turns into Front-End and Back-End files. The front-end is kinda like the application part, and the back-end is the database. You distribute a copy of the front-end to each machine and keep the back-end on the shared drive.

Whether to bite the bullet and make an full app really depends on your situation. If they do not ordinarily have access to a developer, and you think you might move on at some point, then probably continuing with Access is the better choice.

1

I don't think you did your due diligence here in selecting the proper product.

Appropriate Uses For SQLite

SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

For good concurrent use you need a real server. SQL Server Express is free.

0

Access isn't a proper database.

It's essentially a binary file that is manipulated by multiple clients at the same time - a recipe for problems, as you've discovered.

I won't have access to SQL Server ...

Why not?

If your company owns the server that your Access database file sits on, then why can't they put SQL Server on it?

This may well be your easiest option because you can then "Upscale" the data, automatically migrating it from Access into SQL Server; your Access MDB then becomes your Application layer, which you can continue to develop separately if you want to; your data will be is safely managed by SQL Server, accessed via "linked Tables" within Access.

  • I wish it were that simple. I am in the military (not as a software engineer), and comm has everyone pretty much locked down. They would laugh at me if I asked them to set up a SQL server. – keelerjr12 Feb 7 '18 at 14:34
  • @keelerjr12 that explains your profile pic. Guessing we have the same background, then (USAF veteran here, 3C field). Being locked down is a good thing when almost every foreign government on the planet is collectively spending billions of dollars to hack into your network. – user22815 Feb 8 '18 at 3:14
  • @keelerjr12: But they're happy for you to use MS Access????? Which has already /proven/ itself to be unreliable and self-corrupting?? Unbelievable. – Phill W. Feb 8 '18 at 8:50
  • @keelerjr12: If they consider an SQL server a breach of security protocol (regardless of having a valid reason to want it), then they should consider your network-share-Access approach an equivalent breach of security protocol. If they consider SQL a breach and Access perfectly okay, then they are applying the rules to the letter, while completely missing the intention of the rule in the first place. In a company, I'd escalate that to their department head (or mine) as a potential cause for internal security issues in the future; not sure if that applies for your case. – Flater Feb 8 '18 at 13:24
0

SQLite docs explicitly state:

One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem.

(Emph. mine.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.