It's possible connect an ADP to SDF file from SQL Server Compact Edition?


Hi to all!

I need create an small Access proyect to share in a group; in 2 layers; a)
data, b) forms, reports, etc.

In this proyect I can't use a normal SQL Server, etc; and I think thats the
better solution it's an Access ADP proyect linked to SDF file of SQL Server
Compact Edition (this it's free, isn't it?)

Thank you very much

Sylvain Lafontaine

No, you cannot link to it from ADP and by the way, using a SDF file is
possibly a bad choice in your case as the Compact Edition is the only
edition that is not multi-user and it also doesn't all the capabilities of
the other editions in term of programmability.

Of course, by linking, I mean in the normal way for an ADP project. You
could always use ADO to link to it and use unbound forms (not linked to a
SQL-Server) to fill your forms reports but if you want to go this way (using
unbound forms), using an MDB or ACCDB database file or the .NET platform are
possibly better ideas.

What you need to use if you want to use ADP with a free edition of
SQL-Server is the SQL-Server Express Edition. In the past, the Express
editions were limited to database of size of 4GB but with the latest version
release, the SQL-Server 2008 R2 (notice the R2), the limit per database is
now 10GB.

Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:
Independent consultant and remote programming for Access and SQL-Server

Thank you very much for your response; I will think in your alternatives.

This it's the need:

- A friend have an MDB with so much forms, reports, modules, and tables.
This access file it's only for one user (not it's needed muti-user); but now
another people wants his MDB; to use it in a similar proyect, but with
different data.

- Now, 4 or 5 person use this MDB, but not it easy to share the updates of
reports, code, etc (Data size of tables are so big to share by email).

- Then, I think the better way to avoid this problem it's putting data OUT
of the MDB. The better solution it's and ADP Proyect linked to SQL Server
Express, but it's more complex for these home users. Then I remember the SDF
File; and think as most easy solution (runtime installer not it's big and
it's transparent for the user).

But I just try with Access 2010 beta, and continue impossible the link with
this kind of SQL Server CE.

Now, with all needs; what it's your oppinion about the better solution?

Thank you very much

Sylvain Lafontaine


First, when you have a question, you should always post this question
instead of posting one of your possible solutions. In your case, going with
ADP and SQL-Server or with a SDF file are definitely not the answer to your

What you need is to split your database file into two files: the frontend
(FE) with the forms/modules and the backend (BE) with the tables (data) and
link these two with ODBC Linked Tables. After that, all you have to do is
to share the frontend; unless you make modifications to the structure of the
tables (the backend) but usually, these are much less frequent and can be
done easily done with some code or manually.

On the internet and in the other newsgroups about Access, you will find tons
of information about splitting a MDB or ACCDB database files and the use of
ODBC Linked Tables.

Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:
Independent consultant and remote programming for Access and SQL-Server

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question