473,698 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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:
=============== =============>I mport Form>========== ==========
Option Compare Database
Option Explicit

Private Sub cmdImp_Set1_Cli ck()

Dim db As DAO.Database

Set db = CurrentDb()

db.Execute "qdel_Set1_No_P ct_Date", dbFailOnError

db.Execute "qdel_Set1_Cont No_8Char", dbFailOnError

Call CompactDB

DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel8, _
"tbl_Set1_No_Pc t_Date", _
"X:\My Documents\FileR ef\Excel\Random No_3_sets.xls", _
True, "Set1_No_Pct_Da te"

db.Execute "qap_Set1_ContN o_8Char", dbFailOnError

Call OpenImportFrm

Set db = Nothing
End Sub

Private Sub cmdImp_Set2_Cli ck()
Dim db As DAO.Database

Set db = CurrentDb()

db.Execute "qdel_Set2_ No", dbFailOnError

db.Execute "qdel_Set2_Cont No_8Char", dbFailOnError

Call CompactDB

DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel8, _
"tbl_set2_N o", _
"X:\My Documents\FileR ef\Excel\Random No_3_sets.xls", _
True, "set2_No"

db.Execute "qap_Set2_Contr No_8Char", dbFailOnError

Call OpenImportFrm

Set db = Nothing
End Sub

Private Sub cmdImp_Set3_Cli ck()

Dim db As DAO.Database

Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset

Set db = CurrentDb()

Set rst1 = db.OpenRecordse t("tbl_Set1_Con tNo_8Char") ', dbOpenTable

Set rst2 = db.OpenRecordse t("tbl_Set2_Con tNo_8Char")

If rst1.RecordCoun t = 0 Or rst2.RecordCoun t = 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_Cont rNo_8Char_step1 ", dbFailOnError

db.Execute "qdel_Set3_Cont rNo_8Char_step2 ", dbFailOnError

Call CompactDB

DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel8, _
"tbl_Set3_N o", _
"X:\My Documents\FileR ef\Excel\Random No_3_sets.xls", _
True, "Set3_No"

db.Execute "qap_Set3_Contr No_8Char_step1" , dbFailOnError

db.Execute "qap_Set3_Contr No_8Char_step2" , dbFailOnError

Set db = Nothing

Call OpenImportFrm

End Sub
=============== =============<I mport Form<========== ==========

``````````````` ``````````````` ``````````````` ``````````````` `

=============== =============>P ublic Modules>======= ==========
Option Compare Database
Option Explicit

Public Sub CompactDB()

CommandBars("Me nu Bar"). _
Controls("Tools "). _
Controls("Datab ase utilities"). _
Controls("Compa ct and repair database..."). _
accDoDefaultAct ion

End Sub

Public Sub OpenImportFrm()
DoCmd.OpenForm "frmImport"
End Sub
=============== =============<P ublic Modules<======= ==========

--
Best Regards,
Greg Strong
--
Regards,

Greg Strong
Nov 13 '05 #1
2 2465
On Sat, 28 May 2005 22:41:15 GMT, Greg Strong <NoJunk@NoJunk4 U².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
On Sat, 28 May 2005 22:41:15 GMT, Greg Strong <NoJunk@NoJunk4 U².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 TransferSpreads heet to tables with the auto number fields.

--
Regards,

Greg Strong
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
2482
by: Roberto A. F. De Almeida | last post by:
I found that when using negative indices, the slice object passed to __getitem__ depends on the number of slices. An example to clarify: class a: def __getitem__(self, index): return index >>> b = a() >>> print b Traceback (most recent call last):
2
8031
by: Dave Bazell | last post by:
I have a multidimensional array, say 20 x 6, and I want to take a subset of the rows. I have an index array @indx=(1,3,12,17) for example, and what I want to say is @new_array = $data I know that does not work. It has something to do with using the appropriate reference to the array elements. How should I do this? And why does it work?
15
13266
by: christopher diggins | last post by:
Here is some code I wrote for Matrix multiplication for arbitrary dimensionality known at compile-time. I am curious how practical it is. For instance, is it common to know the dimensionality of matricies at compile-time? Any help would be appreciated. Hopefully this code comes in useful for someone, let me know if you find it useful, or if you have suggestions on how to improve it. // Public Domain by Christopher Diggins, 2005 ...
3
2373
by: Trevor Hughes | last post by:
Hello All I have a database (Access 2000, running on Win 2000), which suffers from bloat over a period of time. In order to solve the problem I set the option to compact on exit. This however has caused a problem. The permissions of the mdb file which are set to Everyone-Full control, are reset when the database is compacted. The end result is the the users get a message saying Access cannot locate the database. I can run it with...
6
2305
by: owengoodhew | last post by:
Hi, I am responsible for maintaining an MS Access 97 Database that reliably becomes corrupt following a compact......... About the Database: The database is made up of three linked databases, data, input and output. The files all share the same table structures. Recently the input database started growing in size and it became necessary to compact the databases more frequently. Following the compact of the
23
2328
by: Antoon Pardon | last post by:
Now slices are objects in python, I was wondering if slice notation will be usable outside subscribtion in the future. Will it ever be possible to write things like: a = 4:9 for key, value in tree.items('alfa.': 'beta.'): -- Antoon Pardon
7
2210
by: Alexandre Guimond | last post by:
Hi all, i'm trying to deepcopy a slice object but i get the following error. Does anyone know a workaround? ActivePython 2.4.3 Build 12 (ActiveState Software Inc.) based on Python 2.4.3 (#69, Apr 11 2006, 15:32:42) on win32 Type "help", "copyright", "credits" or "license" for more information. Traceback (most recent call last):
0
1121
by: Tor Erik Soenvisen | last post by:
Hi, all I would like some feedback on a multithreaded HTTP server I've written. The server serves python-scripts by dynamically loading scripts in the same directory as itself. When a request is made to one of these scripts the script is executed and its output is returned to the requester. Here is the server code, HTTPServer.py: # Basic, threaded HTTP server, serving requests via python scripts # Author: Tor Erik Soenvisen
29
2770
by: Neil | last post by:
I would like to compact on close only if the database size goes over a certain amount, rather than each time. Thus, I'd like to check the file size and then perform the compact through code as the mdb's closing. Is that possible? I suppose one option would be to set the Compact On Close option in the switchboard's On Close event, and then clear it whenever the database is opened. That would probably work. But I'd prefer a cleaner...
0
9157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9026
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8893
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8861
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7723
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3045
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2328
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2001
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.