0

I am looking to create an Access application with occasionally connected online sync functionality. The idea is to use SQL Server Compact as a backend locally, then use a program to sync that to a server-side SQL Server. I can't find enough definitive evidence that you can't do that. Can you use SQL Server Compact as a backend for an MS Access application?

WestAce
  • 860
  • 3
  • 9
  • 23

3 Answers3

2

Your best option here is probably Sql Server LocalDB.

LocalDB uses the same data types and formats as full Sql Server, and the engine even relies on libraries built for full Sql Server. But unlike regular Sql Server Express Edition, it does not run as a service, even when your main app is not running, and the installation deployment story is a bit easier, too.

On the Access side, you can setup an ODBC connection to create Linked Tables for use in your application, very similar to how you would with Sql Server Express Edition.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

No, it is not possible, you should use SQL Server Express - what prevents you from doing that?

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • We are going to install this database on 30 laptops belonging to our field salesmen. I had the feeling that using SQL Server Compact, it would be easier to manage installation and updates. I could be wrong... – WestAce Feb 25 '17 at 15:31
  • 1
    If you can refrain from the native replication option of SQL Server, you could consider [SQL Server 2016 Express LocalDB](https://msdn.microsoft.com/en-us/library/hh510202.aspx). It's a silent install. – Gustav Feb 25 '17 at 16:48
  • 1
    @WestAce You are right about the deployment issue, but the install is lightweight and can be made silent quite easily - does require admin acces, however (unlike SQL Compact) – ErikEJ Feb 26 '17 at 07:25
  • 1
    It's not just deployment. Express Edition is still a server engine that runs all the time, even when the Access app is closed, and therefore is using system resources all the time, including as much RAM as it's able to get for that database. IMO it's just not a good idea to use Sql Server Express for desktop application storage. It's more suited to workgroup-sized databases, or when the database is the reason the work station exists at all. – Joel Coehoorn Mar 01 '17 at 16:25
1

You can do that via ADO as described here.

But as you wish to replicate the data, it would be much simpler to use SQL Server Express as it can be set up right away as a replication subscriber to a database published by your server-side SQL Server.

Community
  • 1
  • 1
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    The ADO approach has a number of limitations: no support for blob types, no simple data binding – ErikEJ Feb 25 '17 at 16:04
  • This suggestion makes no sense and does not work for access. The idea that you can create some ADO record set has ANY RELATIONSHIP to being able to run an Access application using compact edition is ABSOLUTE BRAIN DEAD GALACTICALLY CRAZY AND INCORRECT advice. You simply cannot run nor use a typical access application using the compact edition of SQL with access since there is no ODBC driver. This means reports and any bound form (which 99.99% of access applications assume and require) will not work. Being able to create a ADO recordset has near ZERO relevance to using compact SQL with Acces – Albert D. Kallal Feb 25 '17 at 20:04