Results 1 to 9 of 9

Thread: making application access to the same database from two computers?

  1. #1

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    38

    Question making application access to the same database from two computers?

    I now run an application accessing a local MS access mdb database in one computer. I want to be able to run the application in two computers, making input and changes to the database accordingly, possibly one at a time. So I may copy the database to another computer when needed.

    Would changing the database to a replicated database is a good solution? Or is there better alternatives?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: making application access to the same database from two computers?

    One database ... in a location where both machines can get to it. Such as a shared folder on a server (or a PC if no servers) ... then you can point both applications to the same dbase. No replication needed. Far easier to deal with than replication too.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: making application access to the same database from two computers?

    Yeah, I've been doing this with an MDB database using the DAO for decades, used everyday in many different locations. Some don't agree with this, but I put both the MDB database and the EXE of the program out on the server, and let them create shortcuts to the EXE. The way I have it set up is that the EXE looks for the MDB in it's own folder (unless you manually setup an INI file to tell it differently, which is necessary in a few instances).

    Multiple people "hit" the database on an ongoing basis.

    For my purposes, I implemented my own form of a "record lock" that's specific to my application. So, basically, I just run the actual database "wide open", and let my home-grown record locking protect against overwrites (person #1 reads, person #2 reads, person #1 writes, person #2 writes, person #1's write got stepped on).

    If you'll be using the DAO to do this, be sure to turn off all DAO caching, or you may run into problems. I've got some code to do this if you need it.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  4. #4
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: making application access to the same database from two computers?

    Quote Originally Posted by LeonChan View Post
    I now run an application accessing a local MS access mdb database in one computer. I want to be able to run the application in two computers, making input and changes to the database accordingly, possibly one at a time. So I may copy the database to another computer when needed.
    Updating a single database from two places? That's fine. Even for Access. Mostly.
    Copying the database around? That's risky, because while you've been working on Copy A, you cannot guarantee that Copy B is completely unchanged. Copying the file will destroy any changes made to the [database] file you're overwriting.

    Your Access database is basically a binary file that is manipulated by the software drivers that allow Applications to connect to it. There is no coordinating, server-side process that manages multi-user access well. This is one of the biggest reasons that people move from Access to, say, Sql Server - proper, multi-user scalability.

    That said, you can share Access databases and doing so through an Application is better than allowing Users to connect directly to the database (you have better control over what's making the connections, i.e. your Application).

    You need to put the database somewhere that both computers can "get to".
    A file share on a shared server would be better than having it physically on either PC - those tend to get switched off at the end of the day and, when that happens, the database "disappears" for everybody else!

    Quote Originally Posted by LeonChan View Post
    Would changing the database to a replicated database is a good solution? Or is there better alternatives?
    Replication is always a complicated proposition and Access, being basically a single file, doesn't support it anyway - again, that lack of a server-side, coordinating process bites Access in the behind.

    Regards, Phill W.

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: making application access to the same database from two computers?

    Shared DB is the way to go. I have used Access MDBs for this in many cases both with DAO and ADO, and of course SQL Server and SQL Server Express in many cases, MySQL in a few cases in all cases of multiuser systems all users are talking to the same DB.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: making application access to the same database from two computers?

    The issue I have run into with this is that any network interruption experienced while the DB connection was open, can corrupt the database in weird and wonderful ways.

    One thing that was never made clear is whether or not there even exists a shared location accessible to all who want to use the DB. If this is a pair of laptops with no knowledge of one another, then sharing certainly becomes more interesting.
    My usual boring signature: Nothing

  7. #7
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: making application access to the same database from two computers?

    Quote Originally Posted by Shaggy Hiker View Post
    The issue I have run into with this is that any network interruption experienced while the DB connection was open, can corrupt the database in weird and wonderful ways.
    Absolutely agree.
    "+1" for the "Weird and Wonderful" qualification.

    It's perfectly possible for an Access database to be, to all intents and purposes, destroyed by a network glitch. That then means restoring from Backup and that means losing of Data. Never a Good Thing.

    I would suggest that this is one of biggest reasons that people move away from Access, usually to SQL Server.

    Regards, Phill W.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: making application access to the same database from two computers?

    Ever thought about splitting the Access-DB into Frontend and Backend?
    https://support.microsoft.com/en-us/...3-51b1d73498cc
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: making application access to the same database from two computers?

    Sounds nice, but doesn't it still leave you with the same problem?
    At least one of these things is sitting on a [remote] network resource that could "disappear" unexpectedly, causing corruption and resulting in Data loss.

    I would suggest it's better to "Upsize" your Access database, which reduces it to a "front-end" Application by moving all the important stuff - the Data - into Sql Server, which has far, far better performance, resilience, recoverability, etc., etc.

    If course, all this assumes that the O.P. has Sql Server available ...

    Regards, Phill W.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width