By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Delete all Tables

P: 2
I am trying to import tables using an odbc connection on a periodic basis. I don't want to create a second copy of the tables, so I have to delete them all before doing the import. I found another question similar to this and tried the following code, but it doesn't work.

Private Sub btnPBGUpdate_Click()
Dim TblName As String
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Search for closed Tables.
For Each obj In dbs.AllTables
TblName = obj.Name
If obj.IsLoaded = False Then
' Delete the tables.
If Not (Left(TblName, 4)) = "MSys" Then
Access.DoCmd.DeleteObject acTable, obj.Name
Else

End If
Next obj
End Sub

I get a compile error Next without For. I am very new to this, so I really don't know what I am doing and would appreciate any help. Thanks.
Jan 29 '08 #1
Share this Question
Share on Google+
3 Replies


P: 32
You mised the closeing "end if" for the "If obj.IsLoaded = False Then" see the bolded if

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnPBGUpdate_Click()
  2.     Dim TblName As String
  3.     Dim obj As AccessObject, dbs As Object
  4.  
  5.     Set dbs = Application.CurrentData
  6.  
  7.      '  Search for closed Tables.
  8.     For Each obj In dbs.AllTables
  9.         TblName = obj.Name
  10.         If obj.IsLoaded = False Then
  11.              ' Delete the tables.
  12.             If Not (Left(TblName, 4)) = "MSys" Then
  13.                 Access.DoCmd.DeleteObject acTable, obj.Name
  14.             Else
  15.  
  16.             End If
  17.         End if
  18.     Next obj
  19.     End Sub
I am trying to import tables using an odbc connection on a periodic basis. I don't want to create a second copy of the tables, so I have to delete them all before doing the import. I found another question similar to this and tried the following code, but it doesn't work.

Private Sub btnPBGUpdate_Click()
Dim TblName As String
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Search for closed Tables.
For Each obj In dbs.AllTables
TblName = obj.Name
If obj.IsLoaded = False Then
' Delete the tables.
If Not (Left(TblName, 4)) = "MSys" Then
Access.DoCmd.DeleteObject acTable, obj.Name
Else

End If
Next obj
End Sub

I get a compile error Next without For. I am very new to this, so I really don't know what I am doing and would appreciate any help. Thanks.
Jan 29 '08 #2

P: 2
Thanks. Now I get a message that says some of the tables can't be deleted because they are part of a relationship. Is there a way to programatically remove all relationships before running this script?
Jan 30 '08 #3

JustJim
Expert 100+
P: 407
Thanks. Now I get a message that says some of the tables can't be deleted because they are part of a relationship. Is there a way to programatically remove all relationships before running this script?
You would only have to re-enter the relationships. A better method might be to "Empty" the current tables, then append the incoming data to the same (but now empty) table. This keeps all references to the original table, eg relationships, queries, etc intact.

"Emptying" a table is as simple as opening an editable recordset, deleting a record, moving to the next record and continuing until the end of the recordset.

Air Code Warning: just typing off the top of my head here, following is not tested.
Expand|Select|Wrap|Line Numbers
  1. Private Sub SomeButton_Click()
  2.  
  3. Dim dbMyDatabase as Database
  4. Dim rsMyRecordset as Recordset
  5.  
  6. Set dbMyDatabase = CurrentDB()
  7. Set rsMyRecordset = dbMyDatabase.OpenRecordset("TableToEmpty", dbOpenDynaset)
  8.  
  9. Do Until rsMyRecordset.EOF
  10.      With rsMyRecordset
  11.           .Delete
  12.           .MoveNext
  13.      End With
  14. Loop
  15.  
  16. rsMyRecordset.Close
  17. set rsMyRecordset = Nothing
  18. Set dbMyDatabase = Nothing
  19. Exit Sub
Jim
Jan 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.