Access database getting heavy after running query via VBA

2

Next, I have a Access database with a table named tblDados .

This table contains 19 initial fields.

I import a CSV file que contains 1 million records, so far, save and close the uncompressed and repaired database , it gets 135 MB .

The problem is when I run a query via VBA . This query traverses all tblDados and clears the data of a given column. It is working properly, but when I save and close the uncompressed and repaired database, it gets with 274 MB , and if I run the query again it raises the database to 1,6 GB .

I did some testing enabling the compact and repair option when closing access , it greatly reduces the file. However, I need to run multiple queries with this via VBA with the database open and after finishing the processes I can delete the entire table and compact when closing.

Is there something wrong with my query that is consuming many resources of the current database and weighing it that way?

What can I do to run the query and lower / release resources for the database?

Code VBA of query:

Public Sub removeDadosObsoletos()

    Dim db As DAO.Database
    Set db = CurrentDb()
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("SELECT tblDados.DATA_ULTIMA_MOVIMENTACAO FROM tblDados;")
    Dim lQtd As Long    //variável que recebe a quantidade total de registros
    //conta o número de registros
    rs.MoveLast
    lQtd = rs.RecordCount

    //variável que irá contar os registros
    Dim lContador As Long
    lContador = 0
    //loop que irá percorrer a tabela
    rs.MoveFirst    //vai para o primeiro registro

    Do While Not rs.EOF

        rs!DATA_ULTIMA_MOVIMENTACAO = ""

        lContador = lContador + 1 //acrescenta 1 na quantidade

        rs.MoveNext //proximo registro

    Loop

    rs.Close
    Set rs = Nothing

    db.Close
    Set db = Nothing

    MsgBox "Registros obsoletos removidos com sucesso!", vbInformation, "Otimização dos Dados..."

    Exit Sub
End Sub
    
asked by anonymous 25.05.2018 / 19:40

0 answers