How to determine the size of each table in Access database.

Tables are not the only thing in an Access database, but to get a relative number, you can run the free Analyzer for Microsoft Access and multiply the Record Width * the Number of Records.  Both of these values are on the Table Summary Report.  They are also stored in the a_Tbls table, and you can make a query to get this information too. Record Width does not take overhead into account.

free Analyzer for Microsoft Access on CodePlex
http://Analyzer.CodePlex.com
Document Access databases.  Reports to show list of tables, data dictionary, form and control properties, and more.

another consideration factoring into size that tables take is the size of its indexes -- again, you can multiply the number of records times the space for the index field space, for each index.

If someone knows a better way to get a precise number, this is a developer project that anyone can join ~

:-)

If the file is bloated by temporary data, then the simple method to prevent this is to mark the file read-only ... right-click the file, Properties, Read-Only.

For this to work, you cannot make design changes during operation and, of course, the database must be split in a frontend (read-only) and a backend (holding all tables).

/gustav

There is no utility available or calculation that will get you the number.

The only method is to import the table into a new db container and check the size, but even that might not get you close.

As Joe pointed out, relationships cannot be established across containers, so if your using them and enforcing RI, then what you might be missing is hidden indexes that JET/ACE creates on the many side.

 Consider using a temp database to hold the reporting tables, then kill off the temp DB when your done.  You can even keep linked tables pointed to a DB that doesn't exist as long as you don't use them.  So what I do is have:

tblXXX   - Linked table pointing to the temp DB
tblXXX _Template  - table template that I use to create the table in the temp DB.

So:
1. Create temp DB.
2. Copy all the *_template 's to the new DB.
3. Execute reporting using the linked tables.
4. Delete the temp DB.

  No compact problems<g> (or at the very least, minimal.

Jim.

I keep deleting and re-importing all the records there

Bloating aside, this is a pretty dangerous thing to do in a shared database.  How do the users keep from clobbering each other?

MakeTables or this constant Delete/Append are rarely necessary.  Have you tried binding the reports to queries with criteria?  That is a much more efficient method.  The only time I ever use the Delete/Append method is if I am taking millions of rows from a table and summarizing them and then using the data more than once.  If I'm only going to use the data once, then I will always use a query.

If you decide you must stick with the techniques that cause bloat, then Jim's solution is what I would choose.

Hi Experts,

Wow I got the top Access experts all under one roof-:)

I see have missed to explain the situation here which that would probable answer most of the unknown above & enable you to give the right advise..

This is the story..

We have a SQL BE and a ADP/E front end app, which is used for people in office.

Now we also have people working remotely (mostly at night shift) that need access to a small subset of data.

So let say if our DB consists of 100 tables, they need to be able to (read-only) about 10 tables, in addition they have few tables which is for them to enter data.

However for security purposes, we don’t want them to access directly our server/data where the production database resides.

Therefore I designed a small access FE app linked to an access BE containing only the tables/forms they need.

The way its being operated is that someone clicks a button which refreshes daily the data as follows.

The office app has access to both databases, the SQL and the small access BE, and it runs a set of queries that first deletes all data of those 10 tables, then reloads them from SQL.

(WE also have someone in the morning running a transfer back the data those users enter in these few tables back to our SQL database).

The size if the access BE after compacting is about 350MB, and it can grow a few hundred MB after each night transfer, sometimes it does sometimes it doesn't grow at all (my guess is it depends if users are connected at the time of the transfer).

With all this clarified, I guess you got the picture why I am so concerned about -so often- reaching the max.

FYI-
1-      The DB in question only contains tables
2-      I don’t need to enforce referential integrity
3-      Usually there should not be activity during the day, however most of the users do not log off and this poses the problem when it comes to compact the DB.
4-      It’s a terminal server and users are connected thru remote desktop, and for some reason all users are just listed as the server name, which makes it impossible to figure out which user is preventing the compact.

Thanks,
Ben

Ben,

1-      The DB in question only contains tables.  

Then split the tables up into multiple DB's as Joe suggested (believe he was the first without re-reading).

2-      I don’t need to enforce referential integrity

 One table in each container then is not going to hurt you.

3-      Usually there should not be activity during the day, however most of the users do not log off and this poses the problem when it comes to compact the DB.

  Add an inactivity timer to the FE app:

ACC: How to Detect User Idle Time or Inactivity
https://support.microsoft.com/en-us/kb/128814

 and schedule compacts with task scheduler.

4-      It’s a terminal server and users are connected thru remote desktop, and for some reason all users are just listed as the server name, which makes it impossible to figure out which user is preventing the compact.

  You want the net login name.  Code for that:

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function

Select allOpen in new window

 That will give you computer or username.

Jim.

@crystal,
I don’t see how can a solution of dropping tables work for a scenario where users are still connected.

At one point, I was trying to have them as SQL tables, and deleting or truncating them.

However it gave me an error when users where connected, this is why I stayed with access BE.

@Jim,
Our manager don’t like solutions involving timer..(and I am also skeptical, specially not on the server).

Will have the split each table separately, kept as an option if nothing better comes up.

Re your code whoAmI, I think its missing something, like what do i pass in to it?

And besides how is this going to help me, when using server's open file manager to list which user has the file open, or when compacting, the error msg should say "user so and so are still connected.."?

BTW, I think Gustav's solution makes sense, as to divide the tables in a meaningful manner, depending if data needs to be edited or read only.

Thanks,
Ben

This isn't going to help you with the compacting.  You have to get everyone out to make that happen.

This procedure is called in the open event of the main form and it references two form fields.  The calling procedure passes in a form reference so the code can be used from multiple forms as long as each has the appropriate controls.  One shows the actual database size.  You don't actually care about individual table sizes.  You care how big the database itself is since that dictates when you must compact.  The other references a button that runs a delete query.  In your case, it might be several delete queries.  BUT - others have suggested a better way with a template file and I would do that.  Regardless, you MUST get people out of the database.

Don't forget - to use the ShowFileAccessInfo() function, you MUST set a reference to the Microsoft Scripting Runtime!!!!!

Private Sub CalcSize(frm)
    frm.txtDBSize = ShowFileAccessInfo(CurrentProject.Path & "\" & CurrentProject.Name)
    If InStr(frm.[txtDBSize], "COMPACT") > 0 Then
        frm.cmdDel.ForeColor = vbRed
    Else
        frm.cmdDel.ForeColor = frm.cmdImport.ForeColor
    End If
    
End Sub

Function ShowFileAccessInfo(filespec)

'''needs reference to Microsoft Scripting Runtime

    Dim fso As New FileSystemObject
    Dim f As File
    Dim s As String
    Dim FileSize As Long
    Dim sFileSize As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile(filespec)
    FileSize = f.Size / 1000
    
    sFileSize = Format(FileSize, "###,###,###") & " KB"
    
    If FileSize > 1000000 Then
        sFileSize = sFileSize & " COMPACT NOW!!!"
    End If
    s = sFileSize
                             ''''   s = f.Path & "<br>"
                             ''''   s = s & "Created: " & f.DateCreated & "<br>"
                             ''''   s = s & "Last Accessed: " & f.DateLastAccessed & "<br>"
                             ''''   s = s & "Last Modified: " & f.DateLastModified
    ShowFileAccessInfo = s
End Function

Select allOpen in new window

Hi Ben,

When you import information, I am assuming you have temporary tables until the information is shuffled to where it needs to go.  

Some users may also have a need to make tables in which case their temporary database will not be shared.

Without knowing how the database is used, I am guessing what might be helpful to you and simply offering code to implement ideas mentioned in this thread to help keep your working database smaller ~

@Pat,
This is what I have in place, code that checks for the size of that access database and send me an email daily after the transfer takes place.

I use FileLen(), are there any advantages by using the code you posted?

@Pat, crystal,

others have suggested a better way with a template file and I would do that

I am assuming you have temporary tables

Not sure where these temp files/tables come into play here?

As described above, since users are connected at the time transfer takes place, I can only deal with existing tables, just deleting and inserting, which access allows this to occur while users are viewing the data (as opposed to SQL which throws an error).

Thanks,
Ben

BTW, I think Gustav's solution makes sense, as to divide the tables in a meaningful manner, depending if data needs to be edited or read only.

You wouldn't "divide" the tables, you would move them all to the backend - as is the recommended method whenever a database will be used by several users simultaneously.

/gustav

@Gustav,

You wouldn't "divide" the tables, you would move them all to the backend

This was my intended, excuse me for expressing it incorrectly..

Will test your suggestion & let you know.

@Jim,

As far as the server, one doesn't have anything to do with the other...not sure what you mean there.

This server is being used by office people during the day and by remote users during night shift.
In case I implement something involving timer (which keeps constantly running, although only in one place, but for each remote user) this will most likely hike up the CPU usage of the server.

Create a temp DB for the true temp tables.  Doing this may avoid having to do anything else

As stated above, I am not sure how this will work while users are connected?

I use that to create a user lock in a table.  Then by inspecting the table, I can tell who's in the app.

FYI- in the office where we have SQL, someone suggested an easy way how to check for that, see following link.

Re JET Show User Roster function, is this something I would be able to use on terminal server to see which user is connected and has the app open?

Thanks,
Ben

@Gustav,
I use the following code to delete and reload the data to that access database

    Dim rs As ADODB.Recordset, s As String, RecCnt As Long
    
    Dim db As DAO.Database, sConn As String

    sConn = DLookup("Value", "Settings", "Name='NC_Path'")
    Set db = OpenDatabase(sConn)
    
    DoCmd.Hourglass True
   
    Set rs = CurrentProject.Connection.Execute("SELECT * FROM Queriestbl WHERE Code = '" & C & "' ORDER BY [Order]")

    While Not rs.EOF

        s = IIf(Not IsNull(rs("SqlN")), rs("SqlN") & "", rs("Sql") & "")
        SysCmd acSysCmdSetStatus, "Executing " & rs("Name")
        db.Execute s
        RecCnt = db.RecordsAffected
        rs.MoveNext
    Wend
    rs.Close 
    MsgBox "Successfully Exported", vbInformation

Select allOpen in new window

Now after I made that access BE read only, I get the attached at the following line

Set db = OpenDatabase(sConn)

Thanks,
Ben
Untitled.png

It is the FE to make read-only as Access has a habit for storing temp data in this - indeed when running large or many reports.

If the BE is read-only, you must open the database as read-only, but that would only be useful if you only read data from the BE.

That said, I can't quite follow what you are doing. If you export the data, couldn't you just link the tables in the FE and run the exports from this?

/gustav

<<This server is being used by office people during the day and by remote users during night shift.
In case I implement something involving timer (which keeps constantly running, although only in one place, but for each remote user) this will most likely hike up the CPU usage of the server.>>

Timer's don't constantly run.  They only fire off on the timer interval.

In this case, even firing off every second is not going to put any kind of burden on your server. Also keep in mind that after xx minutes of this, their going to get the boot.   If they are active, then the load of them actually using the app is going to far surpass checking two properties once a second.

Believe me, it's a non-issue.

If your management still gives you grief about timers (and they are only a pain because when debugging, they fire off in the background), then flip it around; set a "get out" flag and in the app, check for that once every xx minutes.  Again, no significant burden on anything.

<<As stated above, I am not sure how this will work while users are connected?>>

 What I'm saying is that if you use temp tables, the need to bump people out to do a compact may disappear.  The temp DB and tables are created per user as they use the app and won't conflict with each other.

Have used the the technique many times and it works well.

The other option is to leave the temp tables in the FE, then use the compact on close feature.   Again, reduces bloat in the BE, and one user doesn't impact the others.

<<FYI- in the office where we have SQL, someone suggested an easy way how to check for that, see following link.

Re JET Show User Roster function, is this something I would be able to use on terminal server to see which user is connected and has the app open?>>

  Didn't see your link, but I did mention that already:

You can also use the JET Show User Roster function to see who's in a DB, but again, you'd need to build it into the app.

Jim.

If you convert the BE to SQL Server, the bloat issue will go away.

@Gustav,

There are two parts here.

One is the export from SQL to access (which is done for security reasons as described above) and looks like for that I do need read/write permissions.

The other part is from users point, which is after data was already exported, they only need read only.

@Jim,

We were under impression that when setting a timer interval, the program keeps calculating every second if it should already fire the event, however if thats not true I will discuss with manager about it.

FYI- I had the following code in the switchboard form of my app

Private Sub Form_Timer()
    If Dir("F:/Data/EnableApp.txt") = "" And Me.txtINITIALS <> "Manager" Then
        DoCmd.Quit
    End If
End Sub

Select allOpen in new window

I removed that code as of concern, it would somehow cause slowness to the system.

The temp DB and tables are created per user

The other option is to leave the temp tables in the FE

Both solutions would have the disadvantage, that in the first time users open the app, they will have to wait until data loads from SQL to Access (which takes a few minutes), while currently this is done before they start their shift, enabling them immediately to start using the app.

Didn't see your link, but I did mention that already

Actually I meant to say two things
One is that when all tables are in SQL, there is an easy way to find out who is connected, and that is explained in that link.

The other thing is, I basically wanted to know more details about this Roster function you had mentioned, perhaps you can send me a link that elaborates more on that?

@Pat,

If you convert the BE to SQL Server

As stated above, I remember trying that first, and had an issue deleting records while people where viewing data (read-only), however if you know it should work, I may try that again.

Thanks,
Ben

@Pat,

I see what you're saying re the SQL option, but I guess would have to open a new post for that targeting the SQL experts..

@All Experts,

I will not be in the office tom, please don’t hesitate to add your comments, hope to resume this on Sunday, weigh all pros & cons and finalize next week.

Have a nice weekend!

Thanks,
Ben

Just adding one more point, since this bloating only happens when there are users connected while the transfer is taking place, perhaps someone can come up with a solution that will disconnect the users from the app, without causing the BE to get corrupted.

Thanks,
Ben

I suggest the transfer be done to a new, blank database and then link to its table to shuffle the data ... assuming it IS shuffled out.

What is the code you are using to do the transfer?  Or what manual method is being used?

"it's time to warp this up"
I had in plan to test those suggestions first, in order to choose the best answer.
However perhaps Jim is right..

So I guess will start with the cleaning process (in other words, making sure all fields imported are really necessary..) as Jeff has suggested.

There is an old saying:
    "Records are cheap, Fields are expensive"


Thanks to all participants,
Ben