repair/compact

[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.

 

[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.

 

[syodb]

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

 

[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.

 

[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.

 

[isladogs]

Perhaps newer versions no longer throttle.

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

 

[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