Removing Queries with Code

[MikeAngelastro]

Hi,

Does anyone know the VBA code to remove all queries, forms, reports, etc from a given Access database? Removong them manually takes too long.

Thanks,

Mike

 

[dcx693]

Using DAO or ADO? If you're not sure, which version of Access are you using?

 

[MikeAngelastro]

Using DAO.

 

[SforSoftware]

Public Function DeleteAllTables()
  Dim tbl As Obejct

  For Each tbl in currentdata.alltables
    Docmd.deleteobject tbl.name
  next tbl
End Function

tables/queries are in currentdata, forms/reports are in currentproject

 

[MikeAngelastro]

Thanks Bert.

 

[SforSoftware]

pleased to help you

 

[dcx693]

I thought we were looking for DAO code.

 

[SforSoftware]

No, we were looking for the question 'how to remove access-objects via code'

It's also possible with DAO and ADO, but why should you make it yourself difficult if it's so easy

greetz,
Bert

 

[Uncle Gizmo]

I couldn't get it to work anyway !

 

[SforSoftware]

I couldn't get it to work anyway

What's the problem you've?

 

[dcx693]

What's the problem you've?

If you look at my question above, I asked the user if he/she needed ADO or DAO. The user replied with "DAO". That's the problem.

 

[SforSoftware]

okay, that seems to be your problem, but not the of uncle gizmo

 

[Uncle Gizmo]

What's the problem you've?

Well besides the fact that I haven't got a clue how how it works, I place this code:

Public Function fConList()

 Dim tbl As Object

  For Each tbl In CurrentData.AllTables
    DoCmd.DeleteObject tbl.Name
  Next tbl

End Function

in a module and I called it with a command button on a form and I get the following error message:

This action requires an Object Name argument

Do I need to numerate the collection and loop through it ?

 

[SforSoftware]

okay, you're right, my code isn't complete (because I wrote it in the forum-editor )
it must be:

Public Function fConList()
  Dim tbl As AccessObject
  
  For Each tbl In CurrentData.AllTables
    If Left(tbl.Name, 4) <> "msys" Then
      DoCmd.DeleteObject acTable, tbl.Name
    End If
  Next tbl
End Function

 

[Uncle Gizmo]

Brilliant , all of my tables have gone !

How do I get them back ?











Only joking he he he I know they are gone for good

 

[SforSoftware]

great

 

[Pat Hartman]

Delete the broken db and copy the backup copy. If you are trying to split a db, there is a wizard. You don't have to do this manually. Also, if you want to split the db manually, it's easier to create a new empty db and import just the tables. Then delete the tables from the original db and add links.