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