Autonumber start at 500 (1 Viewer)

[Pat Hartman]

Did you really need to add a new variable? Wouldn't the following have worked? Perhaps you could test this since you already have the db setup.

Function ChangeSeed(strTbl As String, strCol As String, LngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'LngSeed = Long Integer value you want to use for the next AutoNumber

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
'Set connection and catalog to current database
Set cnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\program files\Douglas Penman\show database be.mdb"
cat.ActiveConnection = Cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = LngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("Seed") = LngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

 

[Jack Cowley]

Pat -

I tried various versions of the code that Dawn posted but the only way I could get it to work was by using the two variables. I tried your code but it balks at the 'Set cnn = ...' line of code with a Type Mismatch error.

We both sincerely appreciate your assitance and willingness to give of your time and expertise. Your code may in fact work, but I can't get it to do what it is supposed to do....

Thank you very much for your suggestion.

Jack

PS. I actually found the solution of using the two variables in Access help as I had tried variations on the basic code you suggested above without success...

 

[Pat Hartman]

Thanks for trying my variation. I guess, I'll need to do some ADO reading.

 

[Jack Cowley]

You are more than welcome. Just because I failed does not mean that your code won't work. It just means that I could not get it to work....

Thank you for all your efforts on Dawn's and my behalf.

Jack

 

[servoss]

A couple follow-on questions...

I, too, am very interested in being able to resent the autonumber seed to a specific value.

I have coded the ChangeSeed funtion as described by DBL in the post dated 04-05-2003 07:21 PM, but it isn't working quite right and I have a couple questions:

1) I have checked a reference library that seems to be the right one, but please verify. I have checked "Microsoft ADO Ext. 2.5 for DDL and Security" - this seemed to be the most logical selection, but I want to be sure, of course.

2) I notice in the code, there is a specific path referenced (the value of strCnn). I assume this is the path to the database within which the auotnumber field to be changed resides. Therefore, I changed this to point to my database, of course. This, too, seems to be ok, however I get an error when I execute the function (see attached error messagebox) that seems to indicate that I have the database open and therefore cannot change it. Am I interpreting this correctly and if so, how do I get around it? The function is called from one of the form's VBA procedures.

3) This probably ties into the last question, but could you explain what a split database is and how/why it is useful?

As always, I appreciate you assistance.

Tom

 

[Jack Cowley]

1) My version reads ""Microsoft ADO Ext. 2.7 for DDL and Security" but yours should still work.

2) Here is the code to change an autonumber in the current database with tables attached:

Function ChangeSeed1(strTbl As String, strCol As String, LngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'LngSeed = Long Integer value you want to use for the next AutoNumber

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = LngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("Seed") = LngSeed Then
ChangeSeed1 = True
Else
ChangeSeed1 = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

3) From Access Help "You can put only the tables on a network server, and keep other database objects on users' computers. In this case, the Access database's performance is faster because only data is sent across the network. Also, users can customize their forms, reports, and other objects for their individual needs and preferences without affecting other users.

You can separate the tables from the other database objects by using the Database Splitter Wizard."

For more information search the MS KB on the subject of spitting a database.

hth,
Jack

 

[mtairhead]

It's always the stupid one...

Sorry for being an airhead, but I cannot get Pat's code to work. It's probably because I have no formal training....It's very much because of that.

I attached a sample database, with a single table and the module in question. Can anyone help a babbling buffoon?

 

[Pat Hartman]

You made numerous changes to the code. NONE were required. The code I posted was written to be used as a function. You pass in the name of the table, the name of the autonumber column, and the seed value and the function changes the seed for the specified table!

 

[ya5irha55an]

Hi All,

Sorry for this question, I know its simple but im not very experience with Access: this question is exectly what I am looking for, I also have an autonumebr which I would like to begin from a number other than 0, but I am not sure how to use this code, where do I put it?

 

[Banana]

You put it in a Module; you can make a new module by using database window or selecting New Class Module from menu in VBA editor. You will need to pass the table and fieldname of the autonumbering column with number you want to start with.

HTH.

 

[ccg_0004]

Access 2007

Does anyone know if this code works with Access 2007? When I click RUN in the VB Editor it asks me selecting a macro. Why is this?

CG

 

[boblarson]

You would have to add a reference (Tools > References) in the VBA Window to Microsoft ADO because 2007 doesn't have that selected.

 

[raskew]

Hi -

As this thread moves into three pages, you might want to take a look at Allen Browne's solution at

http://www.allenbrowne.com/ser-26.html

Bob

 

[datAdrenaline]

The easiest way, IMO, to reset the seed value and increment is through a SQL statement ...

Public Function ChangeSeed(lngStartAt As Long, intIncrementBy As Integer) As Byte
    
    Dim strSQL As String
    
    strSQL = "ALTER TABLE yourTablename ALTER COLUMN yourAutonumberColumn" & _
              " AUTOINCREMENT(" & lngStartAt & "," & intIncrementBy & ")"
    
    CurrentDb.Execute strSQL, dbFailOnError
    
    '''''''''''''''''''''''''''''''''''''''''
    'You can also use ADO/JET OLEDB Provider with a connection object
    '
    'CurrentProject.Connection.Execute strSQL, ,adCmdText
    
End Function

 

[datAdrenaline]

If you choose to use the code posted by Pat .... As Bob indicated, you will need the ADO (Microsoft ActiveX Data Objects xx.xx) library reference. In addition you will need the ADOX (Microsoft ADO Ext. xx.xx for DDL and Security) library as well.

 

[ccg_0004]

If you choose to use the code posted by Pat .... As Bob indicated, you will need the ADO (Microsoft ActiveX Data Objects xx.xx) library reference. In addition you will need the ADOX (Microsoft ADO Ext. xx.xx for DDL and Security) library as well.

I appreciate all the help. I had enabled the two references before I posted so I diasbled them and tried again. No such luck. To be sure I checked the complete list of ref's- that thing is damn long.

Microsoft ADO Ext. 2.8 For DDL and Security
Microsof ActiveX Data Objects 2.1 Library

are the two ref's. They have checks next to them which I assume is correct. Maybe I will try to move them up in priority.

 

[Banana]

ccg,

I'd strongly advise you to uncheck all references except for what you had originally. Not only it will make your application run slower, you are running much higher risk of crashing the application due to conflicts or whatever unknown side effects this may have.

You already had the needed references checked, and it's something else, not the references that is the problem.

I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro.

 

[ccg_0004]

I'd strongly advise you to uncheck all references except for what you had originally. Not only it will make your application run slower, you are running much higher risk of crashing the application due to conflicts or whatever unknown side effects this may have.

I was not clear. I searched through the list of unchecked references which was very long. There are only like 5 refs checked, the two in this thread, two that are in use, and OLE automation.

I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro.

Tried but DOH! Still asking for macro. All codes provided ask for macro.

StepbyStep:
I copy the code. I open VB. INSERT->New Module. Paste.

above the paste i type "Call SetAutoNumber(InvoiceID, 550)" and then click Run. Suddenly I'm asked for a macro. Killer.

 

[Banana]

Is the Module you created a standard module or a class module? You can run code from standard module but not from a class module.

 

[datAdrenaline]

Hello ccg...

>> I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro. <<

Clicking the Run will only invoke the procedure IF your procedure does not have any arguments. Your procedure has arguments, that is why Access if prompting you for a Macro ... You are asking VBA to run, but it can not run the procedure you're cursor is in because it does not know the values for those arguments, so it then asks for a Macro or Function procedure that does not have arguments to be passed.

If you want to invoke a procedure that has arguments, you can do that from the Immediate window of the VBA editor by typing the command, then hitting the enter key, NOT the "Run" button (the Run looks at your cursor position) ...

If you wish to see the result of a Function procedure that enter the command like this:

? SomeFunctionName(Arg1, Arg2, etc) {hit the enter key}
<the result will show here>

If you wish to invoke the execution of a Sub procedure or a Function that you have no need for the value returned (ie: a Function that is used like a Sub), then enter the command like this ..

SomeProcedureName Arg1, Arg2, Arg3, etc ... {hit the enter key}

....

For Example:

A "Function" (you want to know the result of a function or value of a property) type command from the immediate window ...

? 2 + 2
4

? CurrentDb.TableDefs("SomeLinkedTable").Connect
;Database=C:\SomePath\SomeFile.mdb
---------

A "procedural" (execute a the series of steps that make up the procedure) type call ...

DoCmd.DeleteObject acForm, "MyFormName" {hit the enter key}
<cursor will be flashing on the line below the command when the procedure>

---------
Aside from all this ... I would encourage you to use the code I posted as it is not AS library dependant (the constants are the only library dependant piece .. and you could use the numbers instead if you wish). Also, libraries become important if you declare object variables too! ... If you want to use the ADOX code, and you are getting errors, then please post the code you are using and the exact line the err is raised on.

Also ... your comment:
>> Maybe I will try to move them up in priority <<

If you have explicitly declared (ie: Dim rs As ADO.Recordset) your object variables, priority will not matter. I encourage explicit declaration of object variables.

Hope this helps!!