Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    5

    Unanswered: 2gig limit migrate to SQL backend?

    I am a self-taught Access user. I've built a database for a client and will quickly run into the 2 gig limit. Can I use MySQL on my desktop to access the data stored on a network and still have people using Access as the front end? Are there any issues with the Access front end being linked to GIS data? Are there any books to walk me through these steps?

    Thanks.

    Marybeth

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as you use a server (SQL Server, MySQL...) as the container of the tables for your application, there should not be any problem if you youse the Front-End/Back-End model: the linked tables of your application reside on the server instead of residing in a MDB file. You just have to be sure that each table has a primary key or, more precisely an identity column (a value that is unique for each row into a table).

    It will become more complex if you intend to use the functionalities of such a server (stored procedures, computed columns, etc.).

    Of course, a server requires a minimum of maintenance (backup, etc.) but that can easily be learned.
    Have a nice day!

  3. #3
    Join Date
    Jun 2006
    Posts
    5
    Any suggestions on reading material. I have never worked in SQL except for what is in Access.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have first to precise which server you plan to use; from SQL Server to MySQL (among several others) the needed documentation may vary.
    Have a nice day!

  5. #5
    Join Date
    Jun 2006
    Posts
    5
    Probably MySQL since we can't afford the full version. There's a Microsoft product Desktop engine. Do you know anything about this?

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'd recommend SQL Server (the free Express version if cost is a factor). I believe though that there is a 2 gig limit on the free edition. You can though, pick up a SQL Server 2000 version really cheap if cost is a factor (finding it though may be challenging). I actually like SQL Server 2000 versus 2005 or 2008.

    I usually create all my tables/relationships/primary keys/etc.. in MSAccess tables first. The upsizing wizard to upsize the tables to SQL Server really works great! (you create the ODBC DSN during the upsizing).

    Then it's a matter of establishing an ODBC DSN on each of the user's computers (see the code bank for examples to automatically create ODBC DSN's in your MSAccess app.) Then you don't have to create the ODBC DSN on everyone's machine (ie. the code will automatically create it when the user opens the mdb.) Note: The ODBC DSN name to connect to the SQL Server db MUST all be named exactly the same. That's why I like the code in the code bank to automatically create it. If even 1 ODBC DSN on a user's computer is named slightly different, you'll have issues.

    I might suggest a basic class on SQL Server. SQL Server is really a nice product to work with and well worth the cost. Especially if you're working with data over the 2 gig limit. I use it all the time for my backend tables.
    Last edited by pkstormy; 03-26-10 at 12:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you don't need a database of more than 4 GB, you should consider SQL Server 2005 Express Edition or SQL Server 2008 Express Edition. The integration with Access is reallly easy and so is the migration of the databases from Access to SQL Server.

    I agree with pkstormy, however what makes me recommend 2005 or 2008 Express Edition over MSDE (which is the 2000 version of the same product) is that I'm not sure MSDE will still be supported in the near future (it's not supported under Windows Vista and Windows 7) and also because it's slightly more limited than the newer versions:

    -database up to 2 GB in 2000, up to 4 GB in 2005 and 2008
    -5 concurrent connections in 2000, no limit in 2005 and 2008
    -No specific user interface in 2000, SQL Server Management Studio Express in 2005 and 2008

    But:

    - Max. 2 GB RAM used in 2000, Max. 1 GB RAM in 2005 and 2008
    -1 to 2 CPU in 2000, only 1 CPU in 2005, 2008

    The upgrade from Express Edition to another (not free) edition is easy, should you overcome these limitations later.
    Have a nice day!

Posting Permissions

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