Compact & Repair (1 Viewer)

[alayash03]

Hello,
I have BE & FE files.
BE file I checked option : Options >> Compact on Close check box.
FE file I checked option : Options >> Compact on Close check box.
also Select General-Legacy
But this ways not working.

My question, have any others system to compact & repair it from FE file with using VBA code by BE file with specific Table Or all Tables.

 

[arnelgp]

you do not need to Compact on Close the BE.
on multi-user, you will not get it to work when
anybody else is using the BE.

manually compact it when nobody is using the BE.

 

[alayash03]

manually compact it when nobody is using the BE.

BE file is not using anyone.

Yes, here 5 users.
Any ways to compact automatically when use FE file any user or ADMIN.

 

[The_Doc_Man]

Generally, this is not a good idea. Auto-C&R has been known to cause problems because even a manual C&R has been known to fail if there was any type of corruption that was more complex than the C&R could handle. At the bottom of the thread, you should see several articles discussing C&R issues, some of which will be highly relevant to your question.

The better solution is that every user should have their own copy of the FE file and should download a fresh copy from a "master" location to whatever folder they use for that purpose. If you look for "Auto-Updater" in this forum, you can find several little batch scripts that would do the job of deleting the older FE version and then downloading and launching the new version, all transparently. Then you manually C&R the master FE just once. Thereafter, everyone copies a clean FE file every time. This works as long as you have a "pure" FE with no persistent populated local tables that could store data from one launch to the next. (But having local pre-loaded and essentially constant lookup tables isn't QUITE as big a sin, just a bit more awkward.)

As to the BE, a C&R is something that should be done during scheduled down time. You should, on a regular basis, make a backup copy AND perform a C&R on the BE file when nobody is in it.

 

[theDBguy]

In case you are interested, take a look at this code, which uses a line to perform a C&R on a remote file (not necessarily the BE itself).

Maybe you can adapt it to your needs. Good luck!

Code Snippets

 

[arnelgp]

Any ways to compact automatically when use FE file any user or ADMIN.

may cause Corruption when connection is intermittent.

 

[Pat Hartman]

Take a look at the utilities offered at www.fmsinc.com There is one that manages backups and compacts. You can schedule for off hours when the BE is not in use.

Do your users have their own personal copies of the FE? Doc referred to a search that will get you info. Users should NEVER share one copy of the FE and it is far more efficient to run the FE on the local PC than from a folder on the server.

 

[alayash03]

may cause Corruption when connection is intermittent.

Yes, for that I afraid to do that, But why I did it, because, black rows is adding into the order table And Purchase table.

Why its happen with :
There is crisis electricity. And any time electricity gone. We use UPS, only Sales, Admin & Server Computer.
We have 5 Users.
Now, we see many OrderID & PurchaseID is blank, No DATA.
The cause is User take a order and this time electricity gone. As same Purchase Order.

If we have a system that next time Open App or Before Open App will take All TABLE compact then the last blank recorded will removed automatically.

 

[alayash03]

Take a look at the utilities offered at www.fmsinc.com There is one that manages backups and compacts. You can schedule for off hours when the BE is not in use.

Its should purchase ?

 

[alayash03]

In case you are interested, take a look at this code, which uses a line to perform a C&R on a remote file (not necessarily the BE itself).

Maybe you can adapt it to your needs. Good luck!

Code Snippets

Thanks. I trying it and give you feed back later.

 

[alayash03]

In case you are interested, take a look at this code, which uses a line to perform a C&R on a remote file (not necessarily the BE itself).

Maybe you can adapt it to your needs. Good luck!

Code Snippets

I test it,

Private Sub CmdCompactBE_Click()
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy

On Error GoTo errHandler

    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String
   
'I will change here
    strTableName = "POS_be.accdb" 'name of your linked table
'I will change here
    strFileName = "D:\POS\Data File\BackUp" 'name of your backup file

    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, "Database=")(1), ";")(0)
   
    'Determine your destination
    strDestination = CurrentProject.Path & strFileName & " (" & Format(Now, "yyyymmddhhnnss") & ").accdb"
   
    'Flush the cache of the current database
    DBEngine.Idle
   
    'Create a file scripting object that will backup the db
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFile strSource, strDestination
    Set oFSO = Nothing
   
    'Compact the new file, ...
    Name strDestination As strDestination & ".cpk"
    DBEngine.CompactDatabase strDestination & ".cpk", strDestination
    'Uncomment the following line and comment the previous line
    'if your backend file is password protected or if you want the backup to have a password
    'DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=YourPassword"
    Kill strDestination & ".cpk"
   
    'Notify users
    MsgBox "Backup file '" & strDestination & "' has been created.", _
            vbInformation, "Backup Completed!"
   
errExit:
    Exit Sub
   
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
   

End Sub

Error ; 3265 - Item not found in this collection.

EDIT:
i tried here also put table name.

strTableName = "POS_be.accdb" 'name of your linked table

Error ; Bad file name

 

[Gasman]

It says 'Name of your linked table' ?
That is not a table name, but a db name.

 

[alayash03]

It says 'Name of your linked table' ?
That is not a table name, but a db name.

its tried also

 strTableName = "D:\POS\Data File\POS_be.accdb" 'name of your linked table

Error ; 3265 - Item not found in this collection.

 

[theDBguy]

its tried also

 strTableName = "D:\POS\Data File\POS_be.accdb" 'name of your linked table

Error ; 3265 - Item not found in this collection.

No, you need to use the name of one of your linked table. For example:

strTableName = "Employees"

 

[theDBguy]

its tried also

 strTableName = "D:\POS\Data File\POS_be.accdb" 'name of your linked table

Error ; 3265 - Item not found in this collection.

Also, if you are going to pass a different path to store your backup, you will have to change this line too, to remove the CurrentProject.Path portion.

'Determine your destination
    strDestination = CurrentProject.Path & strFileName & " (" _
            & Format(Now, "yyyymmddhhnnss") & ").accdb"

 

[alayash03]

No, you need to use the name of one of your linked table. For example:

strTableName = "Employees"

My file name is : tblOrders

strTableName = "tblOrders"

Error : 52 Bad File name or Number

 

[The_Doc_Man]

If we have a system that next time Open App or Before Open App will take All TABLE compact then the last blank recorded will removed automatically.

Your last statement is incorrect. A C&R doesn't remove legitimately inserted but incorrect - or blank - data. If you see that data at all when power is restored, it is there until you manually remove it. Regarding removal of records, C&R only removes things marked for deletion but not yet deleted. If you do a LOT of record updates, you will have a lot of records in the "marked for delete" category. You cannot see them but if you check for database bloating, they DO take up more space. And a C&R gets rid of them.

 

[Pat Hartman]

Its should purchase ?

Yes. It is not free. Although you might be able to find free options, I don't ever recommend them for businesses. Nothing is ever free. I'm pretty sure that your service or product isn't free. When someone offers you something for "free", YOU or your business is the product. The way they are making money to allow them to give you something for "free" is by selling your information.

 

[theDBguy]

My file name is : tblOrders

When you say "file name," are you referring to your "table?" Can you please post the entire code, as you have it right now? Thank you.

 

[alayash03]

When you say "file name," are you referring to your "table?" Can you please post the entire code, as you have it right now? Thank you.

Okay, Lets say it again.

My BE file name is : Purchase Order- V6_be.accdb
I need to compact this file into two tables. Table Name is : tblPurchases and tblOrders
Destination Path : D:\POS\Purchase Order-125 V6_be.accdb

My FE file name is : Purchase Order- V6.accde
The code is in this file into frmDashboard
Destination Path : E:\Purchase Order- V6.accde