Improve company productivity with a Business Account.Sign Up

x
?
Solved

running Autonumber reset function from Debug window

Posted on 2009-05-17
7
Medium Priority
?
365 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

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 2000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

595 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