CODEWITHSUNDEEP
vbams-access
Linggg
Linggg

Reputation: 27

Microsoft Access VBA, compact n repair, database corrupted

I am working on a table with 1.5 million records and i need to make a lot of modification to the data.

As i need to automate the whole process, i am trying to put all the steps under one command button by calling all the sub at one time.

However,due to the size of the data, the size of the access database will hit 2GB(the max limit) after running one subroutine or cant even finish running one subroutine.

I have attached one sub which often lead to corruption of the database as the file size will hit 2GB halfway, and then stop running.

I really need help on this, will appreciate anyone's help!

Sub cogsinout()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Final")

rs.MoveFirst
Do Until rs.EOF
  If (rs!El = "BA") Or (rs!El= "BE") Or (rs!El = "FE") Or (rs!El = "LA") Or (rs!El = "LB") Or (rs!El Then
     rs.Edit
     rs!cogs = "COGS IN"
     rs.Update
    Else

             rs.Edit
     rs!cogs = "COGS OUT"
     rs.Update

   End If
   rs.MoveNext
   Loop

   End Sub

Upvotes: 0

Views: 156

Answers (1)

xShen
xShen

Reputation: 572

try updating the table with this function, It seems that in this method the database is less bloating.

Sub cogsinoutNew()

Dim strSQL As String
Dim db As DAO.Database: Set db = CurrentDb

strSQL = "UPDATE Final SET cogs = 'COGS OUT' WHERE El Not In ('BA','BE','FE','LA','LB');"
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE Final SET cogs = 'COGS IN' WHERE El In ('BA','BE','FE','LA','LB');"
db.Execute strSQL, dbFailOnError

Set db = Nothing

End Sub

Upvotes: 2

Related Questions