repair/compact (1 Viewer)

[syodb]

My local PC has only Access 2010, the remote file is mydb.mdb file.
Like to create a .vbs or .bat file on local PC so that my local Access 2010 can repair/compact the remote .mdb file periodically. any straight forward way to do this?.

 

[The_Doc_Man]

See this article:

https://support.office.com/en-us/ar...4ef5-4178-b235-441737deb3a6#ID0EAABAAA=Access

There is a command-line switch (option) to do this. Note: In the table in that article, the default is to show you Word options. But above the gray bar that is the header for that table, you can find the names of the other utilities. Click "Access" and you will see the option you need.

 

[Pat Hartman]

local/remote implies that the FE and BE are not on the same LAN. I would not run a compact over a VPN if that is what you are doing. It will be too slow and something that slow is always in danger of a network blip that could corrupt the database. Even with BE on the same LAN as the FE, compacting across a LAN is also slow. It is far better to run the compact on the server if possible or even download the BE, compact it, and put it back.

Look at the tools offered by FMSINC.com that allow you to schedule this process.

 

[syodb]

my VBA compact/Repair causes the size to go from almost 1.5G to 1G. What is the real size, 1G?. I have to repeat this action everyday. Is this Ok?.
Only need this database for near future, then there we will be an upgrade.

 

[Pat Hartman]

The bloat is a sign of poor design choices. The two major ones would be
1. Temp tables
2. making design modifications to forms on the fly.

Temp tables might be made by using make table queries or they might be permanent tables but you delete and append different sets of data. both result in the same problem. Vacant space that Access cannot reuse.

Once you identify what is causing the problem, we can offer suggestions for limiting the bloat.

The underlying issue is that Access cannot reuse freed space. so if you delete an object, there is no way for Access to reuse the vacated space until the database is compacted. Compacting is essentially a read/write operation. A new empty database is created and all objects are copied from the original to the new database. In the process, Access also sorts all tables into PK sequence and rebuilds all indexes.

Is the database split?
Does each user have his own personal copy of the FE?

 

[syodb]

Is the database split?
yes, it is.
Does each user have his own personal copy of the FE?
yes.

 

[Pat Hartman]

OK, did you find the source of the bloat?

 

[syodb]

frequent continuous opening, inserting, closing,.. repeating the same thing whole day. Need a more durable engine. mysql is good but bad thing it is not free. any ideas?.

 

[The_Doc_Man]

Open/Insert/Close will rarely cause bloat. Open/Update/Close will do it. Churning (doing explicit Delete and then replacing what you deleted with a newer version) will definitely do it.

Temp tables will do it. Frequently updating a field that is indexed will also sometimes cause bloat if you do that often enough, though that is likely a much smaller contribution.

On a split FE/BE, most of the internal temporaries (like the recordset implied by a query) are in the FE. ORDER BY usually sticks it to the FE as well. I'm not sure what else would hit the BE.

 

[Pat Hartman]

SQL Server Express is free. It will work for a small number of users. It is set to slow down when you have more than a couple of concurrent users. Also, an improperly designed Access FE will most likely work poorly given a straight port to SQL Server so you might end up having to redesign anyway.

You still need to find the problem. Changing the BE is very unlikely to resolve the problem and may cause worse problems.

Check out the FMS tool. It is reasonably priced and will compact the BE on a schedule to keep it in check while you are working on fixing the actual problem.

 

[isladogs]

Pat

I have worked with SQL Server Express for over 10 years both as a developer and an end user. I have never been aware of any slowdown with more than a couple of concurrent users. Indeed with my schools databases there have been many more than that. I believe there would be up to 50 users on a regular basis and could check this using program logs.

I would therefore be interested to know more about the background to your comments.
Initial research indicates there is no throttle on SQL Server Express though there are hardware limitations.
For example see:
https://forums.asp.net/t/1106628.aspx?What+is+the+limitation+of+SQLEXPRESS+
https://docs.microsoft.com/en-gb/sql/sql-server/editions-and-components-of-sql-server-2016


I certainly agree that, to make the most of the features of SQL Server, it is essential to optimise how the Access FE works with it.

 

[Pat Hartman]

Perhaps newer versions no longer throttle.

 

[isladogs]

Perhaps newer versions no longer throttle.

One article I found said there have been no restrictions since 2002

 

[Pat Hartman]

If it's fake news, it's been around for a long time. Good to know there is no problem.

 

[isladogs]

I only picked up on it as it was something I'd never heard of
I didn't use SQL Server until 2007 but it was a bit of a shock when I read your comment
Glad I did so as it prompted me to do a bit of research

 

[nivalju]

I have several access databases that I use daily. One in particular is used to create, modify and delete tables and queries on the fly. There are 3 of us with a similar database and we compact and repair a few times during the day, but each one has crashed at least once and we had to use a backup database and update it.
What can we do to stop this or at least lessen the effects of the crash.

Judy

 

[isladogs]

Please can you start a new thread so as not to hijack the current one.

When you do so, please explain how your database is used.
For example:
Is it split?
Does each user have their own separate copy of the front end database

 

[nivalju]

Did not mean to hijack--

Judy