Can I link an Access database in Drupal? (1 Viewer)

jrantala

New member
Local time
Today, 07:15
Joined
Nov 1, 2017
Messages
6
Good morning from Minnesota in the US!

I have a fairly complex Access database that is broke into a BE and FE. I am an amateur that is quite good in Access, but I'm not a developer.

  • There are about 200 tables and queries, and 15 or so forms.
  • Moving the FE to a WebApp is not an option.
  • Once implemented, I anticipate we will have between 10-100 users.
  • SQL server is not an option.

I currently have the BE in Access, but have help in moving the data tables to an Oracle server. The FE will stay in Access to minimize hours in re-working the forms.

In another organization I worked for, I used this same database and posted it to a SharePoint site. This worked pretty well for persons inside and outside of the organization to access and use the database. But now I don't have that option -- both because the new organization doesn't 'do' SharePoint, and because apparently MS doesn't support that functionality in the new SP.

So far, I have investigated:
(1) Hosting the FE on an in-house server and using VPN to access the database.
Con: My understanding of VPN is that I can have two users signed in at the same time to the server -- but that if both sessions are 'open', other users cannot get in. If people don't log out (or I have more than 2 at a time) causing both sessions to be unavailable, I would have to have the server re-booted to free up the session. Based on my user population, I predict that this will happen a lot.

(2) Distributing the FE locally to each user and then using our standard server access to hit the BE.
Con: I'm afraid that I will lose control of versioning and headaches involves with tracking who has what version. Maybe that's not a legitimate concern.

(3) What about 'posting' a link on my Drupal webpage that will open the FE?

Questions:
  • Is this type of file able to be linked in Drupal?
  • If it can be linked, is this an 'out of the box' solution, or do I need a developer?
  • If possible technically, would I run into the same VPN issues described by (1), above?

Can anyone think of other suggestions, or offer thoughts about why my thinking on the cons might be faulty? Maybe my 'con' isn't that big of a deal.

Remember that going to a WebApp is not an option, nor is an SQL server.

Thanks for any insights you may have!

Julie

:banghead:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2013
Messages
16,553
option 2 is the way you should go. You should never have multiple users sharing the same front end. Among other things you run the risk of corruption. I cannot think of any application in any language where users share the same front end. Just because you can do it in access is not a reason for doing it. Every user should have their own copy of the front end on their machine.

Version tracking becomes a requirement but not difficult to do - plenty of threads on this and other forums for different ways of doing it. It is certainly not a legitimate concern.
 

jrantala

New member
Local time
Today, 07:15
Joined
Nov 1, 2017
Messages
6
Thank you. I appreciate the reply.
 

shadow9449

Registered User.
Local time
Today, 10:15
Joined
Mar 5, 2004
Messages
1,037
I am not clear on the specific concern about version. Assuming that you want all users to have the same version of the FE when it gets revised, a solution that's worked for me that is very simple is to put a shortcut to a batch file in the startup folder of each client computer.

So, assuming that I put the Access application front end in C:\MyDbFE, I put a batch file in there that simply says:

Code:
copy \\path_to_server\MyDB.mdb C:\MyDbFE\MyDB.mdb /Y

Any time I want to revise the FE, I put the updated version on the server. Then to deploy to all users, I create a shortcut to the batch file and put that into the Windows startup folder:

- To find the startup folder for the local user open Run and then enter Shell:startup
- To find the startup folder for ALL users of the computer enter Shell:Common Startup

Then every time users log into Windows, the front end updates automatically. It takes less than a second every morning and that way it's no extra effort for me to deploy 100 copies of the FE than it does 1 copy. Another bonus to this is that I know a lot of developers put temporary tables into another linked local database to prevent database bloating. In my approach, since the FE gets updated with a fresh copy every morning it never gets bloated.

The only concern is if people just leave their computers on and logged in all the time. In that case, I solve it by replacing the shortcut to the FE with a shortcut to the batch file and put in an extra line that launches the FE after it's been replaced.

Good luck!

SHADOW
 

Users who are viewing this thread

Top Bottom