.ldb file stays when connection is closed

[Derrick]

I am working on a vb.net app that access an MS Access Data base.

I am having a confusing problem, and was hoping to get a little
clarification.

One form creates the .mdb file and creates the tables and constraintes using
DDL and a command object using its executeNonQuery() method. When I close
the connection, the .ldb file stays. I checked the connection state and it
reports closed.

In another form, I connect to the data base, retreive information using a
datareader and close the connection. The .ldb file disappears.

Both forms create an instance of same class and call the same methods to
connect and disconnect.

So my questions are:
1. should the .ldb file disappear when the connection is closed?

2. If it should, what could cause it to remain when the connection is
closed?

Thanks for your help

Derrick

 

[Scott M.]

My guess is that ADO.NET uses connection pooling, so even when you close
your connection, it is still in the pool?

 

[Kevin Yu [MSFT]]

Hi Derrick,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that there is an .ldb file remaining after
you have closed the connection to the .mdb file. If there is any
misunderstanding, please feel free to let me know.

Just a Scott mentioned, ADO.NET uses connection pooling by default. After
you have closed the connection, the connection object will not be disposed
actually. It is put back into the pool. It will only be disposed after its
lifetime has expired or the application is over. Pooling connections can
significantly enhance the performance and scalability of your application.

I think you can disable connection pooling by adding "OLE DB Services=-4;"
in the OleDb connection string. The .ldb file will disappear as soon as you
close the connection.

For more information about connection pooling in .NET OleDb data provider,
please check the following link:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconconnectingtooledbd
atasourceusingadonet.asp

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Paul Clement]

¤ I am working on a vb.net app that access an MS Access Data base.
¤
¤ I am having a confusing problem, and was hoping to get a little
¤ clarification.
¤
¤ One form creates the .mdb file and creates the tables and constraintes using
¤ DDL and a command object using its executeNonQuery() method. When I close
¤ the connection, the .ldb file stays. I checked the connection state and it
¤ reports closed.
¤
¤ In another form, I connect to the data base, retreive information using a
¤ datareader and close the connection. The .ldb file disappears.
¤
¤ Both forms create an instance of same class and call the same methods to
¤ connect and disconnect.
¤
¤ So my questions are:
¤ 1. should the .ldb file disappear when the connection is closed?

Yes.

¤
¤ 2. If it should, what could cause it to remain when the connection is
¤ closed?

Perhaps you have an implicit connection from one of your other data objects. What happens when you
terminate the application? Is the .LDB file removed?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Derrick]

Thanks for the quick response. You understood the problem correctly. I
tried using that property, but the .ldb is still there after the connection
is closed. When the entire application closes, the .ldb file disappears.

This is the connect string. Did I do anything wrong there?

Provider=Microsoft.Jet.OLEDB.4.0;OLE DB Services=-4; Data
Source=C:\delete.mdb.

Just in case I am pursuing the wrong angle, maybe we should examine my
logic. Here is what I am trying to do - is this a reasonable approach:

public sub create dataFile()
try
' create data base file
' connect to the file
' fill the data base with default data
catch ex as exception
' if file is created, delete it (doesn't work as long as there is
an .ldb file)
finally
' if connection is open, disconnect it
end try
end sub

Thanks for the help

Derrick

 

[Derrick]

Yes, when I leave the application, the .ldb file disappears.

Since I posted, I have not been able to duplicate the behavior << In another
form, I connect to the data base, retreive information using a datareader
and close the connection. The .ldb file disappears. >>.

As soon as I connect, the .ldb file appears and stays until the app is
closed regardless of the connection state. I control all the data base
connections through my 'dataConnector' object to make sure there is no
connections left open, so it is easy for me to test for any left over open
connections.

Derrick

 

[Scott M.]

Have you tried adding "OLE DB Services=-4;" in the OleDb connection string
as Kevin suggested?

 

[Derrick]

I did - the connect string was in the previous message...

Did I do that correctly?

Derrick

 

[Scott M.]

Sorry, I didn't see it there. Let me ask you this...You say that the .ldb
file is still there after the connection is closed, but is it still there
after the connection falls out of scope? Not that you should need to, but
have you tried setting the connection object to Nothing?

 

[Derrick]

No problem. I appreciate the help - this have been bugging me for a
while...

It is there when the connection is out of scope. I tried setting the
connection = nothing, but the pesky thing is still there.

Derrick

 

[Kevin Yu [MSFT]]

Hi Derrick,

Please also try to check if the .ldb file exists before the program is
going to run. If that file exists and cannot be deleted, please try to quit
VS.NET and delete it. If that still doesn't work, please reboot and try
again. Please let me know if you still cannot resolve it.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Paul Clement]

¤ Yes, when I leave the application, the .ldb file disappears.
¤
¤ Since I posted, I have not been able to duplicate the behavior << In another
¤ form, I connect to the data base, retreive information using a datareader
¤ and close the connection. The .ldb file disappears. >>.
¤
¤ As soon as I connect, the .ldb file appears and stays until the app is
¤ closed regardless of the connection state. I control all the data base
¤ connections through my 'dataConnector' object to make sure there is no
¤ connections left open, so it is easy for me to test for any left over open
¤ connections.

You might want to post your code if possible. I've seen this issue when using ADOX (Microsoft ADO
Ext 2.x for DDL and Security) but not ADO.NET and Jet OLEDB.

AFAIK, the Jet OLEDB Provider doesn't support connection pooling so I don't believe that is the
issue.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Scott M.]

AFAIK, the Jet OLEDB Provider doesn't support connection pooling so I
don't believe that is the issue.

But the OLEDB.OLEDBConnection (which will be the connection for the JET
Provider) does use pooling.

 

[Derrick]

Unfortunately, those were the first steps I took to try to isolate the
problem. I am going to try the code in another project to see if there is
something funny or corrupted with this project. I will post the results
when I have them.

Thanks again for the help.

Derrick

 

[Derrick]

I am posting this to both threads to follow up on both sources of
assistance.

I think I have found the problem. I used this code to create the .mdb file:
<snip>
Dim cat As Catalog = New Catalog()
cat.Create(Me.CreateString)
</snip>

That leaves the .ldb file there. When I bypass this code, create the .mdb
manually, the connect and disconnect using the connection property 'OLE DB
Services=-4;' , the .ldb disappears as it should.

Do I have to do anything when I use the catalog object to close the
connection?

Thanks again for all the help

Derrick

 

[Derrick]

I am posting this to both threads to follow up on both sources of
assistance.

I think I have found the problem. I used this code to create the .mdb file:
<snip>
Dim cat As Catalog = New Catalog()
cat.Create(Me.CreateString)
</snip>
Where CreateString = mProvider & mPath & mFileName & ";Jet OLEDB:Engine
Type=5"

That leaves the .ldb file there. When I bypass this code and create the
..mdb manually, call the connect and disconnect using the connection property
'OLE DB Services=-4;' , the .ldb disappears as it should.

Do I have to do anything when I use the catalog object to close the
connection?

Thanks again for all the help

Derrick

 

[Paul Clement]

¤ > AFAIK, the Jet OLEDB Provider doesn't support connection pooling so I
¤ don't believe that is the issue.
¤
¤ But the OLEDB.OLEDBConnection (which will be the connection for the JET
¤ Provider) does use pooling.
¤

Your implying that the native OLEDB library is overriding the lack of support for connection pooling
by the Jet OLEDB Provider. I don't see that behavior in my testing. Connections are destroyed
immediately upon release when explicitly closing the connection, other than in instances where there
is an object which continues to maintain a connection throughout the life of the application.

If there is connection pooling going on here, it's safe to say it's rather inconsistent and
unpredictable.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Scott M.]

Actually, I'm questioning whether the JET OLEDB Provider needs to support
connection pooling since this is not the connection itself, only the
instructions to the connection on what type of DB and where it is.

I was under the impression that connections to Access databases participate
in connection pooling just as all others in ADO.NET do.

Are you sure you are not confusing connection pooling in ADO with ADO.NET?

 

[Paul Clement]

¤ I am posting this to both threads to follow up on both sources of
¤ assistance.
¤
¤ I think I have found the problem. I used this code to create the .mdb file:
¤ <snip>
¤ Dim cat As Catalog = New Catalog()
¤ cat.Create(Me.CreateString)
¤ </snip>
¤
¤ That leaves the .ldb file there. When I bypass this code, create the .mdb
¤ manually, the connect and disconnect using the connection property 'OLE DB
¤ Services=-4;' , the .ldb disappears as it should.
¤
¤ Do I have to do anything when I use the catalog object to close the
¤ connection?

In the code you posted, I don't see where you are closing the connection for your ADOX Catalog
object. See if the following works:

cat.ActiveConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Derrick]

I feel like taking the day off now - finally it is fixed.

I had to modify your suggestion a bit (I had to cast the active connection
as a adodb.connection), but this worked.

Dim cat As Catalog = New Catalog
cat.Create(Me.CreateString)
CType(cat.ActiveConnection, ADODB.Connection).Close()

Thanks so much for your help.

Derrick