how to import/attach sql server compact edition tables/relationships with microsoft Access?

Question

I need to move my Sql Server CE database into MS Access, so my users who are familiar with Access can run queries on the data. Is there a free way to do that?

Thanks! David

Answer 1

You have two options, you can either import the data into access or link the tables in access so the data stays in SQL CE and is just referenced by access.

I don’t think access can import it directly so you will have to make an ODBC connection to your CE file using the ODNC admin program in windows. Once you have done that you can move onto the next step

On the file menu go to get external data and then either import or link depending on what option you want, on the next dialog box select ODBC databases as the file type and select the ODBC connection you just made.

Follow the on screen prompts from there and Roberts your mothers brother

EDIT:

Sorry yes I did mean ODBC, my finger must have slipped on the keyboard. Anyway Yes it looks like the ODBC driver does not exist however a OLEDB driver does so you could open up the database in code using ADO and either manipulate it there or loop through each table and insert it into an access table you created earlier. Here is some code showing how to open a SQL CE recordset in VBA

Sub test()
Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset
Set pConn = New ADODB.Connection
Dim cmd As New ADODB.Command
Set pRS = New ADODB.Recordset
' For 3.0 use PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
pConn.ConnectionString = 
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Northwind.sdf"
pConn.Open

cmd.ActiveConnection = pConn
cmd.CommandText = "SELECT * FROM Products"
Set pRS = cmd.Execute
' Open the recordset
While Not pRS.EOF
    Debug.Print pRS(0)
    Debug.Print pRS(1)
    pRS.MoveNext
Wend
End Sub