Connect SQL Server Compact 3.5 Database to MS Access 2003 using ADO?

Question

Is it possible to write connect and open a SQL Compact 3.5 database from within MS Access 2003? I want to be able to use MS Access 2003 to manipulate data in a SQL Compact 3.5 database. If it is possible, then what statements would be used to open the database?

Answer 1

Though I did not try it specifically with SQL Compact, connecting to the server should be standard:

1. Check that the ADODB file (msado21.tlb) is correctly refernced in your available tools

2. Write down your connection string somewhere like this one:

   MyConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDatabaseName;Data Source=YourSQLServerInstanceName"

   This string is written for an 'integrated security' context. In cas you want to change it for an SQL security context, please check here to update the string. Ideally, this string should be declared as a public variable in your code.

Once this is done, you can open an ADODB recordset and begin to manipulate it:

public sub connectionTest
Dim activeConnection as ADODB.connection, _
    activeRecordset as ADODB.recordset

Set activeConnection = New ADODB.connection
activeConnection.connectionString = myCOnnectionString
activeConnection.open

set activeRecordset = New ADODB.recordset
'this will open a read-only recordset'
activeRecordset.open _
    "SELECT * FROM myTableName", _
    activeConnection, _
    adOpenStatic, _
    adLockReadOnly

if activeRecordset.EOF and activeRecordset.BOF then
    debug.print "No records in this table"
else
    activeRecordset.moveFirst
    do while not activeRecordset.EOF
        debug.print activerecordset.fields("myFieldName").value
        activeRecordset.moveNext
    loop
endif

activeRecordset.close
set activeRecordset = nothing
activeConnection.close
set activeConnection = nothing

end sub

Answer 2

This is just an idea and I can't confirm that it will work, but given that SQL Compact lacks an ODBC driver and you can't have linked tables, perhaps you can use an OLEDB connect string for SQL Compact as the Source Connect String of a saved QueryDef in Access. If you can get that to work you may be able to create a saved QueryDef for each table, and then utilize them as though the queries were linked tables.

I can't test it on my machine because the only OLEDB provider I have installed is Jet, and Access doesn't seem to like that.

But it might be worth a try. Possibly it's not going to work, as I can't find anywhere that anyone has done this in Access. But I don't really see why it shouldn't work.

Again, I could simply be wrong, though.