Access won't close after referencing to another db (1 Viewer)

[smig]

here is the situation

I made a small Access db that act as an API to another db.
you might remember this:
http://www.access-programmers.co.uk/forums/showthread.php?t=189300

I don't won't to expose my tables to theend user so I crated a small db that connect to the tables and use few functions to add/modify records in the tables.
the end user add this little API db as reference and send data using the exposed functions.

it all work very good

the only problem is after quiting (docmd.quit) the application (The user's main application) the Access main window won't close.
there is no hiden window there. using the exit command won't help.
the only way to shut it is using the Task manager.

 

[vbaInet]

Wouldn't it be Application.Quit?

 

[smig]

nope.
not helping

docmd.quit do quit from the application.
only main Access background won't close.

 

[vbaInet]

Did this start happening after you wrote your DLL?

 

[smig]

it's not a DLL. it's access MDB that I mad aka like API
and yes, it's only after I run commands that use this db.

 

[SOS]

Well, I think seeing the code would be an important step here.

 

[vbaInet]

I quickly glanced through the link and remembered it was something to do with a DLL so I thought that was it

There must be something in your code that is causing the Application to hang. Have you tried setting all objects to NOTHING and running a code to close all open forms and reports before closing?

 

[SOS]

And if you don't reference the items properly it can cause a hanging application until you close your database.

 

[ChrisO]

Could you please post the faulty database before it gets changed?

 

[smig]

I'll explain again:
I do close the database, either using Docmd.Quit or using the Access menu Exit command.
I left with is the Access shell (frame and gray background with the original built in menues). no open db, no hidden db.
using the Exit command won't exit and won't close the Access shell.

here is the code of the AKA API db

Option Compare Database

Function fn_NewClient(CustomerName, IDnum, Address, PostalCode, City, PhoneNumber)

On Error GoTo Errors
Dim ToIskitdb As DAO.Database
Dim Recordset_Students As DAO.Recordset
Dim LastStudentID As String
Set ToIskitdb = OpenDatabase("C:\Gal-On\ToIskit.mdb")
Set Recordset_Students = ToIskitdb.OpenRecordset("SELECT * FROM [Students] ORDER BY [Students].[StudentID]")
If Recordset_Students.RecordCount > 0 Then
    Recordset_Students.MoveLast
    LastStudentID = Recordset_Students.Fields("StudentID")
Else
    LastStudentID = 0
End If
Recordset_Students.AddNew
Recordset_Students.Fields("StudentID") = Val(LastStudentID) + 1
Recordset_Students.Fields("StudentName") = CustomerName
Recordset_Students.Fields("IDnum") = IDnum
Recordset_Students.Fields("Address") = Address
Recordset_Students.Fields("PostalCode") = PostalCode
Recordset_Students.Fields("City") = City
Recordset_Students.Fields("PhoneNumber") = PhoneNumber
    fn_NewClient = Recordset_Students.Fields("StudentID")
Recordset_Students.Update
Recordset_Students.Close
Set Recordset_Students = Nothing

Errors:
If Err <> 0 Then
    MsgBox "ToIskit.fn_NewClient" & Chr(13) & "îñ' ùâéàä: " & Err & Chr(13) & "úàåø ùâéàä: " & Err.Description
End If
End Function
 

Function fn_NewInvoice(MisparHeshbonit As String, CustomerID, CustomerName As String, InvoiceDate, Maam, Notes, CustomerAddress, CustomerCompany, NoOffInvoiceItems, InvoiceItems)
On Error GoTo Errors
Dim ToIskitdb As DAO.Database
Dim Recordset_Invoices As DAO.Recordset
Dim Recordset_InvoiceDetails As DAO.Recordset
Dim i As Integer
Dim MinInvoiceID As Long
Dim NextInvoiceID As Long
MinInvoiceID = 100000
Set ToIskitdb = OpenDatabase("C:\Gal-On\ToIskit.mdb")
' --- invoice
Set Recordset_Invoices = ToIskitdb.OpenRecordset("SELECT * FROM [Invoices] ORDER BY [Invoices].[InvoiceID]")
If MisparHeshbonit = 0 Then
    If Recordset_Invoices.RecordCount > 0 Then
        Recordset_Invoices.MoveLast
        NextInvoiceID = Recordset_Invoices.Fields("InvoiceID") + 1
    Else
        NextInvoiceID = MinInvoiceID
    End If
Else                    ' --- Pinkas Yadani
    NextInvoiceID = MisparHeshbonit
End If
Recordset_Invoices.AddNew
Recordset_Invoices.Fields("InvoiceID") = NextInvoiceID
Recordset_Invoices.Fields("CustomerID") = CustomerID
Recordset_Invoices.Fields("cus_name") = CustomerName
Recordset_Invoices.Fields("InvoiceDate") = InvoiceDate
Recordset_Invoices.Fields("Maam") = Maam / 100
Recordset_Invoices.Fields("Notes") = Notes
Recordset_Invoices.Fields("cAddress") = CustomerAddress
Recordset_Invoices.Fields("cCompany") = CustomerCompany
Recordset_Invoices.Fields("incomeS") = 1
Recordset_Invoices.Fields("CurrName") = 1
If MisparHeshbonit <> 0 Then       ' --- Pinkas Yadani
    Recordset_Invoices.Fields("Printed") = True
End If
Recordset_Invoices.Update
Recordset_Invoices.Close
Set Recordset_Invoices = Nothing
' --- invoice details
Set Recordset_InvoiceDetails = ToIskitdb.OpenRecordset("Invoice Details")
For i = 0 To NoOffInvoiceItems - 1
    Recordset_InvoiceDetails.AddNew
    Recordset_InvoiceDetails.Fields("InvoiceID") = NextInvoiceID
    Recordset_InvoiceDetails.Fields("Product") = InvoiceItems(i, 0)
    Recordset_InvoiceDetails.Fields("Qty") = InvoiceItems(i, 1)
    Recordset_InvoiceDetails.Fields("Price") = InvoiceItems(i, 2)
    Recordset_InvoiceDetails.Update
Next i
Recordset_InvoiceDetails.Close
Set Recordset_InvoiceDetails = Nothing

fn_NewInvoice = NextInvoiceID
Errors:
If Err <> 0 Then
    MsgBox "ToIskit.fn_NewInvoice" & Chr(13) & "îñ' ùâéàä: " & Err & Chr(13) & "úàåø ùâéàä: " & Err.Description
End If
End Function


Function fn_NewInvoiceReceipt(MisparHeshbonit As String, CustomerID, CustomerName As String, InvoiceDate, Maam, Notes, CustomerAddress, CustomerCompany, NoOffInvoiceItems, InvoiceItems, NoOffReceiptItems, ReceiptItems)
On Error GoTo Errors
Dim ToIskitdb As DAO.Database
Dim Recordset_Kabala_Invoice As DAO.Recordset
Dim Recordset_KabalaInvoice_Details_INV As DAO.Recordset
Dim Recordset_KabalaInvoice_Details_Rec As DAO.Recordset
Dim i As Integer
Dim MinKabalaInvoiceID As Long
Dim NextKabalaInvoiceID As Long
MinKabalaInvoiceID = 200000
Set ToIskitdb = OpenDatabase("C:\Gal-On\ToIskit.mdb")
' --- Kabala_Invoice
Set Recordset_Kabala_Invoice = ToIskitdb.OpenRecordset("SELECT * FROM [Kabala_Invoice] ORDER BY [Kabala_Invoice].[InvoiceID]")
If MisparHeshbonit = 0 Then
    If Recordset_Kabala_Invoice.RecordCount > 0 Then
        Recordset_Kabala_Invoice.MoveLast
        NextKabalaInvoiceID = Recordset_Kabala_Invoice.Fields("InvoiceID") + 1
    Else
        NextKabalaInvoiceID = MinKabalaInvoiceID
    End If
Else                    ' --- Pinkas Yadani
    NextKabalaInvoiceID = MisparHeshbonit
End If

Recordset_Kabala_Invoice.AddNew
Recordset_Kabala_Invoice.Fields("InvoiceID") = NextKabalaInvoiceID
Recordset_Kabala_Invoice.Fields("CustomerID") = CustomerID
Recordset_Kabala_Invoice.Fields("cus_name") = CustomerName
Recordset_Kabala_Invoice.Fields("InvoiceDate") = InvoiceDate
Recordset_Kabala_Invoice.Fields("Maam") = Maam / 100
Recordset_Kabala_Invoice.Fields("Notes") = Notes
Recordset_Kabala_Invoice.Fields("cAddress") = CustomerAddress
Recordset_Kabala_Invoice.Fields("cCompany") = CustomerCompany
Recordset_Kabala_Invoice.Fields("incomeS") = 1
Recordset_Kabala_Invoice.Fields("CurrName") = 1
If MisparHeshbonit <> 0 Then       ' --- Pinkas Yadani
    Recordset_Kabala_Invoice.Fields("Printed") = True
End If
Recordset_Kabala_Invoice.Update
Recordset_Kabala_Invoice.Close
Set Recordset_Kabala_Invoice = Nothing
' --- invoice details
Set Recordset_KabalaInvoice_Details_INV = ToIskitdb.OpenRecordset("KabalaInvoice_ Details_INV")
For i = 0 To NoOffInvoiceItems - 1
    Recordset_KabalaInvoice_Details_INV.AddNew
    Recordset_KabalaInvoice_Details_INV.Fields("InvoiceID") = NextKabalaInvoiceID
    Recordset_KabalaInvoice_Details_INV.Fields("Product") = InvoiceItems(i, 0)
    Recordset_KabalaInvoice_Details_INV.Fields("Qty") = InvoiceItems(i, 1)
    Recordset_KabalaInvoice_Details_INV.Fields("Price") = InvoiceItems(i, 2)
    Recordset_KabalaInvoice_Details_INV.Update
Next i
Recordset_KabalaInvoice_Details_INV.Close
Set Recordset_KabalaInvoice_Details_INV = Nothing
' --- kabala details
Set Recordset_KabalaInvoice_Details_Rec = ToIskitdb.OpenRecordset("KabalaInvoice_ Details_Rec")
For i = 0 To NoOffReceiptItems - 1
    Recordset_KabalaInvoice_Details_Rec.AddNew
    Recordset_KabalaInvoice_Details_Rec.Fields("invoceID") = NextKabalaInvoiceID
    Recordset_KabalaInvoice_Details_Rec.Fields("SugTashlum") = ReceiptItems(i, 0)
    Recordset_KabalaInvoice_Details_Rec.Fields("Price") = ReceiptItems(i, 1)
    Recordset_KabalaInvoice_Details_Rec.Fields("Bank") = ReceiptItems(i, 2)
    Recordset_KabalaInvoice_Details_Rec.Fields("branch") = ReceiptItems(i, 3)
    Recordset_KabalaInvoice_Details_Rec.Fields("CheckNUm") = ReceiptItems(i, 4)
    Recordset_KabalaInvoice_Details_Rec.Fields("Acount") = ReceiptItems(i, 5)
    Recordset_KabalaInvoice_Details_Rec.Fields("PayDate") = ReceiptItems(i, 6)
    Recordset_KabalaInvoice_Details_Rec.Fields("Tashlumim") = 1
    Recordset_KabalaInvoice_Details_Rec.Update
Next i
Recordset_KabalaInvoice_Details_Rec.Close
Set Recordset_KabalaInvoice_Details_Rec = Nothing
fn_NewInvoiceReceipt = NextKabalaInvoiceID

Errors:
If Err <> 0 Then
    MsgBox "ToIskit.fn_NewInvoiceReceipt" & Chr(13) & "îñ' ùâéàä: " & Err & Chr(13) & "úàåø ùâéàä: " & Err.Description
End If
End Function

Function fn_NewReceipt(MisparKabala As String, IskitCustomerID, CustomerName As String, ReceiptDate, Maam, Notes, CustomerAddress, CustomerCompany, NoOffReceiptItems, ReceiptItems)
On Error GoTo Errors
Dim ToIskitdb As DAO.Database
Dim Recordset_Receipt As DAO.Recordset
Dim Recordset_ReceiptDetails As DAO.Recordset
Dim i As Integer
Dim MinReceiptID As Long
Dim NextReceiptID As Long
MinReceiptID = 300000
Set ToIskitdb = OpenDatabase("C:\Gal-On\ToIskit.mdb")
' --- Receipt
Set Recordset_Receipt = ToIskitdb.OpenRecordset("SELECT * FROM [receipt] ORDER BY [receipt].[receiptID]")
If MisparKabala = 0 Then
    If Recordset_Receipt.RecordCount > 0 Then
        Recordset_Receipt.MoveLast
        NextReceiptID = Recordset_Receipt.Fields("receiptID") + 1
    Else
        NextReceiptID = MinReceiptID
    End If
Else                    ' --- Pinkas Yadani
    NextReceiptID = MisparKabala
End If

Recordset_Receipt.AddNew
Recordset_Receipt.Fields("receiptID") = NextReceiptID
Recordset_Receipt.Fields("CustomerID") = IskitCustomerID
Recordset_Receipt.Fields("cus_name") = CustomerName
Recordset_Receipt.Fields("receiptDate") = ReceiptDate
Recordset_Receipt.Fields("Notes") = Notes
Recordset_Receipt.Fields("Maam") = Maam / 100
Recordset_Receipt.Fields("cAddress") = CustomerAddress
Recordset_Receipt.Fields("cCompany") = CustomerCompany
Recordset_Receipt.Fields("Saifkabala") = 1
Recordset_Receipt.Fields("CurrName") = 1
If MisparKabala <> 0 Then       ' --- Pinkas Yadani
    Recordset_Receipt.Fields("Printed") = True
End If
Recordset_Receipt.Update
Recordset_Receipt.Close
Set Recordset_Receipt = Nothing

' --- kabala details
Set Recordset_ReceiptDetails = ToIskitdb.OpenRecordset("receiptDetails")
For i = 0 To NoOffReceiptItems - 1
    Recordset_ReceiptDetails.AddNew
    Recordset_ReceiptDetails.Fields("receiptID") = NextReceiptID
    Recordset_ReceiptDetails.Fields("SugTashlum") = ReceiptItems(i, 0)
    Recordset_ReceiptDetails.Fields("Price") = ReceiptItems(i, 1)
    Recordset_ReceiptDetails.Fields("Bank") = ReceiptItems(i, 2)
    Recordset_ReceiptDetails.Fields("branch") = ReceiptItems(i, 3)
    Recordset_ReceiptDetails.Fields("CheckNUm") = ReceiptItems(i, 4)
    Recordset_ReceiptDetails.Fields("Acount") = ReceiptItems(i, 5)
    Recordset_ReceiptDetails.Fields("PayDate") = ReceiptItems(i, 6)
    Recordset_ReceiptDetails.Fields("Tashlumim") = 1
    Recordset_ReceiptDetails.Update
Next i
Recordset_ReceiptDetails.Close
Set Recordset_ReceiptDetails = Nothing
fn_NewReceipt = NextReceiptID

Errors:
If Err <> 0 Then
    MsgBox "ToIskit.fn_NewReceipt" & Chr(13) & "îñ' ùâéàä: " & Err & Chr(13) & "úàåø ùâéàä: " & Err.Description
End If
End Function

 

[SOS]

Well, that should be an easy fix. Nowhere in your functions do you get rid of your database object: ToIskitdb

You need to add this at the end of EACH function:

ToIskitdd.Quit
Set ToIskitdb = Nothing

and that should fix your problem.

 

[LPurvis]

Morning all...

>> Set ToIskitdb = Nothing
>> and that should fix your problem

Well, that's just it. Would it - and even if it did - what version of Access are we talking about here.
And also, before attempting such a fix do bear in mind the earlier request:

>> Could you please post the faulty database before it gets changed?

Chris is thinking the same thing as me. The golden egg that would be a repro.
That's what we need.

Believe me - people come to us lot for help... But for us to get our hands on a genuinely failing example (i.e. that's not fixed by a decompile or explicit boolean control comparison) would be us getting something we want.

So please do consider that.
(But try the decompile first and look for boolean comparisons of bound data that don't use =True).

Cheers.

 

[smig]

I'll try what SOS suggested, later when I get back home.
as I closed only the recordsource and not the db itself it make sense.
thanks

there is no faulty database.
the db works OK and quit access OK if I don't try to activate any of the functions on ToIskitdb.

 

[LPurvis]

>> there is no faulty database.
>> the db works OK and quit access OK if I don't try to activate any of the functions on ToIskitdb

Exactly - that's what we're asking about.
Do you not want to post the app?

 

[smig]

Thanks SOS it worked

though it should to be ToIskitdb.Close and not ToIskitdb.Quit


@LPurvis
I posted the full code of the AKA API application.
it's only contain few functios to get data from one db to put into another without exposing the tables of the second one.

I see no reason to post the main db here as it never caused any problem before, working alone.
why would you want to try to dig into a 20-30 tables, 40-50 queries and another 40-50 forms db just to look for something I'm sure is not there ?

 

[vbaInet]

Thanks SOS it worked

though it should to be ToIskitdb.Close and not ToIskitdb.Quit


@LPurvis
I posted the full code of the AKA API application.
it's only contain few functios to get data from one db to put into another without exposing the tables of the second one.

I see no reason to post the main db here as it never caused any problem before, working alone.
why would you want to try to dig into a 20-30 tables, 40-50 queries and another 40-50 forms db just to look for something I'm sure is not there ?

I'm sure Leigh has good intentions and I'm sure he has dealt with much larger databases. He has his reasons why he's asked for the working part of your db, but it's up to you to show him.

 

[LPurvis]

>> why would you want to try to dig into a 20-30 tables, 40-50 queries and another 40-50 forms db just to look for something I'm sure is not there ?

Well...
Let's just say that perhaps that's a difference between us.

But it's not your application per se that is of interest (no offense of course - I'm sure it's super and you're right to be mindful of your own IP).
It's the instantiation, use and destruction of the target API db that was of importance.
Even just a statement of how it's implemented.
All we can do at the moment is infer how your API code is accessed from a statement in the previous related thread where you say "I want it to be registered into Access through the objects references".

So does that imply that you then added this MDB as a reference in your application?

Even if this issue could be pointed to some other issue it would be of benefit.
(I have nothing to gain by proofs of DAO memory leaks being found - despite having coded against it for years - as such a leak wouldn't do anything to strengthen DAO's reputation. But it would be interesting to test the same scenario in 2007, 2010 and so on and let MS know if it's ongoing).

Cheers.

 

[smig]

I'm sure Leigh has good intentions and I'm sure he has dealt with much larger databases

I'm sure he does. never though he had any other intention.

Yes, I said at the begining of the thread that this MDB is used in an API style.
I register it into the main db referneces, and use it by it's functions only.
at first I thought making the API code into a DLL, but later decided to simply make it into an MDB file that will be used just the same.

I must say that I realy start to like this idea.
with this technique there is no way for the ed users to go directly into tables and change them.

 

[SOS]

Thanks SOS it worked

though it should to be ToIskitdb.Close and not ToIskitdb.Quit

Yeah, had it slightly off. But, after looking at the code it was clear, at least to me (if not the others) that normally you don't have to worry about a database variable when it is referring to the current database, but when you are dealing with ANOTHER database then it is something completely different and as using that variable it would appear to me that it does something when opening up that database and it would need to be closed.

So, glad that it worked.

 

[ChrisO]

I fear another (possible) golden egg has just been scrambled.