Connect MS Access to SQL Server Compact database using ADO

Question

I am trying to connect to a SQL Server Compact edition db (.sdf) from Access 2003. Here's the start of my code:

Const sdfPath = "C:\SSCE\AmphiData.sdf"

Dim pConn as ADODB.Connection

Set pConn = new ADODB.Connection

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source = " & sdfPath

pConn.Open

The Open command fails with error: Method 'Open' of object '_Connection' failed.

The path to the .sdf file is correct; I don't have SSCE installed on this computer but I did register the file "SQLCEOLEDB35.dll". Before I registered the dll I got a 'Provider not found' error, so I assume that I got the right dll.

I'm not sure how to proceed from here - does anyone have a suggestion?

Later note - problem kind of solved: The code started working after I did a full install of SSCE. There is at least one more .dll involved. I didn't do the full install at first because I wanted to keep the installation of the Access front end as clean as possible.

Answer 1

There is a very useful site called Connection Strings

If you are running on Vista, SQL CE may not have installed properly: http://groups.google.com/group/microsoft.public.sqlserver.ce/browse_thread/thread/e03328c2ed526d81?pli=1

Answer 2

I did not think SQL Server Compact could be accessed thru MS Access 2003? Can someone confirm for sure if this is possible to manipulate SQL Server Compact 3.5 thru MS Access 2003?