SQL CE 3.5 Connection issues (1 Viewer)

GBalcom

Much to learn!
Local time
Today, 02:47
Joined
Jun 7, 2012
Messages
459
Hey Everyone,
I've been struggling all day to get access to pull over a recordset in VBA from an sdf file (SQL CE 3.5) :banghead:

Here's the code thus far:

Code:
Public Function Connect(WorkOrderName As String) As Boolean
    ' Status:  BROKEN, DOESN'T WORK.  This was a development test to see if we can connect to the databases that microvellum creates.  So far we can't.
    ' Comments: Used to connect to the specific MV Work order Database
    ' Params  :
    ' Returns : Boolean
    ' Created : 06/21/17 08:06 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

Dim strPath As String
Dim strWoDir As String
Dim strConnection As String
Dim strSQL As String
'Path to work order db
strPath = "S:\Microvellum\Version7\Advanced_Frameless_Library_English_v038_0_Imperial\Work Orders\"     '"\\SERVER\Data\Data\Microvellum\Version7\Advanced_Frameless_Library_English_v038_0_Imperial\Work Orders\"

'Work Order directory
strWoDir = WorkOrderName & "\MicrovellumWorkOrder.sdf"

Debug.Print strPath & strWoDir

 
'The SQL Expression to be used.
strSQL = "SELECT Quantity,Name,Code,BarCode From PlacedSheets;"
Debug.Print strSQL

 
Dim ADODBcnt As ADODB.Connection
Dim ADODBrst As ADODB.Recordset
Dim ADODBfld As ADODB.Field

'Connection string.
strConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
 "Data Source=" & strPath & strWoDir & ";"
 
 Debug.Print strConnection
 
 
'Instantiate the ADODB objects.
Set ADODBcnt = New ADODB.Connection
Set ADODBrst = New ADODB.Recordset
 
'Open the connection to the database
ADODBcnt.Open strConnection
 
 
'Open a read only recordset
With ADODBrst
    .Open strSQL, ADODBcnt, adOpenForwardOnly, adLockReadOnly
    
If .EOF And .BOF Then
    'no records
    
Else
    .MoveFirst
    Do While Not .EOF
        Debug.Print "Qty: " & .Fields("Quantity").Value & "  Name: " & .Fields("Name").Value & "  Code: " & .Fields("Code").Value & "  BarCode: " & .Fields("Quantity").Value
        .MoveNext
    Loop
    
End If

.Close
End With


 
'Close down the connection.
ADODBcnt.Close
 

    Connect = True

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set ADODBrst = Nothing
    Set ADODBcnt = Nothing
    
    Exit Function

PROC_ERR:
    Err.Raise Err.Number
    Resume
    'TVCodeTools ErrorHandlerEnd

End Function

Running this in the immediate window, with this:
Code:
?Connect("82109-1204 GN")
S:\Microvellum\Version7\Advanced_Frameless_Library_English_v038_0_Imperial\Work Orders\82109-1204 GN\MicrovellumWorkOrder.sdf
SELECT Quantity,Name,Code,BarCode From PlacedSheets;
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=S:\Microvellum\Version7\Advanced_Frameless_Library_English_v038_0_Imperial\Work Orders\82109-1204 GN\MicrovellumWorkOrder.sdf;

I'm not seeing anything wrong with my connection..But I'm no pro either.

When I get to this line:
Code:
'Open the connection to the database
ADODBcnt.Open strConnection

I get the attached error.

I have installed the 32 bit sql compact 3.5 runtime on this machine, and I also have a reference to Microsoft ActiveX Data Objects 2.5 Library.


If I change my connection string to this:
Code:
'Connection string.
strConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
                "Data Source=" & strPath & strWoDir & ";" & _
                "Password =;"

Then I get the attached Error2.

Thanks for any help you can provide!
 

Attachments

  • Error.PNG
    Error.PNG
    4.5 KB · Views: 115
  • Error2.PNG
    Error2.PNG
    5.5 KB · Views: 113

GBalcom

Much to learn!
Local time
Today, 02:47
Joined
Jun 7, 2012
Messages
459
Good Idea Minty,
I tried it, but nothing changed.
 

Users who are viewing this thread

Top Bottom