Save and Wipe

[tango]

We are using a database that at the end of the fiscal year gets copied then is saved as an archived (never really looked at again). Then all the information in the original is wiped the auto number is reset and we start all over again. Since we have new people coming and going it would be advisable to make the whole things as simple as possible. So saying that I was wondering is anyone happens to know where there would be an example of macros that would do that or a couple of them I could combine. Or have any other suggestions to do that would be great as well.

 

[bat17]

One way would be to use some thing like:-

Sub DelData()
Dim strSQL As String
DoCmd.SetWarnings False

strSQL = "DELETE Table1.* FROM Table1;"
DoCmd.RunSQL strSQL

strSQL = "DELETE Table2.* FROM Table2;"
DoCmd.RunSQL strSQL


DoCmd.SetWarnings True
End Sub

and work your way through all of the tables you want to clear.
Depending on the version of Access that you are using you can set Acces to compact on close, this should reset the Autonumbers for you, but some versions of Access wont reset Autonumbers on compact.

I would recomend adding a warning box to the code to stop someone running it accidently and wiping out all of your data though!!

Peter

 

[tango]

That works great, The compacting works but I don’t know how to do that on close of that one time only. The only other thing that I need to do is make a save location. If there is some way to record macro’s that I am unaware of then it would be pretty simple. I wouldn’t be surprised since I know Access no where near as good as excel and I only sort of know excel.

 

[tango]

Never mind on the compact on close. I found it under the options menu. Silly me

 

[bat17]

I don't know of a way to make Access save itself with a new name but you could create and run a batch file to copy it.
The following code will copy it with a new name based on the date.
This works OK on my Machine with W2K and AccessXP

Sub copyDb()
Dim str As String
Dim strCopyPath As String
Dim strSavePath As String
' path to save file to
strSavePath = "O:\Helpers\xxx\"
str = Format(Date, "YYYYMMDD")
str = "copy " & Application.CurrentProject.FullName & " " & strSavePath & str & ".mdb"
Open strSavePath & "CopyFile.bat" For Output As #1    ' Open file for output.
Print #1, str
Close #1    ' Close file.
Shell (strSavePath & "CopyFile.bat") ' run created .bat file
End Sub

HTH

Peter

 

[tango]

Thanks that worked. I was wondering if you or anyone else knows a site that has macro examples that might help a newbi learn how they work and what does what.

 

[bat17]

Helen Feddema has some good stuff on her site.

Peter[/url]