Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Access size advise needed (2002)

    Hi!

    I need an opinion or information on at what point does one decide to build a database in SQL or ?.net vs Access. I'm assuming its a size related concept as in the amount of data stored in the Access Database. I have a user who will be importing about 60,000 rows of sales data on a monthly basis. 4 other tables in database will grow accordingly but will be by sums of the data imported so they will grow by about 2000 rows a month. The database is used to generate invoices, log payments and run a variety of sales related reports. One of the reports looks at the net_sales by line item on a monthly basis so the database does in fact need to store and add to the 60000 rows per month. Right now its working fine but they just started importing the data. At what point will they need to look at another type database, if at all. I've considered archiving information but would like to have an idea of when we will need to do that.

    Thanks,
    Leesha

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access size advise needed (2002)

    It is hard to give a definitive answer, because there is no clear-cut dividing line. It depends on the application, on the number of tables, number of records per table, how well the tables are indexed, what you need to do with the data, etc.

    My gut feeling is that 60,000 records per month will quickly build up to the point that you will start seeing some performance issues with Access. Assuming you are going to just keep accumulating data and not purge it for a least a couple of years, you are going to have something like 2 million records in that one table alone. I think you should start considering an upgrade to SQL server. There is no urgency, but I'm pretty sure you will need it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    Hi Mark,

    I had a feeling that would be the advise but am relieved that there is no urgency as I'm just stating to feel confident with Access thanks to this forum and have never dabbled with SQL server. Next question, is it even remotely similar to Access or is it a major learning experience and total different animal to learn.

    Thanks,
    Leesha

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access size advise needed (2002)

    You can still use Access as the frontend to an SQL database. You just link to the tables in the SQL database much like you link to the tables in a backend Access db (which I asssume you already have). So you could essentially use your same Access database with no changes other than any linking code! At that point, you are not really using the power of SQL Server, however, as Access itself is still doing all the processing, while SQL Server is just managing the tables. But it is an important first step.

    You won't really see a difference in performance until you start shifting the processing from Access to SQL server. This means using pass-thru queries, etc.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    It is remotely similar to Access, but it uses a different version of SQL and it has much more rigid ideas about what you can and can't do. It is also far more powerful in its ability to manipulate and control data. SQL Server is not an interface, only a database, so you still need Access or some other product to build the user interface for an application that uses SQL Server as a data store.
    Charlotte

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    OMG this is too good to be true! If I understand you both correctly, the Access database is still good and is actually the brains behind the database and all I do is link the Access Database to the table or tables in SQL? With regard to Charlottes comment re the queries and how rigid SQL is, will the queries I've written in Access still run as long as the table in SQL is named the same?

    Leesha

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access size advise needed (2002)

    Some caveats. I'm assuming you already have a split database schema (frontend/backend). In that case, linking to SQL tables (that have same names as your Access tables) will enable you to run with your existing Access frontend. All your queries will work, etc.

    Again, remember that just doing this will NOT cure your potential performance issues! In this scenario, the processing is still be done on the Access side, SQL Server is merely managing the tables. To do that, you must start using techniques to shift the processing to SQL Server. Generally, as you identify reports (or whatever) that are very slow, you can try using these techniques (such as pass-through queries). This is where you need to know more about writing queries for SQL Server (as Charlotte mentioned).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access size advise needed (2002)

    Adding to what Mark and Charlotte have said, we recommend SQL Server as a back-end whenever you are working with large recordsets, or if you have more than 10 to 20 users. In fact, most of our development is done that way, and we nearly always link to tables using ODBC.

    Some fine points in the process:<UL><LI>To connect to the SQL Server database, you will need to create an ODBC Data Source for each workstation that uses the database, and in general we recommend using Integrated security, rather than using the standard security.<LI>You will typically have to change the name of each SQL Server table one time - linking to SQL Server tables appends the name of the database owner (usually dbo_) to each table. Once you rename them, your queries, forms and reports should generally work.<LI>Simple queries will usually work just fine, maybe even faster in some cases, but there are some definite gotchas. Don't try to join SQL Server tables and Access tables - the performance will be woeful if you do. Also make sure all of your SQL tables have indexes - otherwise Access won't let you update.<LI>One neat trick is to create views in SQL Server and then attach to them as tables. They won't be updateable in general unless you index the view in SQL Server, but you can often get substantial performance gains by doing that.<LI>We generally work in the mode where we start with the simple attached table design, and then test for performance issues, and fix those situations where performance lags.[/list]There are lots of nitty gritty details when you start using pass-through queries, ODBC direct queries, and things like stored procedures and triggers, but the additional robustness is well worth the learning curve in my view. If you run into challenges, we'll do our best to assist you.
    Wendell

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access size advise needed (2002)

    Wendell,

    You seem to have a pretty good handle on SQL server, so I wonder if I could ask you a question about indexing?

    In Access, when you create a relationship with enforced referential integrity, Access creates a hidden index on the child table. This means you don't have to manually create an index on the linking field in the child table. You can actually see the index if you cycle thru the index collection, and my understanding is that Rushmore will use the index in its optimization.

    But about with SQL Server? If I create such a relationship between the parent/child, should I still create an index on the child table for the linking field?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    Hi,

    Big gulp.......what am I getting myself into! OK, the tables are split, however there are two sets of tables. The tables that are in each version of the database that are stored on the user's computer (mainly temporary tables or definition tables) and the permanent tables that are saved on the server. You mentioned that I should try to join SQL Server tables and Access tables. Does this mean that I should have two sets of split/backend tables? The ones that are saved to the server and the ones that are on each users workstation?

    Now for two questions that I'm sure will seem quite silly. Where can I get some understandable information on ODBC queries, pass through queries etc. and what version of the SQL server software should I obtain? Will the user also need to have this installed on their computers?

    Thanks,
    Leesha

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access size advise needed (2002)

    As I understand it (and I haven't researched it to any degree to verify it), Access get's index information from SQL Server at the time the table is linked (or refreshed). In any event, indexing a SQL Server table in Access wouldn't make much sense, as Access has no clue what page SQL has stored the data on. So the index has to be in SQL Server, and you can display those in SQL Server using Enterprise Manager. I rather suspect that SQL Server does create an index when you set up referential integrity between tables, but I always choose to make sure that the right indexes exist. That should be easy to check in any event. Hope this helps.
    Wendell

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access size advise needed (2002)

    Let me answer the last set of questions first. Understandable info on pass through and ODBC queries is rather scarce - but take a look at Alison Balters' Guide to Mastering Access 2002 Enterprise Development (Volume 2), and at Microsoft Access Developer's Guide to SQL Server. If you do a search on Amazon for Access SQL Server you will find others - in particular the one by Rick Dobson is likely to be good. As to the version of SQL Server, I would use SQL Server 2000 - you get the MSDE version of that with Access 2002, so you can build something using that, and then migrate it to the real deal. Also be aware that a new version of SQL Server (2005) is due out late this year - but in your case I don't perceive any significant advantages in starting with it. And no, the SQL Server database engine runs only on a server - you use the ODBC Data Source to link to the server.

    As to the tables - what I said was don't try to join SQL Server and Access tables in a query - the performance is usually awful. It is OK to create temporary tables in the front-end that are Access tables - that's often desirable for reporting or analysis purposes. But in general you want the real data stored only in one place and that place is on the server. Hope that helps. I'll be AWOL for a couple of days traveling in the mountains, but feel free to ask more questions - others have SQL Server knowledge as well.
    Wendell

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access size advise needed (2002)

    Enterprise manager doesn't show the "relationship" indexes; for that matter, Access also hides these indexes when in table design mode for regular Access tables, you have to manually cycle through the index collection to see them (they are marked as having a foreign key). I don't know how to do this in SQL server. I suspect they may be there; however, apparently Access doesn't see them (which means it can't use them). I may follow your lead and create them manually.

    Thanks.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    Hi Wendell,

    Thanks for the tips on the books. I will get both!

    Have a good time in the mountains,
    Leesha

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access size advise needed (2002)

    One more question ............ I am using Access 2002 that came as part of OfficeXP. I did a search of the computer to find SQL Server 2002 and did not come up with anything. Will I need to purchase the individual verson of Access 2002 to get or SQL Server 2002 or is it part of office XP and I'm just missing it?

    Thanks,
    Leesha

Page 1 of 3 123 LastLast

Posting Permissions

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