mykelperz avatar image
mykelperz asked

How to use SQLCE 4.0 on VBA

I have this Class Module that helps me configure an ADODB.Connection. An MSAccessModule which uses MSAccess is working very nice but I'd like to test my luck if SQLCE works better.

ClassName: SQLCEModule

Option Explicit
Private cnn As ADODB.Connection
Public Sub TryConnect()
On Error GoTo Errhandler
If cnn Is Nothing Then Set cnn = New ADODB.Connection
If cnn.State = adStateOpen Then Exit Sub
With cnn
    .Provider = "Microsoft.SQLSERVER.CE.OLEDB.4.0;"
    .CursorLocation = adUseServer
    .Mode = adModeShareExclusive
    .Properties("Data Source") = "C:\pricing\data.sdf"
    .Properties("SSCE:Temp File Directory") = "C:\pricing\tmp"
    .Properties("Persist Security Info") = False
End With
Exit Sub
Set cnn = Nothing
End Sub

Public Function ActiveConnection() As ADODB.Connection
    Set ActiveConnection = cnn
End Function

And in a separate module I reuse this by:

Private Database as New SQLCEModule
Public Sub Export()
Dim wbSIMDatabase as workbook, wsSIMDatabase as worksheet
'I Removed referencing code for Excel Objects to shorten the post

Dim cnn As ADODB.Connection, cmd As ADODB.Command, rsSimDB As ADODB.Recordset, rsLoad As ADODB.Recordset
If isExportToData Then
    'Export to Database
    Application.StatusBar = "Preparing Database..."
    Dim sSQL As String, lRow As Long
    Set cnn = Database.ActiveConnection   
    Set rsSimDB = New ADODB.Recordset
    sSQL = "SELECT * FROM [SIM Database]"
    rsSimDB.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    Application.StatusBar = "Exporting Data"

    Dim arrFields(11) As Variant 'This is all the fields from [SIM Database]
        arrFields(0) = "Status"
        arrFields(1) = "PPERef"
        arrFields(2) = "PPENum"
        arrFields(3) = "NIP"
        arrFields(4) = "ClientName"
        arrFields(5) = "Segment"
        arrFields(6) = "IndustrialStatus"
        arrFields(7) = "Tariff"
        arrFields(8) = "DSO"
        arrFields(9) = "DataQuality"
        arrFields(10) = "Product"
        arrFields(11) = "ForecastingGroup"

    Dim arrFields(11) As Variant
        arrVal(0) = SimWorksheet.Cells(lRow, 1).Value2
        arrVal(1) = SimWorksheet.Cells(lRow, 2).Value2
        arrVal(2) = SimWorksheet.Cells(lRow, 3).Value2
        arrVal(3) = SimWorksheet.Cells(lRow, 4).Value2
        arrVal(4) = SimWorksheet.Cells(lRow, 5).Value2
        arrVal(5) = SimWorksheet.Cells(lRow, 6).Value2
        arrVal(6) = SimWorksheet.Cells(lRow, 7).Value2
        arrVal(7) = SimWorksheet.Cells(lRow, 11).Value2
        arrVal(8) = SimWorksheet.Cells(lRow, 12).Value2
        arrVal(9) = SimWorksheet.Cells(lRow, 13).Value2
        arrVal(10) = SimWorksheet.Cells(lRow, 24).Value2
        arrVal(11) = SimWorksheet.Cells(lRow, 30).Value2
    rssimdb.AddNew arrFields, arrVal

Set cnn = Nothing
Set SimDatabaseWorksheet = Nothing
If Not wbSIMDatabase Is Nothing Then wbSIMDatabase.Close
Set wbSIMDatabase = Nothing
Application.StatusBar = False
End Sub

Here's the problem:
I am receiving an error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." on this line

rsSimDB.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

I've made readings on the web and tried various combinations of solutions:
* Changing the CursorLocation to adUseClient allows me to run rsSimDB.Open but the error occurs when I call rsSimDB.Add arrFields, arrVal
-I am very certain that the cause of the error is not due to data type because I tested it on a test table with a single nVarChar(1) column and added a value of "A" and still receiving the error

* Changing LockType of the recordset to adLockReadOnly allows me to use adUseServer but is not usefull because I will need to call rsSimDB.AddNew later

cnn.connectionstring is stored as follows:
"Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\pricing\data.sdf;Mode=ReadWrite|Share Deny None;SSCE:Max Buffer Size=4096;SSCE:Database Password="";SSCE:Encrypt Database=False;SSCE:Default Lock Escalation=100;SSCE:Temp File Directory=C:\pricing\tmp;SSCE:Default Lock Timeout=5000;SSCE:AutoShrink Threshold=60;SSCE:Flush Interval=10;SSCE:Test Callback Pointer=0;SSCE:Max Database Size=256;SSCE:Temp File Max Size=128;SSCE:Encryption Mode=0;SSCE:Case Sensitive=False;"

sql-serversql servervba
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.