Solved

running Autonumber reset function from Debug window

Posted on 2009-05-17
7
350 Views
Last Modified: 2013-11-27
According to Microsoft, the Autonumber field is no longer reset after you compact an Access database. (article ID 287756).  This is a bug.  In this article, Microsoft provides a function that will reset Autonumber.  They state that you can add the function to a module and run it in the Debug window.

I do sql queries in Access, but no Visual Basic programming.  I need someone to explicitly state how (in Access 2007) you copy the function that Microsoft provides in the article ID 287756, add it to a module, and run it in Debug.  Where do you copy and paste to; how do you get the debug window to appear.
0
Comment
Question by:dakota5
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:coleventures
ID: 24408944
1) Where do you copy and paste to?

2) How do you get the debug window to appear?

1: You paste to a module.  You can create a module (which holds VBA code) by selecting CREATE from the ribbon menu, then choose MODULE (you'll find it at the right of the ribbon).  You may see MACRO, MODULE or CLASS MODULE.  Choose Module.  It is in this upper window that you paste the code.  You will notice an "IMMEDIATE" window at the lower left.  This is where you'll execute the immediate command ('in the debug window').

2:  You can bring up the immediate/debug window by CTRL-G.

There is one step you didn't mention.
Microsoft mentions:
"Note For this code to run correctly, you must reference both the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor. Make sure that the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries check boxes are selected."

Work out #s 1 and 2 first.

Robert
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24409516
"According to Microsoft, the Autonumber field is no longer reset after you compact an Access database"

NO!...the autonumber IS reset (to 1) after a compact.
The article is saying that it is is no longer set to its previous highest value.
0
 

Author Comment

by:dakota5
ID: 24411411
Peter57r.

Yes, I am trying to reset autonumber to its previous highest value.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:dakota5
ID: 24413806
ColeVentures:
Getting there.
1. Referenced both libraries
2. created a new module, called Reset_autonum
pasted the code into the new module
opened  the immediate window
typed in
ChangeSeed(transfer_log, INDX, 69511)        these are "correct names and values for table, field and seed number

Hit return-- "error expecting:=|
tried call ChangeSeed(transfer_log, INDX, 69511)    didn't work

What is the correct syntax to call the new function
(If I hit the run button, it asks me for a macro name-- but I should not be going through the run button).
0
 
LVL 3

Accepted Solution

by:
coleventures earned 500 total points
ID: 24416516
Because ChangeSeed is a FUNCTION you must use it in an assignment statement.

Declare a temporary boolean variable:
    Dim tempvar as Boolean

Then use this assignment/function call statement
    tempvar = ChangeSeed(transfer_log, INDX, 69511)



Robert
0
 

Author Comment

by:dakota5
ID: 24416826
The immediate/debug window gets the following-


Dim tempvar as Boolean
tempvar = ChangeSeed("transfer_log", "INDX", 69511)

The process runs when you hit return after the assignment/function call statement

0
 

Author Closing Comment

by:dakota5
ID: 31582475
Excellent and precise answer.  Thank you.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now