Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29

    Unhappy Unanswered: can't delete linked tables

    Hi there,

    I have a number of linked tables in my database. I can't delete a view of these tables anymore. I've tried pressing Delete+Yes, the DeleteObject action in a macro and VBA code. Nothing works. I also don't get an error message so I have no idea what might be going on.

    Anyone got an idea?
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Is it possible that you have the system warnings turned off?
    Go into one of you modules and open the immediate pane and
    type "Docmd.SetWarnings True". After that, try to delete one of
    the tables. If there is a problem, you should see it then.

    What about permissions? Maybe you've logged in as a user who
    doesn't have the proper permissions for deleting tables...

  3. #3
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    Thanks for your answer. I've tried your suggestion but it didn't work either. I get no warning at all. I also have a VBA function called DeleteTables. It loops through all tables and deletes them if they aren't system tables (MSys etc) using the DoCmd.DeleteObject command. When I place a breakpoint in this function, it shows me that things work "correctly". In other words, it doesn't jump to the Errorhandling code, so I'm guessing Access thinks nothing went wrong. However, the linked tables aren't deleted. This problem is driving me crazy...
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  4. #4
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    About permissions, I'm the developer of the app and have got all permissions to do whatever I want. Still deleting some of these linked tables doesn't work.
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    One other thought... although I don't know why this would work when
    other methods haven't.

    Open a new query and go into the SQL view and try this

    DROP TABLE yourtablename

  6. #6
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    Here is the code I use to delete all tables in a database. Something wrong with it?


    Code:
    '' verwijderd ALLE tabellen in de database
    Public Function VerwijderTabellen() As Boolean
    
        On Error GoTo Err_VerwijderTabellen
        ''On Error Resume Next
    
        Dim dbs As Object
        Dim tbl As AccessObject
        
        Set dbs = Application.CurrentData
        
        For Each tbl In dbs.AllTables
        
            If Left(tbl.Name, 4) <> "MSys" Then
            
                DoCmd.DeleteObject acTable, tbl.Name
                
            End If
            
        Next
        
        Set dbs = Nothing
        
        Exit Function
        
    Err_VerwijderTabellen:
        MsgBox Err.Description
    
    End Function
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The code seems like it should work...
    But if you can't even delete tables manually, the problem doesn't
    reside in your code. What is the source for the linked tables
    another Access database, Sql Server, ....?

  8. #8
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    Yeah, the code works for most tables. There are just a two of them it won't delete. Those are linked tables which link to just another Access database. However, that database resides in a directory (with lots of subdirectories) which has a very long name (120 characters). Maybe that's a problem?
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  9. #9
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    Forgot to mention, when I place the other Access database into another directory with a smaller name, everything works great. I can examine the tables, delete them, whatever. Maybe the connectionstring of my linked tables is too long?
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •