Solved

running Autonumber reset function from Debug window

Posted on 2009-05-17
7
349 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
Comment Utility
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
Comment Utility
"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
Comment Utility
Peter57r.

Yes, I am trying to reset autonumber to its previous highest value.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:dakota5
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent and precise answer.  Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

11 Experts available now in Live!

Get 1:1 Help Now