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:
Running this in the immediate window, with this:
I'm not seeing anything wrong with my connection..But I'm no pro either.
When I get to this line:
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:
Then I get the attached Error2.
Thanks for any help you can provide!
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!