Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Independence, Kansas
    Posts
    22

    Unhappy Unanswered: Kill Database Error

    Gentlefolk,

    I wish mine would work like a charm. I have created a temporary database to do some work in for a form. When the form closes I receiving a Runtime Error 75: File/Path Access Error.

    The form is working properly but when I close it I get this error!

    I've been over the code many times and cannot figure out what is causing the error. The filepath is fine, I think access is the problem. Like I have some object open that is stopping the command from being executed. In the OnClose I am performing the following:
    1) Removing the temp db's tables from listbox RowSources
    2) Closing the recordsets to the temp db
    3) Closing the temp db
    4) Refreshing current db's tabledefs
    5) Setting temp db recordsets to nothing
    6) Setting the temp db to nothing
    7) deleting links to temp db in current db
    and finally failing at Killing the temp db

    This is driving me crazy. Does anyone have any thoughts to share?
    Regards, Kaniksu

  2. #2
    Join Date
    Dec 2002
    Posts
    18

    Cool Where exactly does it go wrong?

    If you debug, on what code line do you get stuck?

    If all works well and there doesn't seem to be any real problem you might consider putting "On Error Resume Next" just above the code. Not the most decent solution, but at least you will be able to close without error messages:-)

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    wouldn't hurt to Copy and Paste the code to us either
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Nov 2002
    Location
    Independence, Kansas
    Posts
    22
    Hello all,

    Poppekop,

    I have already done as you suggested "On Error Resume Next" until I get a fix and found another mind-blower code generation stopper. But that is another subject.

    m.timoney, my code would have to include all of the OnClose Event. We're talking a fair amount of code and I would not be miffed if you did not go through it all. I am working on a tight deadline for some sort of deliverable so I am holding off another inspection. But, although most likely blind-sided by my own overly focused perspective, I have been through it several times in attempts at resolution.

    With that warning said, the code lies below...
    ===============================


    Private Sub Form_Close()
    ' CLOSING THE FORM
    ' 1) Remove captions and visibility from form labels
    ' 2) Remove RowSource values from Listboxes
    ' 3) Delete any records in the temporary tables
    ' 4) Close recordsets to temp tables
    ' 5) Set the recordsets to nothing
    ' 6) Unlink the tables
    ' 7) Delete the temp database


    ' 1)REMOVE CAPTIONS TO THE FORM LABELS
    ' And make sure they are invisible again
    '

    Dim lblName As String
    Dim rstDays As Recordset
    Dim Counter As Integer
    Set dbs = CurrentDb
    Set rstDays = dbs.OpenRecordset("SELECT [Lookupays].Days " & _
    "FROM [Visitation Schedule] INNER JOIN [Lookupays] " & _
    "ON [Visitation Schedule].Day = [Lookupays].Day_Order " & _
    "WHERE [Visitation Schedule].Client_ID = " & gblClient_ID & _
    " AND [Visitation Schedule].Week = " & 1 & _
    " ORDER BY [Lookupays].Day_Order")

    rstDays.MoveFirst
    rstDays.MoveLast
    rstDays.MoveFirst
    Day_Counter = rstDays.RecordCount
    Counter = 0

    With rstDays
    Do Until rstDays.EOF
    lblName = "label_" & Counter + 1
    Me(lblName).Caption = ""
    Me(lblName).Visible = False
    Counter = Counter + 1
    .MoveNext
    Loop
    End With

    ' 2) REMOVE ALL TABLE NAMES FROM LISTBOX ROWSOURCES
    ' If this is not done Access returns a Error 3078
    ' when next opened.
    '

    Dim List_Clone As String
    Dim Table_Clone As String
    List_Clone = "lstRooms_"
    Table_Clone = "Day_Table_"
    Counter = 1

    Do Until Counter > Day_Counter
    Me(List_Clone & Trim(Str(Counter))).RowSource = ""
    Counter = Counter + 1
    Loop

    ' 3) DELETE ANY RECORDS IN THE TEMP TABLE
    ' There are 2 reasons, first for completeness
    ' and 2ndly because I cannot kill the temp db
    ' yet.
    '

    CurrentDb.Execute "delete * from Temp_Rooms"

    Dim Table_Name As String
    Table_Name = "Day_Table_"
    Counter = 1
    Do Until Counter > Day_Counter
    CurrentDb.Execute "delete * from " & Table_Name & Trim(Str(Counter))
    Counter = Counter + 1
    Loop

    ' 4) CLOSE THE RECORDSETS TO TEMP TABLES
    '
    rst_Trooms.Close
    rst_temp1.Close
    rst_temp2.Close
    rst_temp3.Close
    rst_temp4.Close
    rst_temp5.Close
    rst_temp6.Close
    rst_temp7.Close
    dbsTemp.Close

    dbs.TableDefs.Refresh

    ' 5) SET RECORDSETS TO NOTHING
    '
    Set rst_Trooms = Nothing
    Set rst_temp1 = Nothing
    Set rst_temp2 = Nothing
    Set rst_temp3 = Nothing
    Set rst_temp4 = Nothing
    Set rst_temp5 = Nothing
    Set rst_temp6 = Nothing
    Set rst_temp7 = Nothing

    Set dbsTemp = Nothing


    ' 6) UNLINK THE TABLES
    '
    dbs.TableDefs.Delete "Temp_Rooms"
    dbs.TableDefs.Delete "Day_Table_1"
    dbs.TableDefs.Delete "Day_Table_2"
    dbs.TableDefs.Delete "Day_Table_3"
    dbs.TableDefs.Delete "Day_Table_4"
    dbs.TableDefs.Delete "Day_Table_5"
    dbs.TableDefs.Delete "Day_Table_6"
    dbs.TableDefs.Delete "Day_Table_7"


    ' 7) DELETE THE TEMP DATABASE
    ' Getting Run Time Error 75 here File/Path access error
    '
    strTempDatabase = Left$(CurrentDb.name, Len(CurrentDb.name) - 4) & "temp.mdb"
    ' Kill (strTempDatabase)

    End Sub
    Regards, Kaniksu

  5. #5
    Join Date
    Dec 2002
    Posts
    18
    could it be because you didn't give a path with the databasename? I'm not sure if name gives that information.
    Last edited by Poppekop; 12-10-02 at 10:49.

  6. #6
    Join Date
    Nov 2002
    Location
    Independence, Kansas
    Posts
    22
    Poppekop,
    No, that isn't it either. I don't know how it does it, but Access picks up the entire filepath and filename. I am attaching a zip file of the demo database I downloaded from Tony Toews, Granite Consulting that really is quiet valuable if you haven't done this sort of thing (like me).

    In fact, I am convinced that the filepath name is not the problem at all. I have hardcoded it to be all under 8 chars in each segment of the path and the filename and the same error is returned. In addition, Tony's demo will show the filepath in debug and like mine, it has the tilde but his works flawlessly.

    No, there is some aspect of these objects relating the two databases that I am no breaking in the OnClose event that is causing the error. I just don't know what!
    Attached Files Attached Files
    Regards, Kaniksu

Posting Permissions

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