Compacting an Access Database

[tesla]

Does anyone have a good procedure for compacting a live Access 2K database?
Right now it seems the only way is to copy the database to my local machine, compact it, and then push it back to the site. And this is done when user impact is minimized...as in the wee hours of the morning.

Its seems that there must be a better way.

[Xoc]

You could just compact it on the server. You could do that in one of two ways:

1) You could create a batch file on the server and have a scheduled task execute the batch file. There is a command line switch on Access to compact the database. This would require Access to be installed on the server.

2) There is a feature in the JRO library that allows you to compact the database. Create a web page that calls this feature. Schedule a task that activates the page. See this article for details, Compact Microsoft Access Database via ADO [support.microsoft.com].

I hope that this database is rarely used. Access databases are rarely up to the task of serving up web pages. SQL Server is a better choice in most cases.

[tesla]

Thanks for the feedback. I'll look into each of these.

With regard to Access. I have been using it on a relatively active site for about four years. And this is a site with a database with thousands of entries. Frankly, I can't really point to any significant problem related to Access. What specifically are the issues you are concerned with with regard to using Access?

I have considered moving to SQL, because of previous concerns such as yours, but it seemed too painful with no real compelling benefit or reason for doing so.

Thanks,

[txbakers]

I'd like to get in on this conversation about Access.

I have a very popular web based application in ASP using Access as the back end. 40 or so tables, 250 ASP pages, at least 100 regular users, growing rapidly. I'm serving on IIS with 1.4ghZ on a 768 DSL line.

The static welcome pages are very zippy.

When I get 4 or 5 users simultaneously using the site - which is essentially a database front end, the ASP pages grind to nearly a halt. It's disgusting. I would be b*tching and moaning and demanding refunds if I was the customer. Luckily my customers have been patient.

We talked about memory leaks in the ASP code so I went through all my pages and added rsObject = null; on every object created. That seemed to help for a while.

We haven't solved the issue entirely yet, but one expert strongly suggested I ditch Access entirely. I installed mySQL and have just about ported everything over and I'm testing this weekend for a Monday switch-over. While one expert told me that access can handle thousands of simultaneous users, another told me that this was true if they were only reading from the DB, not making updates, inserts, deletes, etc. When he heard I was making updates, deletes, inserts, multiple joins, and other complex logic he told me to get rid of Access.

I'm not 100% convinced that will solve my difficulties, but it's a step in the right direction.

I'd like to keep this thread going with more feedback.

[txbakers]

Here are some real interesting statistics from just tonight.

I have 2 IE sessions going, one to my existing site with Access and a few tables in mySQL, the other going exclusively to mySQL.

The Access sites takes 30 seconds to load one page of db information - not even an update. The same exact page on the mySQL sites takes 2 seconds.

And I'm not even accessing it over the web - I'm sitting at the server. Imagine my customers with a dial up!

The only way to "fix" the slowness is to stop/start the IIS service, and/or compact/repair the Access DB, which I did on Friday.

As the night progresses, the Access based site will get worse and worse.

[tesla]

I too have made sure the ASP code is good natured and closes connections and empties buffers. And as far as I know my site runs just fine with many users. And this is on a shared server. Most accesses by users are reads. There are by far fewer writes. Only when they "add to cart" or make and order do writes occur, and of course an initial write occurs when the session is created. I would suspect that this would be the case with most sites. If I have a problem, if Access is a problem, I sure want to know about it.

Thanks,

[tesla]

txbakers,

Based on my experience with my own site, there must be something substantially wrong with your ASP or Access implementation. There is no way it should take 30 seconds. My ASP/Access pages are in the sub two second range. I run two sites really. My local test site and my real published site. My local site is good for test but it is not useful as real site, as it is running on an XP system that is just an ordinary PC. I find that because of this the memory system is easily swamped. In fact, if I test framed pages on my local test site, many of the frames will no load if I have other applications open, such as MS Interdev and other browser windows. And I have 1.4GHz Athlon with 512MBytes of memory. These same pages work great on my "real" site, where the memory system and the operating system are quite different. Just wondering if this may be your situation as well.

[txbakers]

AFter I reboot the IIS service, the pages are very quick. Something in Access is causing my IIS/ASP service to get slower and slower.

My users are doing constant writes and updates.

This happens even when I'm at the server, so I can rule out hosting issues.

I'm having an expert look at my code to see if I just plain did something stupid. Two heads are better than one, especially if one of them isn't mine.

Meanwhile, mySQL is working great, but returning all my numbers in the European format rather than US. Trying to fix that one elegantly.

[Xoc]

Jet (Access) databases are generally considered okay up to 10 to 20 simultaneous users. The absolute theoretical maximum that Jet will sustain is 255 simultaneous users, but performance sucks long before then.

Tkbakers, your problems almost certainly are memory leaks (still). Make sure that anything that has a "Close" method is calling it, for example a Connection object. Then set the variable to "" in JavaScript or Nothing in VBScript. The following objects must be closed:

Connection
Record
Recordset
Stream

So, for example, you might do code like this in VBScript:

<table>
<tr style="background-color:#CCCC99">
<th align="center">VIE Volume</th>
<th align="left">Collection</th>
<th align="left">Title</th>
<th align="center">Status</th>
</tr>
<%
Dim rst
Dim strSource
Dim lngRow
Const adCmdTable = 2
Set rst = Server.CreateObject("ADODB.RecordSet")
strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Server.MapPath("/vance.mdb") & ";Persist Security Info=False"
Call rst.Open("qryAcquiredWeb", strSource)
lngRow = 1
Do Until rst.EOF
If Not Response.IsClientConnected Then
Exit Do
End If
If lngRow Mod 2 = 0 Then
Call Response.Write("<tr style=""background-color:#b0c4de"">")
Else
Call Response.Write("<tr style=""background-color:#c0c0c0"">")
End If
Call Response.Write("<td align=""center"">" & rst.Fields.Item("Number").Value & "</td>")
Call Response.Write("<td><i>" & rst.Fields.Item("Collection").Value & "</i></td>")
Call Response.Write("<td>" & rst.Fields.Item("PublishedAs").Value & "</td>")
Call Response.Write("<td align=""center"">" & rst.Fields.Item("Status").Value & "</td>")
Call Response.Write("</tr>")
lngRow = lngRow + 1
Call Response.Flush()
rst.MoveNext
Loop
Call rst.close
Set rst = Nothing
%>
</table>