Solved

running Autonumber reset function from Debug window

Posted on 2009-05-17
7
353 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

679 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