Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Sharing Access database across Internet/ODBC?

    Hi all,
    I would like to hear your thoughts about the impossibility (?) to share an Access database with different entities in our organisation - unknown territory to me.
    It is very likely it’s just a no go and/or I can't provide much more context (which could be time consuming and hard to get)... but let's see :-).

    The process involves 4 users: the coordinator (‘we’) and three experts, each in their own entity in our decentralised organization.
    We and one expert work both in the ‘environment’ of a big contractor to which our ICT has been outsourced. Besides, we also have our own ICT department with its own separate environment (let’s say to allow more flexibility and leaner/cheaper solutions…).
    The two others experts manage their own, different ICT 'environments'.

    We use a Access database with 4 users, in a yearly recurring process:
    - we populate it with data about our clients' activities and distribute it to 3 'experts';
    - the three experts approve these activities (or not), change and add some data, and send it back;
    - we solve discrepancies, ask the experts to re-evaluate certain data… to come to a final dataset,… and merge all in the end, for further processing.

    The database is quite small (ca 5000+ records/year in the main table)... but has some advanced (?) features like:
    - VBA processing of ca 250 formatted excel client reports into the database tables;
    - audit trail for specific form fields;
    - a report with quite some calculated fields and text, combined text messages and subreport;
    - VBA processing of the report into individual pdf files (and planned: mailing this to the clients);
    ...
    Some 'suboptimal' data structure choices were made to simplify data handling, coding, etc... but all users are very happy with the result.

    New goal: limiting the time we lose because the experts can't start until (most of the) clients’ data are processed and the database’ll be distributed… and because we can't review our expert's additions before they send the DB back (provisional or definite for further processing).

    So, I considered migrating to:
    - postgres/... backend (in the environment of our own ICT department – no support of SQL server/…)
    - ODBC-connected front end, run locally by us and each expert (in their own respective environments).

    Problem: our own ICT department says no: the data connection from their environment to the users’ environments (of our big contractor but mainly the two others’), through firewall after firewall... creates such a long path that they foresee too many problems. They did it in a previous project... and eventually managed to get it 'run'... but not on a solid basis.

    => Any comments or suggestions to that?
    Is this an expected/recognizable situation?

    Thanks!
    Hasse

    ps Possible alternatives I see:
    - to arrange a personal account for the two ‘other’ experts so they can log into our environment & let them use our Access database ‘directly’ through an online connection using VPN (with a registered laptop of our own). Yearly cost range 500-800€ (personal favourite);
    - RAD tool: might not provide the needed interface & automation features;
    - Citrix
    - starting project at our big contractor: expected to be a no go due to too much overhead;
    - keep it the way it works now.
    Last edited by hasse; 2016-02-09 at 09:12.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Personally, I would have the database on a server, and then use Remote DeskTop from each of the remote users to log into that server. This way they would be "local" to the database. Each user would have separate directory, which would contain a copy of the frontend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    hasse (2016-02-10)

  4. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 977 Times in 907 Posts
    +1

    cheers, Paul

  5. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Reply from our ICT department: this requires a Windows Database Server, which we don't have...
    They propose Citrix now... but no idea what pitfalls that might imply for our setup... Let's google (but any more thoughts are welcome!)

  6. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Remote Desktop and Citrix are pretty similar. We've used both and they work pretty well. I'm not sure what they mean by a "Windows Database Server" but as long as your back-end (hopefully your database is split into a front-end and back-end), then they don't need any sort of server other than Citrix. Note that you will need an installation of Access (or a version of Office that includes it) for each user.
    Wendell

  7. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I don't know what you mean by a "Windows Database Server" either. I'm guessing they don't understand how Access works. There are 2 separate issues here, where you put the Access backend database, and how users connect. You can put the database itself on any server because it is just being a file server for the Access database. I had a client that had it sitting on a Linux server. You will need a Windows Server to host Terminal Services (or Citrix) so you'd be remoting into a Windows box to run Access. Your file server and host machine could be the same. Doesn't matter.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 977 Times in 907 Posts
    Sharing apps on a terminal server via RDC or Citrix is standard IT practice. A database server is definitely not part of that scenario.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •