Hi Oliver,
I think the problem with your approach might be in using the nextval
function as a parameter.
I get sequence values by opening a recordset on the sequence, like this:
Dim cnPGODBC as ADODB.Connection
Dim rsSeqVal as ADODB.Recordset
Dim sSeqValSQL as String
Dim iSeqVal as Integer 'The value returned
sSeqValSQL = "SELECT nextval(bed_id_seq);"
cnPGODBC.Open ("Your ODBC connection string")
Set rsSeqVal = cnPGODBC.Execute(sSeqValSQL)
iSeqVal = rsSeqVal.Fields(0)
Set rsSeqVal = Nothing
Set cnPGODBC = Nothing
Hope this helps,
Eric
Gosling, Oliver wrote:
> Does anybody know how to get the next value of a sequence?
>
> I’m presuming that nextval is an SQL procedure in postgres which can
> be called by passing the name of the sequence as a parameter. The code
> below shows what I’ve done in VB. This does not work however - JET
> throws up an error saying that ‘nextval’ table or query cannot be found.
>
> Dim objCmd As New ADODB.Command
>
> Dim objPrm As ADODB.Parameter
>
> objCmd.ActiveConnection = CurrentProject.Connection
>
> objCmd.CommandType = adCmdStoredProc
>
> objCmd.CommandText = "nextval"
>
> Set objPrm = objCmd.CreateParameter("@name", adBSTR, adParamInput, ,
> "bed_id_seq")
>
> objCmd.parameters.Append objPrm
>
> Set sequenceNumber = objCmd.Execute
>
> seqNum = sequenceNumber!nextval
>
> Can anbody help me with this?
>
> Regards,
>
> Oliver Gosling
>