1
\$\begingroup\$

SQL Server has had LocalDB since 2012. The main primary purpose for the LocalDb is to support developers without requiring a full install and running a service. However, a LocalDb can be also used in production as a local database in lieu of a traditional embedded database.

There are API for managing a LocalDb instance and getting metadata documented here. However, the API are incompatible with VBA for two reasons:

1) The API uses different calling convention, __cdecl, whereas VBA can only have Declares working against a __stdcall convention (I believe others like __fastcall are acceptable as well but don't quote me on that).

2) The API has no ANSI version. Normally, whenever there are strings being marshaled, VBA will always convert the Unicode string into ANSI, so when we have a Declare with a ByVal Something As String, VBA always assumes the string must be converted to ASCII, which is why we always have to use ***A versions of the Declares from common DLLs. Given the lack of an ANSI version, the next best thing to do is to marshal the strings as a byte array then assign to VBA's String variable, which will succeed as-is without any additional work.

So to get started, we need a C++ shim DLL. While this can have been done with C#, the intention is to keep the DLL very small and not rely on any particular .NET framework. This is compiled without the /clr switch. On itself, nothing exciting since we are simply calling the function, changing the calling convention on the way.

LocalDbShim.cpp

#define LOCALDB_DEFINE_PROXY_FUNCTIONS
#include <sqlncli.h>

HRESULT __stdcall SLocalDbCreateInstance(
    PCWSTR wszVersion,
    PCWSTR pInstanceName,
    DWORD dwFlags
) {
    return LocalDBCreateInstance(wszVersion, pInstanceName, dwFlags);
};

HRESULT __stdcall SLocalDbStartInstance(
    PCWSTR pInstanceName,
    DWORD dwFlags,
    LPWSTR wszSqlConnection,
    LPDWORD lppchSqlConnection
) {
    return LocalDBStartInstance(pInstanceName, dwFlags, wszSqlConnection, lppchSqlConnection);
};

HRESULT __stdcall SLocalDbStopInstance(
    PCWSTR pInstanceName,
    DWORD dwFlags,
    ULONG ulTimeout
) {
    return LocalDBStopInstance(pInstanceName, dwFlags, ulTimeout);
};

HRESULT __stdcall SLocalDbDeleteInstance(
    PCWSTR pInstanceName, 
    DWORD dwFlags
) {
    return LocalDBDeleteInstance(pInstanceName, dwFlags);
};

HRESULT __stdcall SLocalDbGetVersionInfo(
    PCWSTR wszVersion,
    PLocalDBVersionInfo pVersionInfo,
    DWORD cbVersionInfo
) {
    return LocalDBGetVersionInfo(wszVersion, pVersionInfo, cbVersionInfo);
};

HRESULT __stdcall SLocalDbGetVersions(
    PTLocalDBVersion pVersions, 
    LPDWORD lpdwNumberOfVersions
) {
    return LocalDBGetVersions(pVersions, lpdwNumberOfVersions);
};

HRESULT __stdcall SLocalDbGetInstanceInfo(
    PCWSTR wszInstanceName,
    PLocalDBInstanceInfo pInfo,
    DWORD cbInfo
) {
    return LocalDBGetInstanceInfo(wszInstanceName, pInfo, cbInfo);
};

HRESULT __stdcall SLocalDbGetInstances(
    PTLocalDBInstanceName pInstanceNames,
    LPDWORD lpdwNumberOfInstances
) {
    return LocalDBGetInstances(pInstanceNames, lpdwNumberOfInstances);
};

HRESULT __stdcall SLocalDbStartTracing() {
    return LocalDBStartTracing();
};

HRESULT __stdcall SLocalDbStopTracing() {
    return LocalDBStopTracing();
};

HRESULT __stdcall SLocalDbShareInstance(
    PSID pOwnerSID,
    PCWSTR wszLocalDBInstancePrivateName,
    PCWSTR wszSharedName,
    DWORD dwFlags
) {
    return LocalDBShareInstance(pOwnerSID, wszLocalDBInstancePrivateName, wszSharedName, dwFlags);
};

HRESULT __stdcall SLocalDbUnshareInstance(
    PCWSTR pInstanceName,
    DWORD dwFlags
) {
    return LocalDBUnshareInstance(pInstanceName, dwFlags);
};

HRESULT __stdcall SLocalDbFormatMessage(
    HRESULT hrLocalDB,
    DWORD dwFlags,
    DWORD dwLanguageId,
    LPWSTR wszMessage,
    LPDWORD lpcchMessage
) {
    return LocalDBFormatMessage(hrLocalDB, dwFlags, dwLanguageId, wszMessage, lpcchMessage);
};

We then need to export them. We can do this with a .def file:

LocalDbShim.def

LIBRARY LOCALDBSHIM
EXPORTS
    SLocalDbCreateInstance  @1
    SLocalDbStartInstance   @2
    SLocalDbStopInstance    @3
    SLocalDbDeleteInstance  @4
    SLocalDbGetVersionInfo  @5
    SLocalDbGetVersions     @6
    SLocalDbGetInstanceInfo @7
    SLocalDbGetInstances    @8
    SLocalDbStartTracing    @9
    SLocalDbStopTracing     @10
    SLocalDbShareInstance   @11
    SLocalDbUnshareInstance @12
    SLocalDbFormatMessage   @13

This will build a dll named LocalDbShim.dll which in my case weighed at 46 kb. In comparison to the LocalDb installation, this is pretty small.

We need to make use of the DLL in the VBA. However, we need constants, UDTs and Declares defined. Let's get them out of the way. We do need few Win32 API declared, though...

Win32 Stuff

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Long
End Type

Private Type FILETIME
  dwLowDateTime As Long
  dwHighDateTime As Long
End Type

Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" ( _
    lpFileTime As FILETIME, _
    lpSystemTime As SYSTEMTIME _
) As Boolean

Private Declare PtrSafe Function ConvertStringSidToSid Lib "Advapi32.dll" Alias "ConvertStringSidToSidW" ( _
    ByVal StringSid As LongPtr, _
    ByRef pSid As LongPtr _
) As Boolean

Private Declare PtrSafe Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" ( _
    ByVal lpPathName As String _
) As Boolean

We need to define constants and enums based on the API reference for making it easier to use API within VBA.

Local Db API Constants & Enums

Private Const ModuleName = "LocalDB API"
Private Const NO_FLAGS_SET As Long = &H0
Private Const ERROR_INVALID_SID As Long = &H537

Private Const MAX_LOCALDB_INSTANCE_NAME_LENGTH As Long = 128
Private Const MAX_LOCALDB_SQLCONNECTION_BUFFER_SIZE As Long = 260
Private Const MAX_STRING_SID_LENGTH As Long = 186
Private Const MAX_LOCALDB_VERSION_LENGTH As Long = 43

Private Const WCHAR_LEN As Long = 2

Private Enum LocalDbApiErrorCodes
    InvalidSIDString = vbObjectError + 1
    UnknownSIDError = vbObjectError + 2
    UnexpectedReturn = vbObjectError + 3
    InvalidTimeout = vbObjectError + 4
End Enum

Public Enum LocalDbHResults
    S_OK = &H0
    LOCALDB_ERROR_CANNOT_CREATE_INSTANCE_FOLDER = &H89C50100        'A folder cannot be created under %userprofile%.
    LOCALDB_ERROR_INVALID_PARAMETER = &H89C50101                    'One or more specified input parameters are invalid.
    LOCALDB_ERROR_INSTANCE_EXISTS_WITH_LOWER_VERSION = &H89C50102   'The specified instance already exists but its version is lower than requested.
    LOCALDB_ERROR_CANNOT_GET_USER_PROFILE_FOLDER = &H89C50103       'A user profile folder cannot be retrieved.
    LOCALDB_ERROR_INSTANCE_FOLDER_PATH_TOO_LONG = &H89C50104        'The path where the instance should be stored is longer than MAX_PATH.
    LOCALDB_ERROR_CANNOT_ACCESS_INSTANCE_FOLDER = &H89C50105        'An instance folder cannot be accessed.
    LOCALDB_ERROR_CANNOT_ACCESS_INSTANCE_REGISTRY = &H89C50106      'An instance registry cannot be accessed.
    LOCALDB_ERROR_UNKNOWN_INSTANCE = &H89C50107                     'The instance does not exist.
    LOCALDB_ERROR_INTERNAL_ERROR = &H89C50108                       'An unexpected error occurred. See the event log for details.
    LOCALDB_ERROR_CANNOT_MODIFY_INSTANCE_REGISTRY = &H89C50109      'An instance registry cannot be modified.
    LOCALDB_ERROR_SQL_SERVER_STARTUP_FAILED = &H89C5010A            'A SQL Server process is started but SQL Server startup failed.
    LOCALDB_ERROR_INSTANCE_CONFIGURATION_CORRUPT = &H89C5010B       'An instance configuration is corrupted.
    LOCALDB_ERROR_CANNOT_CREATE_SQL_PROCESS = &H89C5010C            'A process for SQL Server cannot be created.
    LOCALDB_ERROR_UNKNOWN_VERSION = &H89C5010D                      'The specified version is not available.
    LOCALDB_ERROR_UNKNOWN_LANGUAGE_ID = &H89C5010E                  'The message is not available in the requested language.
    LOCALDB_ERROR_INSTANCE_STOP_FAILED = &H89C5010F                 'The stop operation failed to complete within the given time.
    LOCALDB_ERROR_UNKNOWN_ERROR_CODE = &H89C50110                   'The requested message does not exist.
    LOCALDB_ERROR_VERSION_REQUESTED_NOT_INSTALLED = &H89C50111      'The specified patch level is not installed.
    LOCALDB_ERROR_INSTANCE_BUSY = &H89C50112                        'The specified instance is running.
    LOCALDB_ERROR_INVALID_OPERATION = &H89C50113                    'Default LocalDB instances cannot be created, stopped or deleted manually.
    LOCALDB_ERROR_INSUFFICIENT_BUFFER = &H89C50114                  'The input buffer is too short, and truncation was not requested.
    LOCALDB_ERROR_WAIT_TIMEOUT = &H89C50115                         'A time-out occurred while trying to acquire the synchronization locks.
    LOCALDB_ERROR_NOT_INSTALLED = &H89C50116                        'SQL Server Express LocalDB is not installed on the computer.
    LOCALDB_ERROR_XEVENT_FAILED = &H89C50117                        'Failed to start XEvent engine within the LocalDB Instance API.
    LOCALDB_ERROR_AUTO_INSTANCE_CREATE_FAILED = &H89C50118          'Cannot create an automatic instance. See the Windows Application event log for error details.
    LOCALDB_ERROR_SHARED_NAME_TAKEN = &H89C50119                    'The specified shared name is already taken.
    LOCALDB_ERROR_CALLER_IS_NOT_OWNER = &H89C5011A                  'API caller is not LocalDB instance owner.
    LOCALDB_ERROR_INVALID_INSTANCE_NAME = &H89C5011B                'The specified instance name is invalid.
    LOCALDB_ERROR_INSTANCE_ALREADY_SHARED = &H89C5011C              'The specified instance is already shared.
    LOCALDB_ERROR_INSTANCE_NOT_SHARED = &H89C5011D                  'The specified LocalDB instance is not shared.
    LOCALDB_ERROR_ADMIN_RIGHTS_REQUIRED = &H89C5011E                'Administrator privileges are required in order to execute this operation.
    LOCALDB_ERROR_TOO_MANY_SHARED_INSTANCES = &H89C5011F            'There are too many shared instances.
End Enum

Public Enum LocalDbFormatMessageFlags
    LOCALDB_TRUNCATE_ERR_MESSAGE = &H1
End Enum

Public Enum LocalDbStopInstanceFlags
    LOCALDB_SHUTDOWN_KILL_PROCESS = &H1
    LOCALDB_SHUTDOWN_WITH_NOWAIT = &H2
End Enum

We also need structures that represents API's structure. But we need to have both private UDT and public UDT because we will be doing string conversion inside VBA. Note that the private UDTs uses fixed byte arrays while public UDTs uses dynamically-sized String which also means the length of the structure are very different because the content of the string are not pinned to the latter structure.

LocalDb API Structures and VBA-friendly UDT

Public Type LocalDbInstanceInfo
'See LocalDBInstanceInfo_Private type
    LocalDBInstanceInfoSize As Long
    InstanceName As String
    Exists As Boolean
    ConfigurationCorrupted As Boolean
    IsRunning As Boolean
    Major As Long
    Minor As Long
    Build As Long
    Revision As Long
    LastStartDateUTC As Date
    Connection As String
    IsShared As Long
    LocalDBInstanceName As String
    OwnerSID As String
    IsAutomatic As Boolean
End Type

Public Type LocalDbVersionInfo
'See LocalDBVersionInfo_Private type
    LocalDBVersionInfoSize As Long
    Version As String
    Exists As Boolean
    Major As Long
    Minor As Long
    Build As Long
    Revision As Long
End Type

Private Type LocalDbInstanceInfo_Private
    cbLocalDBInstanceInfoSize As Long
    wszInstanceName(0 To ((MAX_LOCALDB_INSTANCE_NAME_LENGTH * WCHAR_LEN) + 1)) As Byte
    bExists As Long
    bConfigurationCorrupted As Long
    bIsRunning As Long
    dwMajor As Long
    dwMinor As Long
    dwBuild As Long
    dwRevision As Long
    ftLastStartDateUTC As FILETIME
    wszConnection(0 To ((MAX_LOCALDB_SQLCONNECTION_BUFFER_SIZE * WCHAR_LEN) - 1)) As Byte
    bIsShared As Long
    TLocalDBInstanceName(0 To ((MAX_LOCALDB_INSTANCE_NAME_LENGTH * WCHAR_LEN) + 1)) As Byte
    wszOwnerSID(0 To ((MAX_STRING_SID_LENGTH * WCHAR_LEN) + 1)) As Byte
    bIsAutomatic As Long
End Type

Private Type LocalDbVersionInfo_Private
    cbLocalDBVersionInfoSize As Long
    wszVersion(0 To ((MAX_LOCALDB_VERSION_LENGTH * WCHAR_LEN) + 1)) As Byte
    bExists As Long
    dwMajor As Long
    dwMinor As Long
    dwBuild As Long
    dwRevision As Long
End Type

With the scaffolding setup, we can then define our Declares basing on the LocalDbShim.dll.

LocalDb API Declares

Private Declare PtrSafe Function SLocalDbCreateInstance Lib "LocalDbShim.dll" ( _
    ByVal pswzVersion As LongPtr, _
    ByVal pswzInstanceName As LongPtr, _
    ByVal dwFlags As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbStartInstance Lib "LocalDbShim.dll" ( _
    ByVal pInstanceName As LongPtr, _
    ByVal dwFlags As Long, _
    ByVal wszSqlConnection As LongPtr, _
    ByRef lppchSqlConnection As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbStopInstance Lib "LocalDbShim.dll" ( _
    ByVal pInstanceName As LongPtr, _
    ByVal dwFlags As Long, _
    ByVal ulTimeout As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbDeleteInstance Lib "LocalDbShim.dll" ( _
    ByVal pInstanceName As LongPtr, _
    ByVal dwFlags As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbGetVersionInfo Lib "LocalDbShim.dll" ( _
    ByVal wszVersion As LongPtr, _
    ByRef pVersionInfo As LocalDbVersionInfo_Private, _
    ByVal cbVersionInfo As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbGetVersions Lib "LocalDbShim.dll" ( _
    ByVal pVersions As LongPtr, _
    ByRef lpdwNumberOfVersions As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbGetInstanceInfo Lib "LocalDbShim.dll" ( _
    ByVal wszInstanceName As LongPtr, _
    ByRef pInstanceInfo As LocalDbInstanceInfo_Private, _
    ByVal dwInstanceInfoSize As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbGetInstances Lib "LocalDbShim.dll" ( _
    ByVal pInstances As LongPtr, _
    ByRef lpdwNumberOfInstances As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbStartTracing Lib "LocalDbShim.dll" ( _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbStopTracing Lib "LocalDbShim.dll" ( _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbShareInstance Lib "LocalDbShim.dll" ( _
    ByVal pOwnerSID As LongPtr, _
    ByVal wszLocalDBInstancePrivateName As LongPtr, _
    ByVal wszSharedName As LongPtr, _
    ByVal dwFlags As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbUnshareInstance Lib "LocalDbShim.dll" ( _
    ByVal pInstanceName As LongPtr, _
    ByVal dwFlags As Long _
) As LocalDbHResults

Private Declare PtrSafe Function SLocalDbFormatMessage Lib "LocalDbShim.dll" ( _
    ByVal hrLocalDB As LocalDbHResults, _
    ByVal dwFlags As Long, _
    ByVal dwLanguageId As Long, _
    ByVal wszMessage As LongPtr, _
    ByRef lpcchMessage As Long _
) As LocalDbHResults

Phew. That was just the Declarations section of the module. Now, we need to deal with a problem... Note that the Lib argument is simply just LocalDbShim.dll, without the path. Because a VBA project lives in a document, it could be moved anywhere. We might want the DLL to be in the same folder as the document or maybe on some convenient place. Thus we have no guarantee that VBA will know where to find the DLL. So we need to deal with this problem of locating the DLL. One way to do this is to use the LoadLibrary but in my testing, it seemed to make thing less stable which was odd. I found that simply changing the directory, we got better result, which seems hacky but because it was more stable, it seems worthwhile.

LoadDLL Function

Public Function LoadDLL( _
    ByRef FolderContainingDLLPath As String _
) As Boolean
'API functions such as LoadLibrary or GetModuleHandle seems to
'destabilize the program and cause crash. Thus the relatively
'ugly hack of using ChDir + calling the DLL function to load it
'so that the program is now aware of the DLL's location and will
'take care of freeing it appropriately when the application quits.
'This only needs to be called once per application session (e.g.,
'after a restart for instance). We use API SetCurrentDirectory so
'that we can handle a UNC path in case the application was opened
'from an UNC path and the ChDir function does not support UNC path.
    On Error GoTo ErrHandler

    Dim TempDirectory As String

    TempDirectory = CurDir$
    ChDir FolderContainingDLLPath
    LoadDLL = Len(LocalDbApi.FormatMessage(S_OK))

ExitProc:
    On Error Resume Next
    'Do cleanup here
    SetCurrentDirectory TempDirectory
    Exit Function
ErrHandler:
    LoadDLL = False
    Select Case Err.Number
        Case Else
            VBA.MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    End Select
    Resume ExitProc
    Resume 'for Debugging
End Function

Here are the rest of the functions that provides wrapping and thus making it 100% VBA-friendly. However, being a wrapper function, they are still rough and will throw errors directly. They may need to be further wrapped in a class or something to make management easier by consolidating some of the steps required to get started. But that's beyond the scope ATM.

Rest of LocalDbApi module functions

Public Function CreateInstance( _
    ByVal InstanceName As String, _
    Optional ByVal VersionName As String = vbNullString _
) As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbCreateInstance(StrPtr(VersionName), StrPtr(InstanceName), NO_FLAGS_SET)
    If hr = S_OK Then
        CreateInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function DeleteInstance( _
    ByVal InstanceName As String _
) As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbDeleteInstance(StrPtr(InstanceName), NO_FLAGS_SET)
    If hr = S_OK Then
        DeleteInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function FormatMessage( _
    ByRef HResult As LocalDbHResults, _
    Optional ByRef LanguageId As Long = &H0 _
) As String
    Dim MessageBuffer As String
    Dim BufferSize As Long

    Dim hr As LocalDbHResults

    'Because this function is called by other error handler, it should not
    'cascade errors by throwing the errors back to the caller.
    On Error Resume Next

    'Use VarPtr() because we cannot pass in a null pointer for the wszMessage parameter
    hr = SLocalDbFormatMessage(HResult, NO_FLAGS_SET, LanguageId, VarPtr(MessageBuffer), BufferSize)
    If hr = LOCALDB_ERROR_INSUFFICIENT_BUFFER Then
        MessageBuffer = Space$(BufferSize)
        hr = SLocalDbFormatMessage(HResult, NO_FLAGS_SET, LanguageId, StrPtr(MessageBuffer), BufferSize)
    End If

    If hr = S_OK Then
        FormatMessage = TrimString(MessageBuffer)
    Else
        'We shouldn't call the LocalDbFormatMessage as it might cause cascading errors,
        'nor should we Err.Raise here as this might be called by other error handlers
        Select Case hr
            Case LocalDbHResults.LOCALDB_ERROR_UNKNOWN_ERROR_CODE
                FormatMessage = "Unknown error code was passed in. It might be an error outside the LocalDB API and thus cannot be formatted by Local DB API. The error code passed in was: " & HResult
            Case LocalDbHResults.LOCALDB_ERROR_UNKNOWN_LANGUAGE_ID
                FormatMessage = "Unknown language code was passed in. The language ID passed in was: " & LanguageId
            Case Else
                FormatMessage = "An error occurred while formatting the error message for given error code: " & HResult
        End Select
    End If
End Function

Public Function GetInstances() As String()
    Dim NameBuffer As String
    Dim BufferSize As Long

    Dim hr As LocalDbHResults

    Const MaxBufferLength As Long = MAX_LOCALDB_INSTANCE_NAME_LENGTH + 1

    On Error GoTo 0 'Return errors back to the caller

    'Use VarPtr() because we can't pass in a null pointer for the pInstances parameter
    hr = SLocalDbGetInstances(VarPtr(NameBuffer), BufferSize)
    If hr = LOCALDB_ERROR_INSUFFICIENT_BUFFER Then
        NameBuffer = Space$(BufferSize * MaxBufferLength)
        hr = SLocalDbGetInstances(StrPtr(NameBuffer), BufferSize)
    End If

    If hr = S_OK Then
        Dim strArray() As String
        Dim i As Long

        ReDim strArray(BufferSize - 1)
        For i = 0 To (BufferSize - 1)
            strArray(i) = TrimString(Mid$(NameBuffer, (i * MaxBufferLength) + 1, MaxBufferLength))
        Next

        GetInstances = strArray
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function GetInstanceInfo( _
    Optional ByVal InstanceName As String = vbNullString _
) As LocalDbInstanceInfo
    Dim InstanceInfo As LocalDBInstanceInfo_Private
    Dim SysTime As SYSTEMTIME

    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbGetInstanceInfo(StrPtr(InstanceName), InstanceInfo, LenB(InstanceInfo))
    If hr = S_OK Then
        GetInstanceInfo.Major = InstanceInfo.dwMajor
        GetInstanceInfo.Minor = InstanceInfo.dwMinor
        GetInstanceInfo.Build = InstanceInfo.dwBuild
        GetInstanceInfo.Revision = InstanceInfo.dwRevision
        GetInstanceInfo.ConfigurationCorrupted = InstanceInfo.bConfigurationCorrupted
        GetInstanceInfo.Exists = InstanceInfo.bExists
        GetInstanceInfo.IsAutomatic = InstanceInfo.bIsAutomatic
        GetInstanceInfo.IsRunning = InstanceInfo.bIsRunning
        GetInstanceInfo.IsShared = InstanceInfo.bIsShared
        GetInstanceInfo.LocalDBInstanceName = TrimString(InstanceInfo.TLocalDBInstanceName)
        GetInstanceInfo.InstanceName = TrimString(InstanceInfo.wszInstanceName)
        GetInstanceInfo.Connection = TrimString(InstanceInfo.wszConnection)
        GetInstanceInfo.OwnerSID = TrimString(InstanceInfo.wszOwnerSID)
        GetInstanceInfo.LocalDBInstanceInfoSize = InstanceInfo.cbLocalDBInstanceInfoSize
        If FileTimeToSystemTime(InstanceInfo.ftLastStartDateUTC, SysTime) Then
            GetInstanceInfo.LastStartDateUTC = _
                DateAdd("s", _
                        DatePart("s", TimeSerial(SysTime.wHour, SysTime.wMinute, SysTime.wSecond)), _
                        DateSerial(SysTime.wYear, SysTime.wMonth, SysTime.wDay) _
                )
        End If
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function GetVersions() As String()
    Dim VersionBuffer As String
    Dim BufferSize As Long

    Dim hr As LocalDbHResults

    Const conMaxLength As Long = MAX_LOCALDB_VERSION_LENGTH + 1

    On Error GoTo 0 'Return errors back to the caller

    'Use VarPtr() because we can't pass in a null pointer for the pVersions parameter
    hr = SLocalDbGetVersions(VarPtr(VersionBuffer), BufferSize)
    If hr = LOCALDB_ERROR_INSUFFICIENT_BUFFER Then
        VersionBuffer = Space$(BufferSize * conMaxLength)
        hr = SLocalDbGetVersions(StrPtr(VersionBuffer), BufferSize)
    End If

    If hr = S_OK Then
        Dim strArray() As String
        Dim i As Long

        ReDim strArray(BufferSize - 1)
        For i = 0 To (BufferSize - 1)
            strArray(i) = TrimString(Mid$(VersionBuffer, (i * conMaxLength) + 1, conMaxLength))
        Next

        GetVersions = strArray
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function GetVersionInfo( _
    Optional ByVal VersionName As String _
) As LocalDBVersionInfo
    Dim VersionInfo As LocalDBVersionInfo_Private

    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbGetVersionInfo(StrPtr(VersionName), VersionInfo, LenB(VersionInfo))
    If hr = S_OK Then
        GetVersionInfo.Version = TrimString(VersionInfo.wszVersion)
        GetVersionInfo.Major = VersionInfo.dwMajor
        GetVersionInfo.Minor = VersionInfo.dwMinor
        GetVersionInfo.Build = VersionInfo.dwBuild
        GetVersionInfo.Revision = VersionInfo.dwRevision
        GetVersionInfo.Exists = VersionInfo.bExists
        GetVersionInfo.LocalDBVersionInfoSize = VersionInfo.cbLocalDBVersionInfoSize
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function ShareInstance( _
    ByVal OwnerSIDString As String, _
    ByVal InstancePrivateName As String, _
    ByVal InstanceSharedName As String _
) As Boolean
    Dim pSid As LongPtr

    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    If Len(OwnerSIDString) Then
        If Not ConvertStringSidToSid(StrPtr(OwnerSIDString), pSid) Then
            If Err.LastDllError = ERROR_INVALID_SID Then
                Err.Raise LocalDbApiErrorCodes.InvalidSIDString, ModuleName, "Invalid SID string was provided."
            Else
                Err.Raise LocalDbApiErrorCodes.UnknownSIDError, ModuleName, "Unknown error occurred when converting SID string to SID structure. The error number provided was " & Err.LastDllError
            End If
        End If
    End If

    hr = SLocalDbShareInstance(pSid, StrPtr(InstancePrivateName), StrPtr(InstanceSharedName), NO_FLAGS_SET)
    If hr = S_OK Then
        ShareInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function StartInstance( _
    ByVal InstanceName As String, _
    Optional ByRef ConnectionString As String _
) As Boolean
    Dim NameBuffer As String
    Dim BufferSize As Long

    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbStartInstance(StrPtr(InstanceName), NO_FLAGS_SET, StrPtr(vbNullString), BufferSize)
    'Inconsistent but that's what the doucmentation says in response to getting the buffer size...
    If hr = S_OK Then
        If BufferSize Then
            NameBuffer = Space$(BufferSize)
            hr = SLocalDbStartInstance(StrPtr(InstanceName), NO_FLAGS_SET, StrPtr(NameBuffer), BufferSize)
        Else
            'This shouldn't be possible
            Err.Raise LocalDbApiErrorCodes.UnexpectedReturn, ModuleName, "Unexpected return from API - LocalDBStartInstance returned S_OK but did not set the lppchSqlConnection to a nonzero number."
        End If
    End If

    If hr = S_OK Then
        ConnectionString = TrimString(NameBuffer)
        StartInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function StartTracing() As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbStartTracing()
    If hr = S_OK Then
        StartTracing = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function StopInstance( _
    ByVal InstanceName As String, _
    Optional ByVal Timeout As Long = 0, _
    Optional ByVal Flags As LocalDbStopInstanceFlags = NO_FLAGS_SET _
) As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    If Timeout < 0 Then
        Err.Raise LocalDbApiErrorCodes.InvalidTimeout, ModuleName, "Timeout parameter cannot be a negative number."
    End If

    hr = SLocalDbStopInstance(StrPtr(InstanceName), Flags, Timeout)
    If hr = S_OK Then
        StopInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function StopTracing() As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbStopTracing()
    If hr = S_OK Then
        StopTracing = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Public Function UnshareInstance( _
    ByVal InstanceSharedName As String _
) As Boolean
    Dim hr As LocalDbHResults

    On Error GoTo 0 'Return errors back to the caller

    hr = SLocalDbUnshareInstance(StrPtr(InstanceSharedName), NO_FLAGS_SET)
    If hr = S_OK Then
        UnshareInstance = True
    Else
        Err.Raise hr, ModuleName, LocalDbApi.FormatMessage(hr)
    End If
End Function

Private Function TrimString(InputValue As Variant) As String
    TrimString = Replace$(Trim$(InputValue), vbNullChar, vbNullString)
End Function

Of course we need testing code, so here's one that gives good enough coverage of most API functions likely to be used. Note that test mode assumes the functions are housed in a standard module named LocalDbApi.

Testing Code

Public Sub TestIt()
    Dim InstanceName As String
    Dim ConnectionString As String
    Dim VersionNames() As String
    Dim VersionName As Variant

    InstanceName = "TestInstance"

    If Not LocalDbApi.LoadDLL(CurrentProject.Path) Then
        Debug.Print "Cannot load the DLL..."
        Exit Sub
    End If

    VersionNames = LocalDbApi.GetVersions
    For Each VersionName In VersionNames
        Debug.Print VersionName
    Next

    If Not LocalDbApi.CreateInstance(InstanceName) Then
        Debug.Print "Cannot create an instance..."
        Exit Sub
    End If

    If Not LocalDbApi.StartInstance(InstanceName, ConnectionString) Then
        Debug.Print "Cannot start an instance..."
        Exit Sub
    End If

    Debug.Print ConnectionString

    With LocalDbApi.GetInstanceInfo(InstanceName)
        Debug.Print "Build:                       ", .Build
        Debug.Print "Configuration Corrupted:     ", .ConfigurationCorrupted
        Debug.Print "Connection:                  ", .Connection
        Debug.Print "Exists:                      ", .Exists
        Debug.Print "Instance Name:               ", .InstanceName
        Debug.Print "Is Automatic:                ", .IsAutomatic
        Debug.Print "Is Running:                  ", .IsRunning
        Debug.Print "Is Shared:                   ", .IsShared
        Debug.Print "Last Start Date UTC:         ", .LastStartDateUTC
        Debug.Print "Local DB Instance Info Size: ", .LocalDBInstanceInfoSize
        Debug.Print "Local DB Instance Name:      ", .LocalDBInstanceName
        Debug.Print "Major:                       ", .Major
        Debug.Print "Minor:                       ", .Minor
        Debug.Print "Owner SID:                   ", .OwnerSID
        Debug.Print "Revision:                    ", .Revision
    End With

    If LocalDbApi.StartTracing Then
        Debug.Print "Started Tracing...."
        If LocalDbApi.StopTracing Then
            Debug.Print "Stopped Tracing..."
        End If
    End If

    If Not LocalDbApi.StopInstance(InstanceName) Then
        Debug.Print "Couldn't stop instance..."
    End If

    If Not LocalDbApi.DeleteInstance(InstanceName) Then
        Debug.Print "Couldn't delete instance..."
    End If
End Sub
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.