running Autonumber reset function from Debug window
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.
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.
"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.
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.
ASKER
Peter57r.
Yes, I am trying to reset autonumber to its previous highest value.
Yes, I am trying to reset autonumber to its previous highest value.
ASKER
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The immediate/debug window gets the following-
Dim tempvar as Boolean
tempvar = ChangeSeed("transfer_log",
The process runs when you hit return after the assignment/function call statement
Dim tempvar as Boolean
tempvar = ChangeSeed("transfer_log",
The process runs when you hit return after the assignment/function call statement
ASKER
Excellent and precise answer. Thank you.
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