Hi. I have a linked dbf to my Access 2003 database. I have some code
which
I want to clear the dbf's records (Delete all records) and then rewrite
them
using ADO's Execute method using the currentproject connection object with
some INSERT query.
Unfortunately, I've noticed that my dbf bloats like crazy, up to 35MB and
lists an excessive number of records (141,000+). I manually deleted the
records in another environment and ran the sub once, and it dropped to
132Kb,
and 542 records (which sounds more reasonable to me). I'm thinking this
could be for one of two reasons:
1) The Delete has not fully executed (Deleted all records) before starting
to append. If this is the case, how can I get around this? I'm not
really
familiar with transactions, so if someone can help me do this with the
code
I've posted below?
2) The syntax for the delete string is not correct or won't work for a
linked dbf, or some provider related issue. If this is the case, or I'm
going about it incorrectly, I'd really appreciate some suggestions how I
can
proceed.
The code for the Sub is posted below. Thanks!
AK
------------------------
Public Sub RefillTable(strTableName As String, strAppend As String)
Dim cnn As ADODB.Connection
Dim strSQLDelete As String
On Error GoTo Err_RefillTables
Set cnn = CurrentProject.Connection
'Clear Table
strSQLDelete = "DELETE * FROM [" & strTableName & "];"
cnn.Execute strSQLDelete
'Append records
cnn.Execute strAppend
Exit_RefillTables:
Exit Sub
Err_RefillTables:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Resume Exit_RefillTables
End Sub
|
