Re: "If ......Exists'.... Then Run SQL code"
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 11/29/04
- Next message: Angi: "passing text variable to SQL statement"
- Previous message: Graham R Seach: "Re: Determining whether Excel is running"
- In reply to: Gettingthere: "Re: "If ......Exists'.... Then Run SQL code""
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 12:04:29 +0800
Roger, here is the code to delete all records from a table, and reset the
Seed of the AutoNumber field to 1.
To use it:
1. Click on the Modules tab of the Database window.
2. Click New. Access opens a code window.
3. From the Tools menu, choose References.
4. Check the box beside:
Microsoft ADO Ext. 2.x for DLL and Security
5. Paste in the code below.
6. Check that it compiles: Compile on Debug menu.
7. Save the module. A name like Module1 is okay.
To use the code to delete all records from a table named "MyTempTable", use
a macro with the RunCode action, and in the lower pane of macro design,
enter:
DeleteAllAndResetAutoNum("MyTempTable")
-------------code starts----------------
Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String
'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql
'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
-------code ends-------------
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Gettingthere" <Gettingthere@discussions.microsoft.com> wrote in message news:EC4D2E1C-5B54-4746-A761-5EBD3E5BE400@microsoft.com... > No doubt you are 100% correct. Do to my level of knowledge (nil) in > programming (writing scripts), I have ended up in this format. The > biggest > hurdle was trying to group records in batches of 25 grouped by dates AND > list in a reporr same order as entered. Very dificult without knowing any > programming. > Always want to improve anything I do , any other suggestions would be > appreciated. > > Thanks > > Roger
- Next message: Angi: "passing text variable to SQL statement"
- Previous message: Graham R Seach: "Re: Determining whether Excel is running"
- In reply to: Gettingthere: "Re: "If ......Exists'.... Then Run SQL code""
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
