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

Question

Is it possible to write a connection and open a SQL Compact 3.5 database from MS Access 2003? I want to be able to use MS Access 2003 to manage data in a SQL Compact 3.5 database. If possible, which operators will be used to open the database?

Answer 1

This is just an idea, and I can’t confirm that it will work, but given that SQL Compact does not have an ODBC driver and you cannot link tables, perhaps you can use the OLEDB connection string for SQL Compact as the Connect String Source stored QueryDef in Access. If you can do this, you can create a saved QueryDef for each table, and then use them as if the queries were related to tables.

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

But maybe worth a try. Perhaps this will not work, as I cannot find anywhere that anyone has done this in Access. But I really don't understand why this should not work.

Again, I could just be wrong.

Answer 2

Although I have not tried this specifically with SQL Compact, the connection to the server should be standard:

• Verify that the ADODB file (msado21.tlb) is correctly listed in the available tools.

• Write a connection string somewhere like this:

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

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

Once this is done, you can open the ADODB recordset and start manipulating 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