Option Compare Database

'The following options must be set in the ODBC driver:
'
'Row Versioning       (tells the driver to use only the primary key in record searches)
'True as -1
'(unchecked) bools as char
'
'Additionally, the database must have a set of operators loaded
'to permit certain type comparisons required by MS Access, like
'integer to boolean compares.
'
'Note that Access cannot correctly determine the value of a
'generated primary key. It is necessary to explicitly get the
'next value of the target sequence with a passthrough query
'to get nextval() on the sequence.
'
'-------------------------
' WARNING WARNING WARNING
'-------------------------
' Access 2007 appears to have an quirk bug in  passthrough query support.
' If the ReturnsRecords property is set, a query appears to be fired twice.
' This is ugly if we're invoking a stored procedure that's intended to have
' side effects and return a value. Consider `nextval(seqname)' for example.
' To work around this, no stored procedure with side effects may be called
' with ReturnsRecords = true ; instead, it must make its result available
' via the side effects.
'
Public Function DSN() As String
    DSN = "ODBC;DRIVER={PostgreSQL Unicode};DATABASE=dbname;SERVER=server.name.example.com;PORT=5432;CA=r;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;CX=1b890ab9;A1=7.4-1"
End Function

Public Function nextval(seqName As String) As Integer
    'Call the nextval(text) function in PostgreSQL with
    'the passed sequence name, and return the resulting value
    'from the sequence. We need this because Access doesn't use,
    'or the ODBC driver doesn't provide, any way to get the ID(s)
    'generated by an INSERT into PostgreSQL using sequence defaults
    'for a primary key.
    Dim MyDB As Database, MyQ As QueryDef, MyRS As Recordset
    
    On Error GoTo NoOp
    CurrentDb.QueryDefs.Delete "nextval"

NoOp:
    On Error GoTo Err_Execute
    
    Set MyDB = CurrentDb()
    Set MyQ = MyDB.CreateQueryDef("nextval")
    MyQ.Connect = DSN
    
    ' Generate the new key, discarding the result. We must do
    ' this to ensure that Access doesn't call nextval(seqname)
    ' twice, as it will if ReturnsRecords is set.
    MyQ.SQL = "SELECT nextval('" & seqName & "')"
    MyQ.ReturnsRecords = False
    MyQ.Execute
    
    ' Now that there's a value in the context of the current
    ' transaction, retrieve it. Since this procedure has no
    ' side effects, it'll do no harm when Access calls it twice.
    MyQ.SQL = "SELECT currval('" & seqName & "') AS new_id"
    MyQ.ReturnsRecords = True
    Set MyRS = MyQ.OpenRecordset()
    MyRS.MoveFirst
    nextval = MyRS!new_id
    
    CurrentDb.QueryDefs.Delete "nextval"
    MyQ.Close
    MyRS.Close
    MyDB.Close
    Exit Function
    
Err_Execute:
    CurrentDb.QueryDefs.Delete "nextval"
    ' Error return
    nextval = -1
    
End Function


Public Function SplitBooking(bookingID As Integer, FromDate As String) As Integer
    'Invoke the split_booking() stored procedure in PostgreSQL
    Dim MyDB As Database, MyQ As QueryDef, MyRS As Recordset
    
    On Error GoTo NoOp
    CurrentDb.QueryDefs.Delete "splitTemp"

NoOp:
    On Error GoTo Err_Execute
    
    Set MyDB = CurrentDb()
    Set MyQ = MyDB.CreateQueryDef("splitTemp")
    MyQ.Connect = DSN
    
    ' First, split the booking, and discard the return value. Thanks to Access's
    ' quirks, if we set ReturnsRecords=True to get the return value we will call
    ' the procedure twice. Instead, we'll rely on currval(seqname) being set by
    ' the INSERT issued by the procedure.
    MyQ.SQL = "SELECT split_booking(" & bookingID & ", " & FromDate & ")"
    MyQ.ReturnsRecords = False
    MyQ.Execute
    
    ' OK, the record has been inserted. Now obtain the id using currval(...)
    MyQ.SQL = "SELECT currval('booking_booking_id_seq') AS new_id"
    MyQ.ReturnsRecords = True
    Set MyRS = MyQ.OpenRecordset()
    MyRS.MoveFirst
    SplitBooking = MyRS!new_id
    
    CurrentDb.QueryDefs.Delete "splitTemp"
    MyQ.Close
    MyRS.Close
    MyDB.Close
    Exit Function
    
Err_Execute:
    CurrentDb.QueryDefs.Delete "splitTemp"
    ' Error return
    SplitBooking = -1
End Function

Public Function CopyBooking(bookingID As Integer) As Integer
    CopyBooking = SplitBooking(bookingID, "NULL")
End Function