none
Compact & Repair changes database VERSION? RRS feed

  • Question

  • Front ends named FE5j.accdb on several clients linked to TheBackEnd.accdb on a Windows 7 network maintained by a third party, who have apparently "changed some things".

    BE was ~34MB on the server. I took a copy home. C&R reduced the size to ~23MB, so I knew the version on the server could be cleaned up. Which I did.

    At the end of the process, Access produced an error message to the effect that it could not rename Database.mdb to TheBackEnd.mdb, and explained how C&R makes a copy first, then does the compaction, then does a name change. Notice that it was trying to change the file extension. What!?

    I manually changed the file name and relinked the databases on 3 machines, which means there are still a few clients that need relinking.

    The BE is now named TheBackEnd.mdb, which A2010 can read, and there’s nothing in there that’s peculiar to A2010, so it should be good to go, but this is just weird.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, August 23, 2018 7:10 PM

Answers

  • Hi Peter,

    It's a very interesting observation, one I haven't noticed before, but it seems it's the normal behavior - probably a relic of the original C&R code.

    So, although MS may have never updated the C&R code to use a different file extension than MDB, the file extension does not dictate the format of the file.

    What's important to note in your situation is why did the C&R fail to complete and produced an error instead. One of the possible reasons is what Dirk said. You shouldn't perform a C&R over a network connection. This has the potential for data/file corruption, which sounds like what could have happened to your file, if you did in fact perform the C&R on a network copy of the BE.

    As best practice, you should copy the BE on your local drive first, do the C&R, and then copy the BE back to the shared drive.

    Just my 2 cents...

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Thursday, August 23, 2018 7:51 PM
  • It's a very interesting observation, one I haven't noticed before, but it seems it's the normal behavior - probably a relic of the original C&R code.

    So, although MS may have never updated the C&R code to use a different file extension than MDB, the file extension does not dictate the format of the file.

    I never noticed that, but it's true -- I just ran a compact and saw the same thing.  As theDBGuy says, the file extension of the compacted file, before it's renamed, doesn't correspond to the true format of the file.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Thursday, August 23, 2018 8:11 PM
  • I personally have wished the Access team would not have used a 'known' extension when creating the compacted file and used something like '~<name of the orginal>_compact.cpk', especially since the initial file is a temporary file!

    When I do a C&R programmatically, that is what I do since the extension does not determine the format.


    Brent Spaulding | Access MVP

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Friday, August 24, 2018 3:07 PM
  • I personally have wished the Access team would not have used a 'known' extension when creating the compacted file and used something like '~<name of the orginal>_compact.cpk', especially since the initial file is a temporary file!

    When I do a C&R programmatically, that is what I do since the extension does not determine the format.


    Brent Spaulding | Access MVP

    Hey Brent

    I do the same thing. I use <orig name>.accdb.bak. adding the bak reminds me it's a backup. Keeping the original extension tells me what the file is.

    I noticed the mdb extension a while back when a compact got stuck and left the copy in place. Interesting that it never got corrected.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Friday, August 24, 2018 3:59 PM

All replies

  • Peculiar.  When you compacted the version on the server, did you copy it to your own local PC to do it, and then replace the server copy?  Or did you logon to the server to compact it, or did you compact it directly on the server across the network?

    What version of Access was used to compact the server copy of the database?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 23, 2018 7:23 PM
  • Hi Peter,

    It's a very interesting observation, one I haven't noticed before, but it seems it's the normal behavior - probably a relic of the original C&R code.

    So, although MS may have never updated the C&R code to use a different file extension than MDB, the file extension does not dictate the format of the file.

    What's important to note in your situation is why did the C&R fail to complete and produced an error instead. One of the possible reasons is what Dirk said. You shouldn't perform a C&R over a network connection. This has the potential for data/file corruption, which sounds like what could have happened to your file, if you did in fact perform the C&R on a network copy of the BE.

    As best practice, you should copy the BE on your local drive first, do the C&R, and then copy the BE back to the shared drive.

    Just my 2 cents...

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Thursday, August 23, 2018 7:51 PM
  • It's a very interesting observation, one I haven't noticed before, but it seems it's the normal behavior - probably a relic of the original C&R code.

    So, although MS may have never updated the C&R code to use a different file extension than MDB, the file extension does not dictate the format of the file.

    I never noticed that, but it's true -- I just ran a compact and saw the same thing.  As theDBGuy says, the file extension of the compacted file, before it's renamed, doesn't correspond to the true format of the file.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Thursday, August 23, 2018 8:11 PM
  • The compact worked; it was only the renaming step at the end that failed.

    My PC isn’t connected to their network, but I suppose I could bring a laptop, copy it out and back.

    I have everyone log off the database so I have exclusive access. I open the database by double-clicking on the BE. But I’m still using a FE machine, so I’m not sure which category of network connection I’m in, Dirk.   ?

    The network admins have turned off the file extension display! But, since the BE is/was A2010, and that is the only version on the machines, it has to be 2010.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, August 24, 2018 12:54 AM
  • I would demur from describing this as normal behavior. Perhaps consistent behavior is more accurate – consistently weird.

    What happens is that Access leaves 2 files: the original and the new.

    I’m thinking that I’m NOT doing a compact over the network because all the action is over there, not on my side. I say to Access “do it”, but there is nothing flowing over the network at all after that instruction.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, August 24, 2018 12:54 AM
  • Dirk – how did you interrupt the compact to observe this phenomena?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, August 24, 2018 12:55 AM
  • Peter,

    Just a quick FYI, I passed along this info (link to this thread) to the Access Dev Team.  So they may address it down the line.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, August 24, 2018 12:21 PM
  • I would demur from describing this as normal behavior. Perhaps consistent behavior is more accurate – consistently weird.

    What happens is that Access leaves 2 files: the original and the new.

    I’m thinking that I’m NOT doing a compact over the network because all the action is over there, not on my side. I say to Access “do it”, but there is nothing flowing over the network at all after that instruction.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Hi Peter,

    What I was referring to as "normal" behavior was specifically the use of the MDB file extension for the intermediate file during the C&R process. All the other issues happening when one performs a C&R, I am not claiming as normal.

    Hope this clarifies what I meant a little bit.

    Cheers!

    Re: "compacting over the network," here's my understanding...

    For you to perform a C&R, you'll have to open Access, correct? If so, you must be opening the Access app on your local machine when you do this. For example, when you navigate through the network folder and double-click on the BE file, then the Access application on your local machine will open up and connect to the BE on the network share. At this point, there is/are some network traffic going on (I believe).

    So, when you click on the C&R button on your local Access application, you are telling your local machine to perform the C&R function. Although the ACCDB and MDB files are on the network share, since the application doing the C&R process is on your local machine, I still think there are some network traffic going on.

    Just my 2 cents...

    Friday, August 24, 2018 2:57 PM
  • I personally have wished the Access team would not have used a 'known' extension when creating the compacted file and used something like '~<name of the orginal>_compact.cpk', especially since the initial file is a temporary file!

    When I do a C&R programmatically, that is what I do since the extension does not determine the format.


    Brent Spaulding | Access MVP

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Friday, August 24, 2018 3:07 PM
  • I personally have wished the Access team would not have used a 'known' extension when creating the compacted file and used something like '~<name of the orginal>_compact.cpk', especially since the initial file is a temporary file!

    When I do a C&R programmatically, that is what I do since the extension does not determine the format.


    Brent Spaulding | Access MVP

    Hey Brent

    I do the same thing. I use <orig name>.accdb.bak. adding the bak reminds me it's a backup. Keeping the original extension tells me what the file is.

    I noticed the mdb extension a while back when a compact got stuck and left the copy in place. Interesting that it never got corrected.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Peter N Roth Friday, August 24, 2018 4:27 PM
    Friday, August 24, 2018 3:59 PM
  • Thanks, all for your contributions to my education!


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, August 24, 2018 4:28 PM
  • Hi Peter,

    You're very welcome. We were all happy to assist. Good luck with your project.

    Friday, August 24, 2018 4:53 PM
  • Dirk – how did you interrupt the compact to observe this phenomena?

    I didn't interrupt it; I just compacted a very large database, one that would take a minute or two to compact, and watched the folder to see what file was created.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 24, 2018 4:59 PM