to save MDB as Access 200 format with ado?

[torrasar]

I have a problem in my VBA project .
I use DB.MDB as database file using ADO.It is 300 kByte. Anyway, whenI add/delete a column, The file becomes 1 Mbyte. And I can solve this problem if I open the file manually and save it as Access2000 Database.

I sit possibel to open and save the file as Access200 database with VBA codes?

[JONvdHeyden]

Hi

I presume you want to do this all through Excel?

Tell me, does compact and repair also solve the problem? If so we could construct a code to use the CompactRepair method...

[torrasar]

I am using MS Visual basic 6.0 .. And I use DB.mdb file as database, using ADO.

What is " compact and repair" ? I have no idea.

I open the MDB file, and select save as.. and save the flile as Access 2000 file.. It solves the problem. but when I add or delete any column using ADO, it becomes 1 MB again. So I need a VBA code to save the file like this. Whenever I add a column or table, I will use that code to reduce the file size.

Main problem is , I take copies of the file as archieve(archieve of last 50 days) , so 750kByte difference causes 0.75*50=37.5 MB area loss.

[JONvdHeyden]

Instead of saving as Access 200 format next time try going into the DB and on the menu bar go Tools > Database Utilities > Compact and Repair Database. Then check the file size again.

If it works then look at the CompactRepair method in the MS Access VBA help file. I would store the CompactRepair function in the DB and then use ADO to execute it...

[torrasar]

For a better explaination, commands I use to connect the DB.mdb:

Public Sub connect()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=DB.mdb"
conn.Open
End Sub

Private Sub CLOSE ()
On Error Resume Next
Dim I As Integer
FileCopy "DB.mdb", "Archieve-" & Date & ".MDB"
For I = 50 To 365
Kill "Archieve-" & Date - I & ".MDB"
Next
End
End Sub

[torrasar]

tried it now..
Yes, compact repair works, but when ı add a new column using VBA codes, size again becomes 1Mbyte.
So the archieve files are also 1 MB

[JONvdHeyden]

Why do you need to create an archive file? Are you adding / deleting fields using ADO sql? Or how are you doing it? Can you show me that code?

I would use that same code to add / remove the field and then immediately call the CompactRepair function (within the same code)...

[torrasar]

connect
SORGU = "ALTER TABLE mytablename ADD COLUMN " & TextBox1 & " cURRENCY "

conn.Execute (SORGU)
conn.close

this adds the column. Column name is typed in textbox1

SORGU is the SQL string..

[torrasar]

ALTER TABLE mytablename DROP " & ListBox1.Column(0) & "

and this query deletes the column

[torrasar]

By the way, my tablename is not hidden.. but I couldnt type here because only uses Turkish characters..possibly u cant read

[torrasar]

I tried compactrepair manually from access menu.. Yes the file becomes smaller but at this position ADO cannot get data from the file. ıt seems as Unknown file format. It seems it will be better to save in access200 format. But how with VBA codes?