How to access SQL CE 3.5 from VB6

Question

We have a .NET 3.5 SP1 application written in C# that stores data in an SQL CE 3.5 Database. We also need to access (read only) this very data from a legacy VB6 application.

I don't know if this is at all possible. There are several approaches to this problem that I can think of.

1) I have read about ADOCE Connections, but this seems to be an option for embedded Visual Basic only

2) I can't seem to get a connection working using ADODB.Connection Objects like so

Dim MyConnObj As New ADODB.Connection
' Microsoft.SQLSERVER.CE.OLEDB.3.5
' Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
MyConnObj.ConnectionString = "Provider=SQLOLEDB;Data Source=c:\test.sdf"
MyConnObj.Open

Maybe this is just a bad choice of providers? I also tried the providers that appear as comments above and different connection strings, but to no avail. Both providers are not installed on my dev machine and won't be installed on my customer's machine.

3) Maybe there is a way to use a more generic approach like ODBC? But I believe this would result in setup / deployment work, right?

Does anyone have any experience with this scenario? As you can see, I am really looking for some good starting points. I also accept answers like "This is drop dead simple and so are you" as long as they come with some guiding directions ;-)

Answer 1

According to connectionstrings.com, you should be able to use

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=myPath\myData.sdf;

However, the OLEDB provider for SQL Server CE does not provide all functionality.

Answer 2

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\test.sdf;

Verify that you have the provider available. Start Regedit.exe, open the HKEY_CLASSES_ROOT node, scroll down and verify that you see the Microsoft.SQLSERVER.CE.OLEDB.3.5 key. If it is not there, you have to install it. Download link.

Answer 3

You could create a COM-visible C# component to extract the data for the VB6. That avoids using the potentially cranky OLEDB provider for SQL Server CE.

It has another benefit in that it adds a layer of insulation between the VB6 and the database structure.