Results 1 to 6 of 6
  1. #1
    chipwhitley is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    3

    Exclamation Database continues to corrupt—Google Drive issues?

    The company I work for uses a central server that syncs to Google Drive, that every computer has selective access to. Tasked with building a parts database, I've been working in Access 2007, saving the database to the network drive. After a week or so, the database started to become corrupted. Every time I opened it, a warning message came up saying the database is in an inconsistent state, and needs to be recovered. This was odd, but worked for another few days, until saving individual tables or objects in the database refused to save, saying the database isn't a valid file format. I searched online, and it looks like the most common cause of this error message comes from opening an Access 2007 database in Access 2010, but that isn't what's happening here; I'm just using the database under normal conditions. Now, the database won't open at all, saying the file cannot be recovered, and that the file is currently in use—which is odd considering the file isn't open on any other computers. I tried to rebuild the database just to see if it would come up again, and sure enough, after just a few days, it started to become corrupted.



    Maybe Google Drive doesn't play nice with Access? Does an Access database use invisible files in the background, which Google Drive is syncing separately to and from the network server, causing some sort of file disparity?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Google drive is not a good home for access. Get a real file server.

  3. #3
    chipwhitley is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    Google drive is not a good home for access. Get a real file server.
    The cost of switching from Google Drive would be too high considering we've been using it for years, our whole internal file system is built on top of it, everyone here is trained to use it, and Access is the only program that has had an issue with it. And I'm not 100% certain Google Drive is actually causing the problem.

  4. #4
    chipwhitley is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    3
    Clarification: We don't use or do our work with the Google Drive web interface. We save our files to and from the "real file server" on our local network, which happens to sync those files up to Google Drive.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by chipwhitley View Post
    < snip > Maybe Google Drive doesn't play nice with Access? Does an Access database use invisible files in the background, which Google Drive is syncing separately to and from the network server, causing some sort of file disparity?
    There could be a couple of things going on here. (We'll see how well I can state my case )

    1) Lets say you have a split design - An Access FE on the local computer and an Access BE on Google Drive. (This is VERY bad). You are connecting to the BE over the WAN. Unless the dB is designed as a Client/Server design, you WILL corrupt the BE.

    Consider your cell phone. You can text someone (Client/Server) or you can call them (P2P - constant connection).
    When texting - you prepare the data/request/whatever, the phone dials the number, a connection is made, the data/request/whatever is transferred, the line goes dead. There is enough built in verification to know that the transfer was completed without corruption.
    When calling - you dial the number, the call is answered, you start talking (ie transferring data), the line goes dead (the line dropped), the person receiving the call is left holding the line wondering what happened. Does he wait to see if you call back or should he go home? This is like an inconstant state.

    So,
    If the Access BE is on Goggle drive (connected over a WAN designed as P2P) has ANY drop in connection (even if momentarily), the BE will be corrupted.
    If the Access BE is on Goggle drive (connected over a WAN designed as Client/Server) has ANY drop in connection (even if momentarily), the BE will NOT be corrupted.


    2) Lets say you have a split design - An Access FE on the local computer and an Access BE on a local network drive (LAN). As long as the LAN stays up (99.99% reliable) while Access is running, you will not have any problems.
    BUT... the files are syncing with Google Drive and this happens at random times. (You have no control when it syncs).
    What happens when you are writing to the BE and Google Drive starts a sync? Have you ever tried to copy an Access BE file while Access is running? Its not pretty. Access is NOT designed like the big boys - SQLS, DB2, Oracle, MySQL,etc that can be backed up while there are active connections.
    Access... well,.....not so much (or at all). Most of the time you get a corrupted copy of the BE.




    Some reading - Using a wan with ms-access? by Albert D. Kallal This was written in 2003 but is still informative

    Look for sub-headings:
    Why a does JET file share corrupt when the connection breaks?
    Use a true client to server setup.


    But remember that Replication has been depreciated and MS has discontinued support for SharePoint. (IIRC)

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    To add to Steve's comments
    Access is the only program that has had an issue with it
    that's because you are trying to update the file directly. In word, excel, etc, the entire file is loaded to your local machine edited and then saved on a delete and replace basis, whilst being edited other users only have read only access. Access would be fine if it worked on the same basis and you were able to load the entire file to your local machine, make your changes and save according - but then like Word, Excel, etc only one user at a time would be able to make changes, changes by other users would be lost. Access (any rdbms in fact) is designed to be multi user

    Recommendation - only work on your server BE, don't allow anyone to open the file on google drive (no idea how you would do this) but copy to their local machine and open that copy instead - and this may still not work if the file happens to be being updated when the user takes a copy. Consider google drive as an archive resource for access at best - depending on how google drive is updated, not ideal if it is updated part way through updating transactions. If your server copy becomes corrupted, you should just be able to copy the BE down from google drive - but I wouldn't guarantee it.

    You will find the same issue with environments similar to google drive - onedrive for example.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-08-2017, 05:57 PM
  2. Access and google drive
    By alymac in forum Access
    Replies: 2
    Last Post: 11-10-2016, 09:23 AM
  3. Replies: 4
    Last Post: 11-03-2014, 08:36 AM
  4. Replies: 2
    Last Post: 11-19-2013, 11:44 AM
  5. Issues with Continues form
    By ducecoop in forum Access
    Replies: 3
    Last Post: 11-11-2010, 01:18 PM

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
  •  
Other Forums: Microsoft Office Forums