Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete object in remote db (Access 2000)

    What is the best way to delete an object in a remote db? I am afraid my function is not the best.
    I have a function for deleting a report in a remote database and replacing it with another report.
    There is something wrong with my second function KillObject, since on executing the command
    i receive the message Transfer dtabase

    Public Function ChangeReport()
    Dim appath As String
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Set ws = DBEngine.Workspaces(0)
    If Dir(strSo) <> "" Then
    appath = strSo
    Else
    'no database found so
    Exit Function
    End If


    Call KillObject(appath, 3, "ROffer")
    DoCmd.TransferDatabase acExport, "Microsoft Access", appath, acReport, "ROffer", "ROffer"

    End Function

    Public Sub KillObject(strDbName As String, acObjectType As Long, strObjectName As String)
    Dim adb As Object
    Set adb = CreateObject("Access.Application")
    adb.OpenCurrentDatabase (strDbName)
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    Set adb = Nothing
    End Sub

  2. #2
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete object in remote db (Access 2000)

    Dear Hans,

    Thank you so much for your reply.Would you please help me further ? I have followed your advice, but my
    function KillObject seem not to be in order and i get the error
    " User defined type not defined", especially on the line
    Dim adb As New Access.Database

    I will be grateful for your reply

    In order to test5 it i have made a from and i tried to perfrom the following in the OnClick event:




    Private Sub Command0_Click()
    KillObject strSo, acReport, "ROffers"
    End Sub

    Private Function KillObject(strDbName As String, acObjectType As Long, strObjectName

    As String)
    Dim adb As New Access.Database
    adb.OpenCurrentDatabase strDbName
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    adb.Quit
    Set adb = Nothing
    End Function

    Unfortunately i get the above message

    Please accept my best regards

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete object in remote db (Access 2000)

    Error in code corrected

    In ChangeReport, the variables db and ws are never used, and appath is not really needed, you can use strSo instead. In KillObject, you omit an essential instruction: you forget to quit the instance of Access you created. Apart from that, the code should work OK. The modified code looks like this:

    Public Function ChangeReport()
    If Dir(strSo) = "" Then Exit Function
    KillObject strSo, acReport, "ROffer"
    DoCmd.TransferDatabase acExport, "Microsoft Access", strSo, acReport, "ROffer", "ROffer"
    End Function

    Public Sub KillObject(strDbName As String, acObjectType As Long, strObjectName As String)
    Dim adb As New Access.Application
    adb.OpenCurrentDatabase strDbName
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    adb.Quit
    Set adb = Nothing
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete object in remote db (Access 2000)

    I'm sorry, that is my mistake. It should have been Access.Application instead of Access.Database:

    Dim adb As New Access.Application

    I have corrected my previous reply.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete object in remote db (Access 2000)

    Dear Hans,


    Thank you for your reply. I have amended my code accordingly, and now i get no error mesages,the report is indeed deleted in the remote database, but something strange occurs with this database.First, a second blank database is created and second i cannot open the remote database. I get the message "The source or destination file may be in use". So i cannot either open it or delete it. If i shut down the computer,
    and open it again, that eveyrhing is fine. So, the problem is, that i somehow freeze the database with my function. Is it possible to avoid it ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete object in remote db (Access 2000)

    There must be something else going on, for I have tested the code (with the name of a database and of a report that exist on my PC) and it works without problems.

    There is no reason that the code as posted should create a blank database; are you sure that it is not created by other code than the bit we are discussing here?

    And since the code uses CloseCurrentDatabase and quits the second instance of Access, the remote database should not be locked once the code has run.

    So I suggest that you take a closer look at the rest of your code to see if the cause can be found there.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete object in remote db (Access 2000)

    Are you sure you aren't referring to the LDB file of the same name as the remote database? That file is created when you open the connection to the other database and delete the object in it. While an LDB file is a database, it isn't used for the same purposes as an MDB.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete object in remote db (Access 2000)

    Dear Hans ans Dear Charlotte

    as usual you are right.You code is OK, i have seen now that i have foridden the closing action in mt db,and hence my errors.
    So the code works perfect !!!!

Posting Permissions

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