Solved

running Autonumber reset function from Debug window

Posted on 2009-05-17
7
352 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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