Using Parameters in Queries

[dilettante]

One problem is that these "DBMSs From Planet X" (including SQL Server which is a re-branded and then heavily modified Sybase) are foreign to Windows.

You have to keep in mind that Windows is far more of a Visual Basic operating system than people are willing to give it credit for. That's VB as in classic VB, i.e. VB6 - not The Great Pretender a.k.a. VFred.

VB (VB6), ADO, Windows in general have an impedance mismatch with such a foreign DBMS. There are lots of hacks and workarounds to make them usable, but again these are not fully compatible with Windows or VB6.


Foreign date formats can be particularly problematic. It doesn't help that a lot of the Morts who use VB can barely handle the native VB/OLE Date type without becoming completely tangled up in their own underwear. They usually go very far wrong trying to stuff Date values into String variables and getting confused when they rely on implicit type coercion that tries to undo the mess they've made.

But foreign date/time values might be anything. For example the "SQL Server Clueless" often end up choosing a data type like the SQL Server "Timestamp" type when they create a table column. For most of the current SQL Server ODBC Drivers and OLEDB Providers this type gets mapped to ADO's adDBTimeStamp "crutch" data type which represents such values as the Variant subtype Decimal. This is such an old issue that there are plenty of MS KB articles dating back to the turn of the Century on coping with it.

These can be problematic, and so it is possible this is where you have gone wrong.


Consider this example, which makes use of a persisted Recordset to save the need to install SQL Server or create and populate a database instance just to show some of the gyrations involved:

Option Explicit

'Based on:
'
'   HOWTO: Get Fractions of a Second from ADO adDBTimeStamp Field (Q193869).

Private Sub Form_Load()
    Dim ConversionFactor As Variant 'Pseudo-Const, We don't have Decimal Consts or literals.
    Dim RS As ADODB.Recordset
    Dim Dec As Variant
    Dim FullFidelity As String

    ConversionFactor = CDec(86400000) 'Milliseconds per day.

    Set RS = New ADODB.Recordset
    With RS
        .CursorLocation = adUseClient
        .Open "persisted.xml", , adOpenStatic, adLockReadOnly
        Dec = CDec(.Fields("Timestamp").Value)
        FullFidelity = Format$(.Fields("Timestamp").Value, "YYYY-MM-DD HH:NN:SS") _
                     & "." _
                     & Format$((Abs(Dec) - Fix(Dec)) * ConversionFactor Mod 1000, "000")

        'Margins:
        ScaleLeft = -90
        CurrentX = 0
        ScaleTop = -90
        CurrentY = 0
        Font.Bold = True
        ForeColor = &HFF8000
        Print "As a "; TypeName(.Fields("Timestamp").Value); Tab(22); _
              "As a "; TypeName(Dec); Tab(40); _
              "With Full Fidelity ";
        ForeColor = &HF0&
        Print "(ms)"
        
        Font.Bold = False
        ForeColor = vbBlack
        Print Format$(.Fields("Timestamp").Value, "YYYY-MM-DD HH:NN:SS"); Tab(22); _
              CStr(Dec); Tab(40); _
              Left$(FullFidelity, Len(FullFidelity) - 4);
        ForeColor = &HF0&
        Print Right$(FullFidelity, 4)
        .Close
    End With
End Sub

Since we haven't seen your database schema we can't say whether this is a part of your problem.