sql code to find max recordcount?

[notageek7]

I'm wondering how to find with a sql statement the max count for a table, in other words what the next autonumber would be.

 

[hkaing79]

SELECT Count(*) FROM table

 

[jtseltmann]

If you run a sql statement such as this...it would return the highest value.

"SELECT MAX(yourautonumberfieldname) FROM Tablename;

 

[jtseltmann]

hkaing79,
the select count assumes the autonumber field started at 1. To find the next number that will be assigned you have to check the values in that autonum field.

 

[Tranman]

notageek,
You're treading on fairly dangerous ground, here. There is 'nothing' in Access which in any way guarantees that the next autonumber will have any certain value, that it will reflect the order in which the rows are added to the table, (or, for that matter, that it will be one greater than the last autonumber it issued). The only guarantee is that whatever autonumber is issued will be unique.

I think most people in the know would advise you not to use the value of an autonumber field as anything more than a (possibly random) unique number, fit to use as a random primary key, and not much else.

You can take this advice with a shaker of salt, but I would be cautious in my use of the values of autonumbers.

(Once bitten)
Tranman

 

[jtseltmann]

I totally agree with Tranman. Found this out also along the way! I was simply giving you the answer to your question assumming it was just an example of finding a max value.

 

[hkaing79]

Good point jtseltmann. I overlooked the autonumber part. I assumed if notageek7 was looking for the next number then he's not using the autonumber part. I didn't see the need to find the next number if you're using an autonumber.

 

[notageek7]

Thanks for everyones feedback. To clarify my problem. I've got an access db that has linked tables to oracle tables. What I'm needing to do is syncronize my sequence s_nexval with the highest key value in access. The problem occurs when users paste records into the db. After a paste I'm needing to run some code that retreives my nextval and compare it to the highest key in my access table. I originally used recordcount but that dosn't account for somebody deleting records. So, I've got to open each table and find what the primary keys fieldname is and check it's highest value. Here is a piece of the old code.


Dim tbf As DAO.TableDef
Dim db As DAO.Database
For Each tbf In db.TableDefs

tablename = tbf.Name

If rst.recordCount > newKey Then
Do While rst.recordCount > newKey
newKey = GetOracleAutoKey(tablename)
Loop
End If


Next

 

[hkaing79]

jseltmann's code should work, no? Do you have varying primary key field names? Also, am I understanding correctly? newKey is the highest primary key value in all the tables in your database? So if Table1 is 99 and Table2 is 100, newKey is 100, rather than 99 for Table1 and 100 for Table2?

 

[notageek7]

Yes jseltmann's code should work. My difficulty is that the primary keynames of my 60 some odd tables are different. So, I'm in the process of writing code that will retreive the primary key fieldname from each table.