Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Help with general knowledge of database solutions

    Hi, I wonder if anyone can explain a few things I've been wondering about:

    I may be designing a database solution for a company to keep track of their customers and sales and so on - nothing too fancy. My skills are mostly web-based (ASP, ASP.NET) although I have a good deal of experience making Access databases for small companies, but very little with bigger corporations.

    If I wanted to use MS SQL server for example, how does it work?
    - How do I make the front end? Should I learn how to use Visual Studio? I'd rather stick to Access for the front end if at all possible, but I'd have thought it's more expensive for the client.
    - How can I allow multiple locations to access the data? Is it simply a case of opening up the firewalls to the SQL Server port and Bob's your uncle? Any performance issues with this (if it's a broadband connection or even dial-up)?
    - If I set up a MSSQL server, will it need constant maintenance, or will I be able to just leave it with the client & do the odd security check once a month? Or is it better to go for a hosted solution?
    - What about other databases such as MySQL, Oracle & Sybase? How do their front-ends work?
    - Basically, I suppose what I'm asking is what do I need to learn to make the break from a split Access database to something bigger?

    Many thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    MS-Access has two major parts, which are usually inextricably linked in the users mind. This generally causes massive mental trauma when the user tries to pull these things apart, which is abolutely crucial as they try to handle larger and more complex projects.

    One part is the Jet database engine. It holds the tables, queries (aka views) and the related database objects. This is the part that the average user is most likely to outgrow first.

    The other part is the Access application (the GUI stuff) and the programming tools (which are really VBA, Visual Basic for Applications). This is the part that the developer is most likely to outgrow first.

    As you build progressively larger and more complex applications, you'll certainly outgrow one part of MS-Access or the other. In the worst case, you'll outgrow them both at about the same time, which means that you'll have to leave the comfort of the tools you've grown accustomed to using in one swell foop instead of one piece at a time. This can be really hard on some folks.

    The pieces are designed as separate things. Microsoft tries very hard to keep them two separate things. If you've paid any attention at all to the design issues that Microsoft harps on, you shouldn't have a huge problem separating the pieces from one another and migrating the parts of your application that need "bigger guns" to use them.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    <<
    ...My skills are mostly web-based (ASP, ASP.NET) although I have a good deal of experience making Access databases for small companies, but very little with bigger corporations.

    If I wanted to use MS SQL server for example, how does it work?
    - How do I make the front end?...
    >>

    ASP pages that were set up to work for an Access database would still work with a SQL Server database possibly with just some minor tweaking involved in the SQL strings. And of course the connection string would be different unless it were just a DSN:

    Able Consulting - ADO Connection String Samples
    http://www.able-consulting.com/ADO_Conn.htm
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jun 2004
    Posts
    2
    Thanks for the advice, just the kind of thing I was looking for. At the moment I use a bit of VBA on my Access forms, but I know (from ASP and .NET) how to use SQL exclusively to update & create records. I guess it's a question of making the front-end leap to VB - I can't imagine it will take too long to learn about stored procedures and triggers etc.

  5. #5
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    You sound pretty bright, so no, it shouldn't take you too long to learn about stored procedures and triggers and the like, but be prepared to take some time to learn about how much the scale up from Access can improve your queries and data integrity.

    Access, in other words, is limited in the complexity of the queries you can use. I've had to "dumb down" several procedures created for a fully SQL-92 compliant DB to make them work in Access, at a performance cost.

    I know you said this application you were working on wasn't difficult or complex, but if you have a chance, explore the great new capabilities you will have at your hands.

    In response to your other questions, you may want to consider MySQL as an option. It is free, and very robust. I find the interface difficult to work with, I much prefer SQL Server when the client can afford it.

Posting Permissions

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