SQL Server Express (2003)

Topic

New Reply

I see that this is avaiable as a free download. Is this usable with an access front end and is there any documentation anywhere on doing that?
Thanks

Reply | Quote

Replies

My impression is that this is not yet possible. In Visual Studio 2005 Express Editions: SQL Server Express Edition September CTP, Microsoft states[indent]

---

To connect to and manage a SQL Server Express database, you can use any of the following options:

• … SQL Server Management Studio …
• … sqlcmd …
• … any Visual Studio Express Edition …
  [/list]

  ---

  [/indent]But I would guess that Access 12 will be able to connect to SQL Server Express, and perhaps earlier versions too.

Reply | Quote

We’re about to begin working with it, and will follow up with what we find, but based on what we’ve been able to deduce from documentation and yesterday’s launch info, you should be able to create an ODBC data source and connect with it that way. It remains to be seen whether you can connect to it with an ADP – almost certainly you will be able to in Office 12, as it will replace the “despised by some” MSDE.

Reply | Quote

Where can you download it and the ODBC drivers? I am running a database with 5 users and 70,000 main records and each one with attached fields. Is it worth the increase in speed?
Thanks for your input.
Zave

Reply | Quote

The download for SQL Server Express can be found at http://msdn.microsoft.com/vstudio/express/sql/default.aspx%5B/url%5D, which also includes some links to basic information about the product. You might also want to look at http://msdn.microsoft.com/sql/express/%5B/url%5D which compares SQL Server Express with the full version and also with Jet.

It isn’t clear however that you would gain a speed increase just by adopting SQL Server – and with Access 2003 you do have the MSDE already. The version that comes with 2002 and 2003 actually works fairly well for a small number of users and a modest size database. If you are having performance issues with your current configuration, you may want to look at optimizing it before you switch to SQL Server.

Reply | Quote

Thanks for the links. Where is a good place for a step by step way to learn how to upgrade a BE to MSDE?

Reply | Quote

See for example Choosing and Using MSDE 2000 as the Database Engine for Your Application. It contains many links, such as Using Microsoft Access 2002 with MSDE 2000. (Should be the same for Access 2003)

Reply | Quote

Thanks Hans and Wendell for all the insight

Reply | Quote

Hans has given you a couple of excellent resources – note however that they focus on using an Access Project (ADP) front-end rather than your existing MDB forms, reports and queries. The option we use almost exclusively is to upsize to SQL Server as described in the second reference, but then create an ODBC connection to that database using the standard SQL Server drivers. The MSDE lacks the nice tools that you have manage the maintenance (backup, repair, etc.) tasks, but with a simple ADP you can use the basic facilities that Access provides to do backups and create stored procedures and the like.

Reply | Quote