Creating a 'Sequence' in Access ...

[familyman1]

I am working on mimicking a Linux databse in Access. One of the objects is called a 'Sequence'. A PostgreSQL book that I have states the command as "CREATE SEQUENCE", but I have no idea how to create a 'Sequence' in Access. I already have a database created with some tables, but also need to create a 'Sequence' in this same database. Please help.

 

[KenReay]

Do you mean next sequential number?

If yes an autonumber column may be the answer you are looking for, but note with autonumber it is possible to get gaps in the sequence

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[familyman1]

I am working with code that is already written to access tables and sequences on a Linux OS, the code consisting of PostgreSQL commands. Here is the exact command used to access this sequence I am talking about:
"SELECT nextval('seq_name')

What I need to do is mimick this sequence in my access database so that this code will run the same just as if it were accessing the Linux box. I have all the tables and such working fine, but don't know how to replicate this sequence functionality in Access, and I can't modify the code. The code must remain the same.

Do you create a table then a sequence within a table? How will the code know what table this sequence is in without the table name as part of the command? Etc... I have been looking on the Internet some but haven't found the solution yet. Basically just need someone to tell me how to do it.

 

[jsteph]

You need to build your own table, and read/update that. This is easy, it's commonly done by those of us (including myself) who abhor the Access implementation of Autonumber.

to maintain the code, if this is in Access (?) you could make NextVal() a function, and do "Select nextval() From tblSomedummytable"
...where somedummytable is just that--a single record-dummy table. NextVal() is a function that reads from you're handbuilt sequence table.

The way you deal with 'seq_name' can vary, but you could make tblSequence a single-record table with a field for each 'seq_name', etc. or a separate record with a text key using the sequence name's value.

Then the nextval function locks/reads the proper record or field, then adds 1, updates, releasees.
--Jim

 

[familyman1]

Thanks! I will have to try this at home tonight as I do not have Microsoft Access where I am at right now.

The code is written in LabVIEW. This LabVIEW code is what is accessing the Linux tables and sequences. What I am trying to do is simulate the Linux setup in Access so that I can do testing at home. And maybe eventually having this Access database replace the Linux setup.

Tonight, I will create a table just for the sequences and make a field for each sequence in this table. I have worked with Access in various ways and only know bits and pieces. When you say write a function, would this be a macro? Function in a Module? Other? How would I create this function within this Access database so that when the "SELECT nextval('seq_name') command is executed against this database, it finds it?

 

[jsteph]

This would be a function in a module. Something like:

Public Function NextVal(byval strSeqName as string) as long
dim sq as string,db as database, rs as dao.recordset
'Assuming fields named with 'SequenceName' as value field
sq = "SELECT " & strSeqName & " FROM tblSequence WHERE Recid = 1"
'(Recid should be an Integer field, make it PrimaryKey, and you put a 1 as it's value, put 1 in Validation Rule, so nobody can add records--this is a single-record table)

'(For simplicity we wont' lock the table--but if this were live production--you would open the recset with locking options)
Set rs = db.openrecordset(sq,dbopendynaset)
rs.edit
rs(strseqname) = rs(strseqname) + 1
NextVal = rs(strseqname)
rs.update

End Function

Then have a table called, say, tblDummy--with just one record, you can do as above with Recid, integer, validation rule = 1, etc, etc. This is like the Oracle Dual table.

Then In the query, you do:
Select nextval("SomeSequenceName") as Thesequence from tblDummy

Then if the above is a source for a recordset or a sub-select, it will return only one record, which will be the value of the next sequence for that 'sequence'.
--Jim