Additional functions

Public Function DumbSqlFormat(ValToFormat As Variant) As String

Format a variant for SQL based only on its type

Public Function ExecutePassthroughQuery(SQLString As String, Optional ConnectString As String = "") As String

Execute the passed SQL as a passthrough query to the back end specified by the connection string. A passthrough query executes the SQL directly against the server without JET involvement.

Public Function GetLastIdentity() As Long

Returns the last identity value created in this database (uses @@IDENTITY)

Public Function IsErrorODBCLargeFieldBug(Optional PrintMessage As Boolean) As Boolean

Checks for known bug with linked SQL Server tables involving (a) using the 'SQL Server' driver in the ODBC DSN, and (b) the presence of a varchar(MAX), nvarchar(MAX) or varbinary(MAX) column in the table. Optionally prints a message describing the condition and solutions.


Public Function IsValidSqlSmallDate(v As Variant) As Boolean

Is the date valid and does it fit into the range of the MSSQL SmallDateTime type (1 Jan 1900 to 6 Jun 2079) ? Access allows dates from the year 100 to 9999.
Attempting to insert a valid date outside this range into a back end SmallDateTime column will throw an error.


Public Function MakeADOConnectionString(ConnectionString As String) As String

Take a DSN-less ODBC connection string and convert it to an ADO connection string

Public Sub ODBCErrorDisplay(sCallerProc As String, sql As String)

Displays the errors in DBEngine.Errors (commonly used to retrieve a specific error from a connection to an MSSQL database after the 'ODBC Call Failed' error.


Public Function ODBCErrorText() As String

Like ODBCErrorDisplay but returns the text of the errors concatenated.


Public Function ReturnKeyAndValColumnAsDictionary(sqlStr As String, Optional DebugOn As Boolean = False, Optional SuppressError As Boolean = False) As xf_Dictionary

1 Chai 2 Chang 3 Aniseed Syrup 4 Chef Anton 's Cajun Seasoning 5 Chef Anton 's Gumbo Mix

Public Function ReturnSingleColumnAsDelimList(sqlStr As String, Optional Delimiter As String = ",", Optional QuoteValues As xf_QuoteTypeEnum = None, Optional DebugOn As Boolean = False, Optional SuppressError As Boolean = False) As String

Returns the values for the selected column from each row of the result set, as a delimited list

Public Function ReturnSingleRecAsDictionary(sqlStr As String, Optional DebugOn As Boolean = False, Optional SuppressError As Boolean = False) As xf_Dictionary

ProductID 2 ProductName Chang CategoryID 1