Results 1 to 6 of 6

Thread: Access vs. SQL

  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Exclamation Unanswered: Access vs. SQL

    It has been recently requested that I publish a survey which will be accessed by possibly up to 3000 individuals. These individuals will be requested to fill out an online Intranet survey consisting of 50 questions all of which will be captured in an Access DB file.

    Are there any Access issues that I should be aware of when dealing with such a large number of contributors? Would SQL be a better solution in capturing this data?

    Any information that can be provided would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SQL is a language, Access is an application. Access uses SQL.

    MySQL is another platform, if you can possibly use MySQL, I think that would be the better choice.

    Large numbers of concurrent users don't generally play nice in an access database, particularly a web oriented access db.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I think when cstapp says SQL He means SQL Server, Which altough costs far more is probably another good option (using ASP or ASP.NET as a front end)

  4. #4
    Join Date
    Dec 2004
    Posts
    2

    Clarification

    To clarify, I have am referrring to Access 2003 and SQL Server 2000.

    Cost is not an issue. I have access to both solutions but wanted to get some factual information as to why I should used one server option over the other?

    I've read where Access issues can occur when the number of concurrent users reachs a certain number? I've used this same Access DB file in a similiar situation but with only 450 contributors. With the siginificant increase (3000) in contributors I wanted to get as much information in order make the correct DB selection prior to development.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you have access to SQL Server, I would recommend going with an adp/ade.

    SQL Server is designed to handle this size project and much greater if necessary. By using Access as a front end to sql server, you retain Access' rapid development capability (its primary strength in my opinion), as well as MSSQL's back end fexibility and control.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Access with multiple users

    I would recommend SQL Server. Especially if money's not a problem. MySQL may be fast, but SQL Server has a lot more to it (ie. configuring Tranlog backups every hour). We use linked tables in the Access application. The only thing you have to worry about is ODBC and making sure the DSN names are all consistent.

    I've found that more than 3-4 users using the same Access tables at the same time can cause some problems unless you program your application with unbound forms (i.e write a lot of code to write/retrieve/update records in the tables).

    If you do use SQL Server, below is a neat little app which will spawn a database. This helps with certain situations (ie. when you have to do make tables, etc.):

    I developed a spawning technique to copy an Access file with the users name attached to it. I had a little help writing the code and it works fantastic.
    We use MDE's and a "spawning" technique which works quite well. Below is a sample vbs script for spawning a database with the users name added to the MDE file. This makes it easy for us to copy new code to our shared working drive without having all the users exit out and works very well. It's also another way to find out who's using the database as you will see the database name with the user name at the end and an ldb file associated with it.

    Here's the vbs code to "spawn" a database called "WhosIn2004.mde" located in \\SQLServer\Databases\Databases\WhosIn\WhosIn2004. mde (UNC Name). Change the LUName to the location of your Access file.

    Create a vbs script in notepad (saving it with a .vbs extension) using the following code, then make the vbs script your central file to run for all users:

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing

    LUName = "\\SQLServer\Databases\Databases\WhosIn\WhosIn2004 . mde"
    'Call Shell(LUName, 1)

    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")

    objShell.Run "MSAccess.exe " & LUName, 1

    Set objShell = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •