By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,895 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,008 IT Pros & Developers. It's quick & easy.

Code completion / compact DB from code / slice data ???

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sat, 28 May 2005 22:41:15 GMT, Greg Strong <NoJunk@NoJunk4UČ.com> wrote:
The reason I say this
is the auto number fields are NOT being reset to zero.


I should have stated they are not being reset because technically the auto
number fields by default start with 1.

--
Regards,

Greg Strong
Nov 13 '05 #2

P: n/a
On Sat, 28 May 2005 22:41:15 GMT, Greg Strong <NoJunk@NoJunk4UČ.com> wrote:
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.


Well I've changed the code. I now include the compact db code per
http://www.mvps.org/access/general/gen0041.htm at the end of a procedure and
all the auto number fields are reset.

There must be an issue when the compact database code is followed by importing
of data via TransferSpreadsheet to tables with the auto number fields.

--
Regards,

Greg Strong
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.