Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opinions required please! (Access/SQL Server)

    OK, firstly, apologies if this has been posted to the wrong forum...

    I am an experienced Office/Access/VBA developer, and have recently started working with SQL Server too. I have been given an oppurtunity to tender for my first contract, and seek the opinions of anyone who feels they would like to contribute.

    The potential customer runs a screen printing facility, and currently enters all data regarding the production into journals.... Now he wants to go to a database...

    I have carefully considered his requirements which are as follows.. Max 5 users.. (2 in production, 1 entering orders, invoicing etc, and the Director running reports). Approx 500 - 700 new rows per week to be appended to the main table. He is running already running an NT domain, with Exchange Server. Looking at possibly uploading certain order status information to a secure part of his 3rd party hosted website, and maybe importing oders from, and sending confirmations to, by email to his customers automatically.

    So my question?? I am happy that this should be a client/server structure, with a back end containing tables etc, and front ends on each workstation. SQL server with Access front ends would seem to hold the following benefits: low server app maintenace req. by customer, reliable platform, designed for multi user from the outset...

    Would SQL Server be overkill in this situation??? With only 5 users, and a relatively small amount of new data being added I also think Access as a back end would be ok too...

    One last question: His hardware IT contractor has advised him that his current server is more than up to the extra work and that no new additional server would be necessary (i agree), but i presume to avoid the risk of breaking his mail system, i should get his current contractor to install which ever application we go with......

    Many thanks to all who read this..

    Peter

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    Hi Peter,

    I'm not an expert with SQL Server, but I was also recently in a situation where I had to advise a client on which direction to pursue with a new data management system. My thought is that the extra stability and reliability of SQL Server makes it a good fit for my case and yours. Since the client's current hardware will be adequate, it seems to be almost a no-brainer. Not to mention the ease of maintenance with SQL Server... Plus, by investing in a great software product now, they will be able to have more SQL Server apps in the future that can easily scale to meet their growing business needs (forgive me if this sounds like a Microsoft commercial).

    Not to get too far off track here, but I have heard or read rumors that the Access file format will not be around much longer. Microsoft seems to be pushing everything into the SQL Server format (or MSDE for smaller situations).

    Your client/server plan sounds like a winner. I hope things go well with development!

    Best,

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

    Re: Opinions required please! (Access/SQL Server)

    One thing to keep in mind is that SQL Server requires an administrator, someone to tend to its needs at the server/IT level, not just a user with extra permissions. There's a cost issue as well.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    Thank you to both of you for your valued opinions.... It looks like SQL Server is the way to go...... One question: With an MDB, i can build it at home, copy to CD or my laptop, and then copy and install on to the client's system. I know this can't be done with SQL Server, so how does one "copy" to transfer it???

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    There is an Import/Export feature in SQL called DTS (Data Transformation Services). This is similar to the import/export features you'll find in Access except far more powerful. For instance, if you were running SQL Server on your laptop for development (as I do), you could easily plug into your client's network and copy your structure and/or data directly from your laptop to their server. You can also import/export to many other formats (Access/Excel/Text--any ODBC compliant source).

    To find these options simply Right-Click in one of several places in the Enterprise Manager (on the Database in question or on the "Databases" folder), select All Tasks and you'll see the option for Importing and Exporting.

    If this type of transaction needs to occur on a regular basis, you can create a package with the appropriate instructions and tell it to run at any given interval. Isn't SQL Server great?!?!

    HTH

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    Mark... Thanks for your comments! I have used DTS to import from Access into my training/development SQL Server db.

    What i meant was, that in Access, i can design a new db, make a few tables, design a few queries, and write a bit of code. I can transfer that to my laptop, and then log in to the client's network and copy that mdb to his pcs. This is how i will do his front ends, but it will mainly contain forms and reports etc.

    I believe that you can't do that with a SQL Server database, so how would i do that in SQL Server?? I am sure there is a way, as i can't imagine i would have to be sat in his office logged into his server to write the backend.....

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    You can transfer the contents of one SQL Server database to another SQL Server. The DTS also supports this. It works the same way as importing/exporting to/from Access. Just select SQL Server (yours) as the source and SQL Server (theirs) as the target - assuming you're connected to their network and have the appropriate permissions on their SQL Server.

    HTH

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    Hey Mark! Cool! I had no idea! I thought it was only for data, as opposed to objects, etc... Maybe in my ignorance, i was misled by the title, DTS. OK, so all i do is select my laptop as the source when i am at home, and then that becomes the source when i am at my client's.....

    Time to go play i think.... Guess how i am going to spend my evening! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions required please! (Access/SQL Server)

    Oops...... I must be so excited.... I meant the destination when i am at home..........

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Opinions required please! (Access/SQL Server)

    Not quite. I think you have two options for the front end: Use an MDB and link to tables on the back end, or build an Access Data Project (ADP) (I think that's what it was, but I lent out my book; this is all armchair stuff for me). You cannot put interface objects like forms and reports and macros into a SQL Server database, just tables, views (queries) and stored procedures (queries). On the other hand, if those are the only object you have, then "upsizing" should work just fine.

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

    Re: Opinions required please! (Access/SQL Server)

    Jefferson's comments are correct. You will need to decide whether you want to use an ODBC connection to the SQL Server back-end, in which case you use an MDB, or use an ADP, in which case you will need to write stored procedures and such. At this point most people are using the ODBC approach because it lets you work with both Access tables and SQL Server tables. That can become a significant issue if you need local tables that need to be unique to a particular user. The trade-off is mostly performance related - ADP tends to be significantly faster in some scenarios. In our experience, ODBC is usually fast enought however.
    Wendell

Posting Permissions

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