2
Next, I have a database Access
with a table called tblDados
.
This table contains 19 initial fields.
I import a CSV file que
contains 1 million records, so far so good, saved and close the database uncompromising, The same stays with 135 MB.
The problem is when I run a query via VBA
. This consultation runs throughout the tblDados
and clears the data of a given column. It is working correctly, but when saved and close the database uncompromising, same goes for 274 MB
and, if I run the query again it increases the database to 1,6 GB
.
I did some tests enabling the option of compress and repair when closing the access, It greatly reduces the file. However, I need to execute several queries with this via VBA with the database open and after finishing the processes I can delete the entire table and compress when closing.
There is something wrong with my query that is consuming many resources of the current database and weighing in this way?
What can I do to run the query and decrease/release resources to the database?
Code VBA
of consultation:
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
Dude, a suggestion would be you create a query generates table for another access database.
– Edvaldo Lucena