shrink size of Access app by grabbing subset of all table data
-
hiya,
I've been handed an Access 2003 app that I want to convert to yukon.However, before I can do that, I need to get a copy of the app on a datastick to find out how the app works.
I want to minimise the size of the app, by selecting only the TOP 5 records from each table.
After all, all I currently need is a working copy to play with.
Can this be done?Preferably without having to write any queries.Maybe there is a better way?
Many thanks,
yogi
-
hiya,
So I just have to copy the entire app over?
If someone can reply, even to confirm that this is the case, then that'd be much appreciated.
cheers,
yogi
-
From Access Help
"Dim strGetSQL As String
strGetSQL = "SELECT TOP 10 Products.[ProductName] " _
& "AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " _
& "ORDER BY Products.[UnitPrice] DESC;"
Me.RecordSource = strGetSQL"
Would recommend not doing this though as you may miss child records in other tables, reference data etc ... ?
Make sure to compact the DB also (Tools | Database Utilities)
-
It sounds really boring to do, but if its for a `make a 1 off play copy`, there arent too many tables and there arent too many records in them (xxx,xxx's) then its probably as quick make a copy of your db and...
- open and view each table in access (thats a double click of the mouse)
- go to and select the last record (populates the table) (2 mouse clicks to do that)
- scroll up to the top of the table in your view (a click a scroll of the mouse)
- shift click about 6 rows from the top (another click)
- press the delete key with a couple of confirmations
Its dirty compaired to a nice elegant solution, but sometimes but the time your elegant solution is ready, coudl coudl have got the dirty way done and dusted and been down the pub.
martin
-
righto, thanks for taking the time.
I hope I didn't come across as being lazy.I was looking for an approach to do this so that that I could "re-use" this approach in the future.
I was lost in googleland.I'll do it the quick way that was kindly suggested.
thanks all,
yogi
-
re-usable approach for the future
ive knocked together a quick database (you can grab it http://www.jitsu.net/misc/wipetoprecordsintables.zip for the access database along with some prefilled tables so you can see it wiping them)
the code goes as follows - its about 40 times more long winded than you actually need, all it does is cycle through the tables collection, then runs a delete statement on non system tables leaving `rowstokeep` number of records in each table.
Im a sucker for status fields telling you whats going on, so everything else just fill a couple of controls in a form in the database with information on what stage the code has reached. Far prettier than a debug.print
You can play with the ORDER BY direction to change if its the first or last rows in the table that are deleted.
Ive highlighted the couple of important lines in green
many thanks
martin
-----------------------------
- Option Compare Database
Option Explicit
- ' (c) 2005 Martin Bastable
' Please feel free to use this in whatever way you see fit!
- Private Sub DoIt_Click()
Dim count As Integer
Dim tbl As TableDef
Dim db As Database
Dim rowcount As Integer
Dim fieldname As String
- Dim statustxt As String
Const rowstokeep As Integer = 5
Set db = CurrentDb()
count = db.TableDefs.count
- statustxt = "Keeping top " & rowstokeep & " for all non system tables" & vbCrLf & vbCrLf
- For Each tbl In Db.TableDefs
count = count - 1
DoIt.Caption = "Processing '" & tbl.Name & "' (" & count & " still to go)"
statustxt = statustxt & "Processing '" & tbl.Name & "' (" & count & " still to go)" & vbCrLf
- ' a quick and dirty check, we dont want to go wiping system tables!
If Left(tbl.Name, 4) = "MSys" Then
statustxt = statustxt & tbl.Name & " skipped (system table)" & vbCrLf & vbCrLf
setstatus statustxt
Else
' delete all but the top 5 entries
' first - how many entries we have
setstatus statustxt & "- Counting " & tbl.Name
rowcount = DCount("*", tbl.Name)
statustxt = statustxt & "- " & rowcount & " found, "
If rowcount > rowstokeep Then
' and delete all but the top 'rowstokeep'
' 'rowcount - rowstokeep' gives us the total number of rows after we keep our top few
statustxt = statustxt & rowcount - rowstokeep & " to be deleted" & vbCrLf
setstatus statustxt & "- deleting please wait..."
' we do need a fieldname to do some sql with, as DELETE TOP x FROM table doesnt seem to work in access!
' first one will do, were not fussy
fieldname = tbl.Fields(0).Name
' we don't want lots of warnings
DoCmd.SetWarnings False
db.Execute "DELETE * FROM [" & tbl.Name & "] WHERE [" & fieldname & "] NOT IN (SELECT TOP " & rowstokeep & " [" & fieldname & "] FROM [" & tbl.Name & "] ORDER BY [" & fieldname & "])"
DoCmd.SetWarnings True
- statustxt = statustxt & "- " & rowcount - rowstokeep & " deleted!"
Else
statustxt = statustxt & "not enough to delete"
End If
statustxt = statustxt & vbCrLf & vbCrLf
setstatus statustxt
Me.Repaint
End If
Next
statustxt = statustxt & "ALL FINISHED!"
setstatus statustxt
DoIt.Caption = "Done!"
db.Close
End Sub
- Private Sub setstatus(val As String)
Status = val
Status.SetFocus
Status.SelStart = Len(val)
End Sub
- Private Sub Form_Open(Cancel As Integer)
DoIt.Caption = "Go!"
setstatus ("")
End Sub
- Option Compare Database
-
cheers martin,
I'll give it a go.
yogi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply