closing connections to database

You can use ADO to compact the database. See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q230501 for an example.

As for the closing, close every connections and close every forms that have a ADODC control on it.

if I don't want to close those forms that have a ADODC control on it because I have a ADODC control in my main form. How can I do that?

If you set the connection using the ADODC control, it keeps an handle on the connection as long as the form is open (and thus you won't be able to compact your database).

If you use a regulare connection and pass a recordset object to the ADODC control, you can close it (adodc1.recordset.close) and close the connections.

I understood that first part but not the second one. Regulare connection...?

>>Regulare connection

dim cn as new ADODB.Connection
dim rst as new adodb.recordset

cn.connectionstring = "..."
cn.open

rst.open "select * from table1", cn
set adodc1.recordset = rst

I guess you already know what I'm trying to do here...

I wanna delete some records from my database and then compact it.

You gave me already good answer to that delete-part, but now there is this compacting part left.

I do this in the main form of my application:

myAdo.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
     & "Data Source= " & myPath & "/db.mdb;"
  myAdo.RecordSource = "SELECT * FROM myTbl ORDER BY myField"

so can I close this kind of connections to that database so that I could compact it?

You can't close this connection (because the ADODC can't left its handle).

Some thoughts:

-You don't need to compact the database everytime you delete data. Therefore, you should think about compacting the database before loading the main form. Use a "Sub Main" as your startup object and compact the database before loading your form.

-Are you aware that you don't need an ADODC control for binding? For example, you can open a recordset using ADO objects and bind a grid to it without using the ADODC control:

dim cn as new ADODB.Connection
dim rst as new adodb.recordset

cn.connectionstring = "..."
cn.open

rst.open "select * from table1", cn
set grid1.recordsource = rst

OK, I did it like you told me. Database is now compacted when loading main form. Works fine! Thanks!

Problems:

I delete those records from database like you adviced. How can I refresh my dbgrid? I try this:   myAdo.Refresh
but this doesn't work. myAdo is connected that database.

I run my application from VB editor and then close my app. Second time I try to run app, it stops to database compacting and I get an error: "You attempted to open a database that is already opened exclusively by user..."
So why VB editor won't release that handle?

Yes I know that those ADODC controls are not necessity and I very much would like to get rid of them. I started to use them a long time ago when I used ADO first time and I didn't know how to connect database by using code. If it's easy to change maybe I will do it sometimes...

>>How can I refresh my dbgrid

You need to refresh the recordset and you need to refresh the grid. If you are using multiple connections, have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q216925.

I tryed to refresh that grid too but it didn't work somehow and for some reason that link you gave me didn't work either. I don't know if it's something to do with countries... I live in Finland and that link took me to the Finnish Microsoft pages.

Anyway, thanks for your answers. You solved the problem I asked.

Again that internal server error!

the link has a dot at the end. Use this one:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q216925