Hello All,
I've written code in a test database with test data. Everything seems
to be working except compact database in VB code per
http://www.mvps.org/access/general/gen0041.htm. The reason I say this
is the auto number fields are NOT being reset to zero. I delete the
data from tables with action delete queries, then call the compact DB
code which is followed by importing data to tables and subsequent
append queries to other tables. I have received intermittent error
messages on the compact DB code a small percentage of the test runs.
However I can tell the database is being compacted because I've
manually imported the data followed by running the code. Each time the
DB is reduced to 50% of the original size after doubling up the data
with the manual import. This leads me to believe that code is not
finishing before the next line is being run in VB. The reason I say
this the auto number field is not reset even though the import & append
queries are run after the compact DB.
The whole purpose of imports and append queries is to insure that the
critical text field is a certain length when comparing 3 different
sources of data which sets up the 'flag' field. The critical text
field hold numeric information which may have leading zeros. I set up
length of the critical text field with an append query with
'ConNo_x:format(txtConNo_x, "00000000")' for an expression in the
append query. The relationship of the 3 sources can best be summarized
below.
Source critical field characteristics flag
-------- -------------- ----------------------- ----
source 1 txtConNo1 none = to source 2 "A"
source 2 txtConNo2 none = to source 1 "A"
source 3 txtConNo3 some = to source 1 or 2 "A"
source 3 txtConNo3 none = to source 1 or 2 "B"
I accomplish this by setting up a form with 3 command buttons to
import the 3 sources of data. The import of the 3rd source checks the
number of records in source 1 and 2, and exits if either have no
records which insures setting up flag "B". I wanted to compact the
database because of the amount of data that is imported from Excel
files, and the compact keeps the DB at the appropriate size. The
problem with the compact DB is that the form is closed. The form won't
open when called from VB code after the compact even though other
append queries appear to be working after the compact DB.
My questions are as follows:
How can I insure code has completed execution before the next line is
executed?
Any ideals why the auto number fields are not being reset to 0 after
the compact?
How can I get the form to open after the compact of the database?
TIA!
I've included the code below:
============================>Import Form>====================
Option Compare Database
Option Explicit
Private Sub cmdImp_Set1_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "qdel_Set1_No_Pct_Date", dbFailOnError
db.Execute "qdel_Set1_ContNo_8Char", dbFailOnError
Call CompactDB
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"tbl_Set1_No_Pct_Date", _
"X:\My Documents\FileRef\Excel\RandomNo_3_sets.xls", _
True, "Set1_No_Pct_Date"
db.Execute "qap_Set1_ContNo_8Char", dbFailOnError
Call OpenImportFrm
Set db = Nothing
End Sub
Private Sub cmdImp_Set2_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "qdel_Set2_No", dbFailOnError
db.Execute "qdel_Set2_ContNo_8Char", dbFailOnError
Call CompactDB
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"tbl_set2_No", _
"X:\My Documents\FileRef\Excel\RandomNo_3_sets.xls", _
True, "set2_No"
db.Execute "qap_Set2_ContrNo_8Char", dbFailOnError
Call OpenImportFrm
Set db = Nothing
End Sub
Private Sub cmdImp_Set3_Click()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("tbl_Set1_ContNo_8Char") ', dbOpenTable
Set rst2 = db.OpenRecordset("tbl_Set2_ContNo_8Char")
If rst1.RecordCount = 0 Or rst2.RecordCount = 0 Then
MsgBox ("You must import set 1 & 2 before set 3")
Exit Sub
End If
db.Execute "qdel_Set3_No", dbFailOnError
db.Execute "qdel_Set3_ContrNo_8Char_step1", dbFailOnError
db.Execute "qdel_Set3_ContrNo_8Char_step2", dbFailOnError
Call CompactDB
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"tbl_Set3_No", _
"X:\My Documents\FileRef\Excel\RandomNo_3_sets.xls", _
True, "Set3_No"
db.Execute "qap_Set3_ContrNo_8Char_step1", dbFailOnError
db.Execute "qap_Set3_ContrNo_8Char_step2", dbFailOnError
Set db = Nothing
Call OpenImportFrm
End Sub
============================<Import Form<====================
`````````````````````````````````````````````````` ```````````
============================>Public Modules>=================
Option Compare Database
Option Explicit
Public Sub CompactDB()
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Sub
Public Sub OpenImportFrm()
DoCmd.OpenForm "frmImport"
End Sub
============================<Public Modules<=================
--
Best Regards,
Greg Strong
--
Regards,
Greg Strong
