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

Hi Experts,

I have a database that is used mainly for reporting, and I keep deleting and re-importing all the records there.

However every once in a while the database reaches the max size (2GB), and I need to compact it.

The problem is that there are always users connected there and its a big hassle to kick them off.

I would prefer to split the tables into multiple (access) database files, thereby reducing the frequency that it needs to be compacted..

Now my question is, how can I determine how much space does each table consume?
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Keep in mind that you cannot establish relationships across multiple dbs - if that matters to you ...
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
:-)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
There is no exact "Table Size" formula.

But this is the crux:
every once in a while the database reaches the max size (2GB), and I need to compact it.
If you are constantly running up against this limit, then you need to know if the size is due to the tables or the other objects in your DB.

First know that un-normalized tables eat up a lot more space that normalized tables..
un-normalized tables will typically have lots of "repeating" fields, For example:

tblOrders
OrderID (PK)
CustID (FK)
OrderDate
Product1
Product1Quant
Product2
Product2Quant
Product3
Product4Quant
Product4
Product4Quant
...etc
There is an old saying:
    "Records are cheap, Fields are expensive"
So this would be a better design:
tblOrders
OrderID (PK)
CustID (FK)
OrderDate

tblOrderDetails
OrdDetID (PK)
OrderID (FK)
ProductID
Quant


So here if your real issue is that the table size approaching 2GB, then Compacting or "Splitting" the data wont help much, as neither of those techniques will really "reduce" that data.
In this case, you should consider moving to one of the SQL Server variants, where data sizes are measure in *terabytes*

"I keep deleting and re-importing all the records there."
Then also make sure you are only importing the data and/or fields needed for the report.
If the report needs 10 fields, ...then only import those 10 fields.
If the report is for Sales in the last year, ...then only import last year's sales data.
;-)

I cant speak for other developers, ...but my biggest "compacted" db (A test samples db) is around 100mb, with 1500 objects.
So for me, running up against the 2GB limit, is something I have never had to worry about.

Second, ...it is typically the Forms and Reports that take up the most space.
*Especially when Graphics are added*
You can have a table with a 100,000 records and be 3 MB in size.
You can also have *one* Report, with a single, embedded, 2 mb image, that balloons the DB to *50* MB!
I have seen databases where there are scores of old forms and reports that people think "don't amount to much"

JeffCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
ARe multiple users performing these imports and deletes all of the time, primarily for the activities they are using, or is there something else going on?  If you explain why your are doing frequent imports and deletes, we might be able to make another recommendation.

If user activities are causing these imports, for their exclusive use, then you might consider using temporary tables on the users own PC, linked into your FE application.  These tables would only be tied to an individual user and could be cleaned out and compacted each time that user exits (or enters) the application.

You mention that "there are always users connected", do you mean always, or only during work hours (8-5 or something like that)?  There are programs which you can configure automatic compact and repair operations during "off" hours, or you could simply write your own.
PatHartmanCommented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
adding on to what Jim suggested:

here is code to create another database:
   '---------------------- create a new database
   dim sPathFileDatabase as string 

   sPathFileDatabase = CurrentProject.Path & "\ReportDatabase.accdb"   '---- CUSTOMIZE THIS

   'make a blank database
   DBEngine.CreateDatabase sPathFileDatabase, dbLangGeneral

Open in new window


When you are using Make Table and Append queries, you can use the optional IN clause to specify the path and filename of an external database.

After a make-table query, for instance, you would then want to link to the table:
   '---------------------- link to a table in another database

   dim sPathFileDatabase as string _
      , sTablename as string

   dim db as dao.database _
      , tdf as dao.tabledef

   sPathFileDatabase = "c:\folder\databasename.accdb"    '---- CUSTOMIZE THIS
   sTablename = "Name of table"                            '---- CUSTOMIZE THIS

   'set db to be the current database
   set db = currentdb

   'if table is already there, delete it
   call DropTheTable(db, sTablename)

   'link to table
   With db 
      Set tdf = .CreateTableDef(sTablename)
      tdf.Connect = ";Database=" & sPathFileDatabase
      tdf.SourceTableName = sTablename
      .TableDefs.Append tdf
      .TableDefs.Refresh
   End With

   'release object variables
   set tdf = nothing
   set db = nothing

Open in new window

and here is code to drop a table:
Public Sub DropTheTable( _
   pdb As DAO.Database _
   , sTablename As String _
   )
'150821 s4p
'deletes a table from the passed database reference
'if the table is not there to delete, no error will be returned

    Dim sName As String
   
    On Error GoTo Proc_Err
   
    'See if the table is there
    sName = pDb.TableDefs(sTablename).Name
   
    'If no error then table is there -- delete it
    With pdb
      .Execute "DROP TABLE [" & sTablename & "];"
      .TableDefs.Refresh
   End With
   DoEvents
      
   
Proc_Exit:
   On Error Resume Next
    Exit Sub
   
Proc_Err:

    Select Case Err.Number
      Case 3265 'Table does not exist
      Case Else
         MsgBox Err.Description, , _
           "ERROR " & Err.Number _
           & "   DropTheTable"
   End Select
   
   Resume Proc_Exit
   Resume
   
End Sub

Open in new window


for importing, again, you can do it to the scratch database instead of the current db -- ask if you want code for that.
bfuchsAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Open in new window


 That will give you computer or username.

Jim.
bfuchsAuthor Commented:
@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
PatHartmanCommented:
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

Open in new window

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
bfuchsAuthor Commented:
@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
Gustav BrockCIOCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@Ben,

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

  Timer's can be problematic, but if you limit it to this one case, there is not a major issue.  The apps that are painful are ones that have a timer everywhere.   What I do is have one hidden form at startup that does everything for app level control.  It's the only one that fires a timer event. It also holds controls to keep app level data (like security), performs checks at app close (since it's the first to open in the app, it's the first to close, so I can use it to clean-up anything or perform any checks before the app close proceeds), etc.  

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

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

 Personally if it were me, in order I'd:
1. Create a temp DB for the true temp tables.  Doing this may avoid having to do anything else.
2. Add the inactivity timer and then schedule a compact with task scheduler
3. Splitting into multiple DB's if still needed.

 Between all those, I think your situation would be well handled, quite functional, and workable.

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

  True if you want the username returned, false if you want the computer name.

<<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.."?>>

  It's something you would use and build into the app.   There are a few approaches outside of the server manager, which does work, but only if it's not a TS Server because it only shows remote connections.  Since everything on a TS Server is local, all you have is open file handles in the OS.

  So I generally prefer building user tracking into the app itself.  What I use is this:

http://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html

 Placing a user lock when the app starts and removing it when the app quits.  WhoAmI() is part of that.   I use that to create a user lock in a table.  Then by inspecting the table, I can tell who's in the app.

  I also provide "admin" shutdown in apps using that global form.  When the timer fires off, it checks for a "app lock" in the resource table and forces a quit.   So as as admin, I can lock everyone out.

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.

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

 He always has good ideas!

Jim.
bfuchsAuthor Commented:
@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
bfuchsAuthor Commented:
@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

Open 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
Gustav BrockCIOCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
PatHartmanCommented:
If you convert the BE to SQL Server, the bloat issue will go away.
bfuchsAuthor Commented:
@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

Open 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
PatHartmanCommented:
Database maintenance (which is what this is) really should be done when people are out of the database.  SQL Server gives you the ability to sever connections forcefully so that will be better for this purpose.  To delete all the rows from a table, use the Truncate predicate rather than Delete.  It is more efficient and significantly faster.  You will need some help from a DBA (which I am not) to get help with severing all open connections.  You'll also have to figure out how to deal with that in the Access app so either the user has to close and reopen Access or you will have to reconnect for him.

I also don't like timers but they serve a purpose and this is a useful one.  In the two apps where I actually used them, I had code in the login that checked to see if I was the one logging on and if so asked if I wanted to have the timers off or on.  Only if I were testing the timers specifically would I have them on so most of the time for myself, they were off.  Doing development in a database with an active timer is a recipe for disaster.
bfuchsAuthor Commented:
@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
bfuchsAuthor Commented:
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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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?
Gustav BrockCIOCommented:
Returning to your original statement:
I have a database that is used mainly for reporting, and I keep deleting and re-importing all the records there.

However every once in a while the database reaches the max size (2GB), and I need to compact it.

The problem is that there are always users connected there and its a big hassle to kick them off.
I think Pat came up with the golden key:
SQL Server gives you the ability to sever connections forcefully so that will be better for this purpose.
You could run a separate database - even an instance - if needed but - at any time - you could sign out the users. You may even have a non-issue as SQL Server itself does some internal cleanup or, at least, the database file may settle at some size you don't have to worry about.

Thus: No timer, no corruption, no oversize database.

/gustav
bfuchsAuthor Commented:
"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.