How to clean the database quickly !

[khashyar7]

Hi,

I have made a simple database and then to test it I have added some scrap data to it. Is there a quick way to get rid of the scrap !?

Please lemme know,

Kash

 

[RoyVidar]

I don't think there's a "clean my database" command, I think you'll have to just delete the contents of each individual table.

If you're using and displaying autonumbers, then you'll perhaps also want to reset those, which should be achievable thru a compact/repair. I think in later versions, the behavious of compact/repair has changed somewhat, you might need to manually reset some, for instance thru something like this:

[tt]dim strsql as string
strsql="alter table MyTable alter column MyAuto int identity(1,1)"
currentproject.connection.execute strsql[/tt]

First number is starting value, second is increment. I think this needs version 2000+/Jet 4.0.

Perhaps for future questions, be a bit more precise? See faq181-2886 on how to get the most out of the memebership.

Roy-Vidar

 

[TheAceMan1]

How are ya khashyar7 . . . . .

In a module in the modules window, copy/paste the following function:

[blue]Public Function ClearTables()
   Dim db As DAO.Database, tbl As TableDef
   
   Set db = CurrentDb()
   
   For Each tbl In db.TableDefs
      If tbl.Attributes = 0 Then
         DoCmd.RunSQL "DELETE * FROM " & tbl.Name & ";"
      End If
   Next
   
   Set db = Nothing

End Function[/blue]

To run the code, setup a [blue]hotkey combination[/blue] with an [purple]Autokeys Macro[/purple] that calls the function. As already mentioned by [blue]RoyVidar[/blue], [blue]Compact & Repair[/blue] to reset any autonumbers you've used.

Thats it . . . . give it a whirl & let me know . . . .

See Ya! . . . . . .

 

[khashyar7]

Hey AceMan

Thanks for the quick help again, but I'm afraid I don't quite get

1. What you mean by setting up a hotkey combination with an Autokeys Macro !!!

2. Why in the loop you have only tbl.Attributes = 0 !

3. Why do I need to reset the autonumbers. Is it because otherwise they couldn't be allocated again !?

Sorry for the dumb questions !

Cheers,

Kash

 

[TheAceMan1]

[blue]What you mean by setting up a hotkey combination with an [purple]Autokeys Macro !!![/purple][/blue]

[purple]HotKeys[/purple] are a means of running macros or VBA from the key board. The [purple]HotKeys[/purple] are run from the specific macro object [purple]AutoKeys[/purple]. An example will serve best ([blue]you might as well set this up in preparation for the [purple]ClearTables[/purple] function[/blue]):
[ol][li]In a module in the modules window, copy/paste the following test function:

[blue]Public Function TestHotKey()
   MsgBox "HotKey is Working!"
End Function[/blue]

[/li]
[li]Open a new macro in the macros window. Click [blue]Save[/blue] and name the macro [purple]AutoKeys[/purple] (spelling is important).[/li]
[li]Now to setup the hotkeys. The key combination will be [purple]Ctrl+Shift+F9[/purple]. Setup the macro to look like the following:

[blue]Macro Name    Action     Function Name
----------  ----------   -------------
^+{F9}      Run Code     TestHotKey()
            Stop Macro[/blue]

To enter the caret [purple]^[/purple] its [blue]Shift+6[/blue].
The brackets around F9 are the [purple]curly brackets[/purple]
Save/Close the macro.[/li]
[li]Execute the hokeys [purple]Ctrl+Shift+F9[/purple], a confirmation message should pop-up.[/li][/ol]
Now . . . I could have suggested you use a [blue]command button[/blue], but clearing all the tables is not a function you want out in the open. So to avoid [purple]click-itis[/purple] I suggested the hotheys ([purple]you have to intentionally go out of your way to execute the keys . . . no mistake . . . no accidents here[/purple]).

[blue][purple]click-itis:[/purple]
That condition which occurs when the finger keeps going, although brain to hand is disconnected ;-)[/blue]

When your satisfied with the hotkeys, replace the [blue]function name[/blue] in the macro with [purple]ClearTables()[/purple] and your set.

[blue] Why in the loop you have [purple]only tbl.Attributes = 0 ![/purple][/blue]

Besides the tables you design, there are [purple]hidden system tables[/purple] used to track objects throughout the entire database. To see them, in the database window click [blue]Tools[/blue] - [blue]Options...[/blue] - [blue]View Tab[/blue]. Check [purple]System objects[/purple] & click ok. Now goto your tables window and you'll see the additions.

System objects are assigned certain integers and negative integers for the Attribute property. Delete the records in one of these tables and the whole database goes [purple]KA-BLEW-EEEE![/purple]. User defined tables are assigned a value of zero. I'm sure you understand!

[blue]Why do I need to reset the autonumbers. Is it because otherwise they couldn't be allocated again !?[/blue]

Well . . . you dont really. What compacting does as far as autonumber is concerned, is set the next autonumber to the highest used +1, per table. So if a tables has no records, autonumberis reset back to zero.

See Ya! . . . . . .

 

[khashyar7]

WOW ! That was a whole lot lol

I can't believe that you're doing this for free...but hey I'm not complaining

ok, back to the problem, I pretty much got all your points...just had a hard time setting up the hot key combination. I made the ^+(F9) macro and it ran no problem, but including it in the "AutoKeys" macro didn't quite work...The problem is that I do not have a "macro name" column listed in my macros. There is only two columns:
1. Action
2. Comment

Is that something that is switched off by default and I need to switch on in the options or something !?

Cheers,

Kash

 

[RoyVidar]

There's a button on the macro toolbar (when in "design view") called "macro names". Hint, open the help files, type "Autokeys" in the answer wizard.

Roy-Vidar

 

[khashyar7]

Thanks guys ! I did the autokey part when you guys advised me about 20 days back, but now going back to it I remember I never got around doing the "reset the autonumbers" thing....even though I googled the keyword and searched the help files found nothing quite helpfull !

Could you gimme a hint please !?

Cheers,

Kash

 

[mustangcoupe]

in the tools menu, under database utilities choose compact and repair. it should reset your autonumbers. BUT you shouldnt depend on the numbers being consecutive as during daily use (deleting records ect...) it will get out of sync.
and if you are in a multi user DB this will be even worse.

--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)