cherian

I am accessing an Access database through my VB.NET
application. I would like to programatically compact the
Access database. The problem that I have is for some
reason the Access database gets locked up, even after I
have closed all the recordset connections. I have narrowed
it down to the line where I am doing a "Fill" on the
OleDbDataAdapter.

My connection opening code looks as below...

-----------------------------------------------------------
--

sSQL = "SELECT * FROM ....."
sConnect = "Provider=......"
connection = New OleDbConnection(sConnect)
dataAdapter = New OleDbDataAdapter(sSQL, connection)
dsDatasources = New DataSet()
dataAdapter.Fill(dsDatasources, "Table1") 'THIS IS THE
PROBLEM LINE.
dvDatasources = New DataView(dsDatasources.Tables
("Table1"))

-----------------------------------------------------------
--

Before compacting the Access DB, I have the following line
of code to close all connections, etc...

-----------------------------------------------------------
--

dataAdapter.Dispose()
dsDatasources.Clear()
dsDatasources.Dispose()
connection.Dispose()
connection.Close()
Application.DoEvents() 'Just in case OS wants to catch up
on some tasks.

dvDatasources = Nothing
dsDatasources = Nothing
dataAdapter = Nothing
connection = Nothing

-----------------------------------------------------------
--

Even after doing all this cleanup stuff, I still get the
error of "File is being used by some other user". If I
comment out the line of "dataAdapter.Fill" then everything
goes fine.

Also, what I have noticed is that as soon as the Fill
command is executed, I have the Access lock ".ldb" file
created. I cannot delete this file, till I close my
application.

Any suggestions on what I can do to resolve this problem
would be highly appreciated.

Thanks much,
Cherian.