SQL Express and MS Access

[kye]

I am developing an MS Access database system and would like to put it on our network (using the Upsizing Wizard) with the tables in an SQL environment. This will be a very low traffic DB, but the security and multi-user features would be really nice to have. Also, it will eventually have some parts that will be getting added to our web page. We don't have an SQL server on our network, and I am getting major resistance from the Network Admin saying it is very expensive and very complicated to set up SQL. I have found the free version (SQL Server Express), but it seems to be linked to Visual Studio, which we also don't have. Soooo -
Question #1; Does anyone know if SQL Server Express can be used directly with MS Access, or would I have to purchase Visual Studio?
-- and --
Question #2; Does anyone know what the "Express" part of SQL Server Express involves? What - generally - would I NOT be able to do with this compared to a regular SQL server setup?

Thanks a billion!!!

 

[pono1]

Question #1; Does anyone know if SQL Server Express can be used directly with MS Access, or would I have to purchase Visual Studio?

Not sure... SQL Server Express is another one of Microsoft's loss leaders, a free, scaled-down version of SQL Server... As I'm sure you know, Microsoft wants you to use SQL Server Express and ultimately upgrade to a pay version. I suggest you download and install SQL Server Express on your desktop and then work with it and then get back with your net admin. (You can, of course, also download Visual Studio Express --- another loss leader.)

Question #2; Does anyone know what the "Express" part of SQL Server Express involves? What - generally - would I NOT be able to do with this compared to a regular SQL server setup?

Here's comparison info.

Regards,
Tim

 

[kye]

Thanks! I'll let you all know it goes.

Kye

 

[drPavlovic]

..pretty tough

.. I have tried to use Access XP with SQL Express 2005, and, after 3 days, and a lot of read article on the web, where Microsoft even doesnt support Visual Studio 2003 with Express 2005, I decided to go back on : Access 2003 + SQL 20005 .

It goes much faster, you can see all object from SQL 2000 in MS Access, write new, edit them, whereas, with SQL 2005, you can just see object in database window, but cannot open them , neither create or write new ones.
So .. my suggestion is .. forget it

 

[kye]

thanks for the feedback

Thanks for all the info/opinions. I now have things up and running on SQL Express version - in a basic kind of way, and the boss has purchased the full version of SQL Server, which should be another "FUN" little adventure - getting things moved over. When people started seeing all the cool stuff we could do in a multi-user environment it was a no-brainer! The free Express version thing is a good way to get things rolling!

Thanks again!
Kye

 

[Freshman]

Re: ..pretty tough

.. I have tried to use Access XP with SQL Express 2005, and, after 3 days, and a lot of read article on the web, where Microsoft even doesnt support Visual Studio 2003 with Express 2005, I decided to go back on : Access 2003 + SQL 20005 .

It goes much faster, you can see all object from SQL 2000 in MS Access, write new, edit them, whereas, with SQL 2005, you can just see object in database window, but cannot open them , neither create or write new ones.
So .. my suggestion is .. forget it

Yes I know it's an old post, but I want to know if the 20005 was a typo, so should I try and google for SQL 20005 or 2005 or 2000?

A bit confusing.

I don't want to venture into SQL 2008 if I'm gonna pick up the same problems.
I would rather use Access 2003 along with an old but stable (easy to use) SQL version.
Please advice.
Thanks all

 

[Lightwave]

Access 2003 works no problem with SQL 2005 it does however need an additional managment download called SQL Server Management Studio. Fell into the same trap myself. Don't know how you interact with SQL 2005 without SSMS I suspect its command line without it...

SQL Server wasn't really designed with your granny in mind.

 

[Freshman]

SQL Server wasn't really designed with your granny in mind.

Hee-hee - I agree.

I was thinking maybe (just maybe - need to confirm that) Access 2003 will work almost better with Server 2000 than with Server 2005.

I'm almost hoping for a "simple" solution like that (meaning going backward in Server versions).
Almost like Access 2003 is designed for the Developer in mind probably more than 2007 and 2010 which is more End-User focussed (my feeling anyway).

 

[Galaxiom]

Access works fine with Express. I would use the latest version.

Express has a limit of 1GB of RAM usage and a limit to the databse size. It is 4GB for 2005 and 2008 and 10GB with 2008-R2.

Watch out for the datatypes with SQL Server 2008. The "Date/Time" type is not supported by Access until 2010. You have to use "Tiny DateTime" on older versions of Access.

You can interact with SQL Server directly using SQL to do everything. The management studio is a handy graphic interface though.

Beware the licencing in the full version of SQL Server. Not only do you require the licence for the server but also Client Access Licences. Each user who connects either directly or indirectly to the server must have a CAL. (There are also machine CALs as an alternative.)

This includes anyone who receives data from the database even if that is via a web server that provides dynamic pages (but not things like reports dumped into Excel)

If you cannot control the connections such as on a public website you need a Per-processor connection license. This is also the best alternative if you have many users.

In Australia User CALs cost over $200 each. The Per-processor license is over $12K.

 

[Galaxiom]

I was thinking maybe (just maybe - need to confirm that) Access 2003 will work almost better with Server 2000 than with Server 2005.

I would certainly not use SQL Server 2000. It is well out of date. The connection between Access and SQL server is either via ODBC or OLEDB. This takes care of the compatibility and there is nothing to gain with an old version. You simply can't use everything in the new version such as the new DateTime field type as mention above.

Almost like Access 2003 is designed for the Developer in mind probably more than 2007 and 2010 which is more End-User focussed (my feeling anyway).

I disagee entirely. The developer interface on 2007 is vastly superior to 2003. It just takes a while to get used to it and learn its advantages. I would never choose to go back.

 

[Lightwave]

I disagee entirely. The developer interface on 2007 is vastly superior to 2003. It just takes a while to get used to it and learn its advantages. I would never choose to go back.

This is good to hear I'm between 03 and 07 and feeling a bit hamstringed when designing within it.

 

[Freshman]

Thanks for all the info Galax!!