Microsoft Access & Microsoft SQL.

[john527]

Questions / Help :

What I would like to know about Microsoft SQL Server & Access 2003 :
I am a novice at creating my own database.
I would like to use Microsoft SQL Server & Access 2003 .
I have in the last 3 months read all I can about creating a database in Microsoft Access 2003 .
Now I need to learn about Microsoft SQL Server, and it is very different from Access 2003.
I have purchase many bussiness applications over the last 5 years, and fount that over software are to rigid, as I need something very specifil to my bussness.
If you intend to reply, please be very specific & in plain English.

Can someone help me ?


1 Can Microsoft SQL Server 2000 work with Microsoft access 2003.
a. Is this do-able .

2 Can Mysql also work with Microsoft access 2003

3 What is the difference between Microsoft SQL Server & Mysql.

4 And any other information that may help me.


Thank You For Your Time.
John Calcitrai.

 

[FoFa]

1 - YES, it is designed to work with SQL server. A MSAcces Project uses a SQL Server DB as it's backend DB instead of Access tables. Basically a Applicaiton front end is all.
2 - Any ODBC compliant DB should work with MSAccess, however they can be slower than Sql Server which is Designed to work with Access.
3 - Sql Server and MySql are both DB Engines that run independently of Access. Typically on their own server for performance reasons.
4 - If you don't need the extra power of the big DB engines, stick with Access only. Access is remarkable in how much data it can handle. I find it more limited on concurrent users and network traffic depending on your setup. Security is OK, but not near as tight as say Sql Server. Using an external DB like Sql server or Mysql seems to add extra complexity to what ever you are doing.

 

[Pat Hartman]

1. SQL Server 2005 is the most recent version. But yes, the 2000 version will work with A2003. Even though .adp's are an option, I would strongly recommend against them since support for them will likely be dropped with the version of Access following A2007. Stick with the .mdb format. You can create your application totally with Access and then if you decide that you need to upsize due to the amount of data or the number of concurrent users, you can use the upsizing wizard to create your SQL Server database and link to it.
3. MySQL is free and SQL Server is pricey. Many people swear by MySQL but it does have some problems when used from Access. Search here and the web to find posts on the topic.

 

[shadow9449]

3. MySQL is free and SQL Server is pricey.

Microsoft's SQL Server 2005 Express Edition is free and will meet the needs of many people requiring a better database format than Jet who do not need the more senior (and pricey) editions of SQL Server.

SHADOW

 

[Pat Hartman]

The Express edition limits the number of active threads, same as the MSDE available since A2K. That significantly slows down access if more than a couple of people are attempting concurrent access. I think the limit is 8 threads but don't quote me. Each user can have multiple threads in use depending on how many queries/forms/reports he has open at one time.

 

[shadow9449]

The Express edition limits the number of active threads, same as the MSDE available since A2K. That significantly slows down access if more than a couple of people are attempting concurrent access. I think the limit is 8 threads but don't quote me. Each user can have multiple threads in use depending on how many queries/forms/reports he has open at one time.

AFAIK, the 8 user limit was imposed by the workload governor of MSDE, and no longer applies in SQL Server Express. I would have to dig through the specs that MS claims on it, but this does not seem to be listed among the limitations.

SHADOW

 

[FoFa]

1 CPU: SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. This limitation prevents the use of parallel query execution in SQL Server Express.

1 GB RAM: The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, etc. is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1GB for the buffer pool. This limitation prevents the use of Address Windowing Extensions (AWE) with SQL Server Express.

4 GB Database Size: The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Applications planning to use SQL Server Express are recommended to keep these changes in mind when designing their applications.

In the new Express Edition the workload governor has been removed, and the database size limit of 2GB in MSDE has been raised to 4GB. Surprisingly, Express Edition limits you to using a single processor and 1GB of RAM, where its predecessor allowed two CPUs and 2GB RAM. The limit to one processor in Express Edition means that parallel queries can't be run.

 

[shadow9449]

In the new Express Edition the workload governor has been removed, and the database size limit of 2GB in MSDE has been raised to 4GB. Surprisingly, Express Edition limits you to using a single processor and 1GB of RAM, where its predecessor allowed two CPUs and 2GB RAM. The limit to one processor in Express Edition means that parallel queries can't be run.

I haven't yet found people with enough experience with SQL Server Express to determine how many users they would allow on an application before worrying. The cutoff for Access using Jet is probably somewhere between 10 and 20 (depending on the design and complexity of the application). I'd like to know if SQL Server Express can comfortably handle 30-50.

SHADOW

 

[FoFa]

I haven't yet found people with enough experience with SQL Server Express to determine how many users they would allow on an application before worrying. The cutoff for Access using Jet is probably somewhere between 10 and 20 (depending on the design and complexity of the application). I'd like to know if SQL Server Express can comfortably handle 30-50.
SHADOW

Not having used it I can't say for sure, but a look at the limitationa would suggest the memory and CPU limit would throttle the performance level down. So I guess it most likely would handle that many, or more, just depends on what the server is doing. I think work load will be the most limiting factor if those specs hold to be true.

 

[shadow9449]

Not having used it I can't say for sure, but a look at the limitationa would suggest the memory and CPU limit would throttle the performance level down. So I guess it most likely would handle that many, or more, just depends on what the server is doing. I think work load will be the most limiting factor if those specs hold to be true.

A hefty server should not have a problem...