Automatically clearing data

[retro]

I have been tweaking my friend's database for him. I included some sample data in the main table (ficticious names and addresses etc.) and in a linked table.

I want to leave the sample data in so my friend understands how the database works. However, once he has tested it, he will want to blank those tables (not all tables, though), and reset the primary keys to start from 1 again.

Can I have a button on a form that will do this? Obviously with an OK/cancel message box. If so, how?

The tables are called Main and Workshops.

Thanks in advance for any help!

 

[Matt Greatorex]

You could always do soemthing along the following lines:

Behind the OnClick event of the button you're adding,

Dim str_Msg As String
Dim str_SQL as String

str_Msg = "Do you wish to remove all data?"

If MsgBox(str_Msg, vbOKCancel) = vbOK Then
str_SQL = "DELETE FROM Main;"
docmd.runsql

str_SQL = "DELETE FROM Workshops;"
docmd.runsql
End If

 

[retro]

Thanks for the reply!

Unfortunately, when I click the button, I get:

Compile error:

Argument not optional

And it highlights .RunSQL

Have I missed something obvious out here? If it helps, I'm using Access 2003

 

[Matt Greatorex]

Ah yes, that would be because I'm an idiot

You need to tell the code which SQL to run. At present, it doesn't know, which is why you're bing prompted to tell it.

We created the str_SQL string, assigned two values to it, but didn't actually name it when we were trying to run the command.
Try altering the bit within the 'IF' statement to

str_SQL = "DELETE FROM Main;"
docmd.runsql str_SQL

str_SQL = "DELETE FROM Workshops;"
docmd.runsql str_SQL

Sorry about that.

 

[retro]

That works great - thanks!

Only thing is, the primary key has kept going - for example, I had a table with 6 entries, and the primary key when I blank it starts at 7. How can I make this reset at the same time?

Thanks for the help! Much appreciated

 

[neileg]

Why does it matter?