Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server / Access as FE (AXP/SQL2000)

    I am looking for the "right" solution for an interim front end to a SQL database. I converted an Access BE data file to SQL and it is running. I have the old Access front end that I would like to use but it is very slow to interact with the tables. I am not sure if something is set wrong or what. I have made a new Access Data Project today and now I can access the records with the speed I was expecting. Now I have to ask the gurus out there, what would be the best interim solution as a front end file for this database? We have 25+ concurrent users hitting this thing throughout the day so it has to be fast. After some development, we will use ASP pages to access the DB. Any ideas. Oh yeah, I forgot to mention that I have set up access using ODBC right now and I am not sure if this is best either.

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Using an ADP will always be faster than using ODBC connections as you are running in native SQL mode. However, unless you have bunches of users pounding on it with data entry, you can usually get acceptable performance with ODBC. There are some things to consider however. Make sure all of your tables get pushed to SQL. If you start using some tables in SQL and some in Jet, things get very slow quickly. Also make sure that things got indexed properly in SQL, and where performance is key, consider making a view and connecting to the view via ODBC rather than joining to individual tables and then creating an Access query. Another thing to do, especially if you are running on a 10Mbit LAN, is to deploy the FE database to the individual workstations.

    All that aside, we have a client who runs about 60 users on a 100Mbit LAN with a pretty complex system of about 150 tables and 800 queries running ODBC, and their typical response time to pull up a specific record with 15 to 20 tables behind the form is under 1 second. We are gradually converting from ODBC to ADP, but the interim solution has proven to be pretty stable and give acceptable performance. Hope this is useful.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Thanks Wendell,
    The connection using the ADP is pretty fast. I have some learning to do about indexing in the tables. What is the best method of indexing? Also, I have to recreate all of the queries as they are not compatible I guess. I am able to open the tables, they are all resident in SQL by the way. I have imported everything that the ADP allows like forms, reports, etc. These items came from the original FE file.

    The existing system had been using the FE on the local PC while the BE file was on the network. However this was a bit slow still (using 100mb). We then implemented a Citrix system where the entire app runs on the server. That is really fast in comparison as the data is not running back and forth across the network. With the addition of many more users I thought it prudent to convert to something scalable like SQL. I am tossing between using something like VB or an ADP to run the front end. In the end we will write ASP pages to run the database. I need a quick interim solution that will take advantage of SQL and not cost too much development time. Am I making any sense here? What are your suggestions? Also, if anybody else has anything to add, I would appreciate it.

    Thanks,
    Mark

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Go with the ADP for now. It's a window directly into SQL Server. With VB, you have to fumble around in the dark.
    Charlotte

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    I haven't played with the conversion tools in XP, but when we tried it with 2000, it left lots of holes, and all but the most mundane of queries had to be recreated by hand as either SQL views or as Stored Procedures. That's a good deal of work! I frankly think you'd be ahead to spend some time in performance tuning with ODBC connections. You get most of the benefits of SQL in that if your queries are well behaved, the ODBC driver converst the SQL into something SQL Server understands, sends it across the network, and SQL sends back just the data you want. A couple of questions:

    How many users do you anticipate growing to?
    How much of the usage is data entry as opposed to display and analysis?
    Are your servers on a dedicated 100mbit LAN switch, or is it just a simple 100mbit hub?

    As to doing the work with web pages, a couple of cautions. You will find it takes three times as long to write a comparable web page form using ASP as it does to do a VB form, which typically takes three or more times as long as doing an Access form. In addition, there are a number of limits to what you can do on a web page compared to what Access can do. Data validation before submiting to the server is one thing. Combo boxes don't work nearly as well. There isn't anything comparable to a subform. I know lots of people who are convinced it's the best thing since sliced bread, but I'm yet to be convinced.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server / Access as FE (AXP/SQL2000)

    HI Charlotte,
    Understood. Thanks!

    Mark

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Hi Wendell,
    We will have as many as 500+ users, Data Entry maybe a tenth of that, and yes they are on 100MB switches and not hubs.

    When you say it takes three times as long to write, are you saying the initial creation of the form (design) or when it is being used by the end user? What ever the solution be it ASP, java, etc., it will be web based as this is the direction the company is going.

    Thanks,
    Mark

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    As to the three times, it can be both. Maybe .NET will change things - I've heard noises about it reducing the development overhead to about 1.5 times VB. The real challenge is to do the sort of things we do in Access routinely when you don't have an event driven paradigm. As a result you often have to create several to many pages to do what you do in one form in Access. We are currently doing a web version of an address update form we created (actually, it's a main form and two subforms), and it looks like it's going to somewhere between 20 and 30 ASP pages to duplicate the functions. As a result, it will take the web user considerably longer than an Access user to make the changes. In this situation it will be acceptable, as the user is removing data entry tasks from an Access user, but some will try it and decide it's too big of a pain, and send an email or fax saying please change my address to ..... I understand the corporate direction - we are seeing it with lots of clients. Good luck - hopefully these comments will give you some idea of the gottchas.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Does anyone know where I can find a reference of what the differences between DAO and ADO are? I have recently created and Access ADP with a connection to the SQL server. I can see all the data by opening the tables. However, when I import my forms they cease to function and I think the problem is that the DB is now running ADO instead of DAO. For example: When I try to "dim dbsperson as database" it doesn't recognize "database".

    Thanks,
    Mark Santos

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Hi Mark,.

    Be sure to use Explicit variable declarations:
    Dim dbsperson as DAO.Database 'OR
    Dim dbsperson as ADO.Database

    As for the reference of ADO vs. DAO, it's included in the Office VBA Programming guide (it comes with the Developer Edition of Office).

    You can also view this on the web at: <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrchoosingadoordaoforworkingwithaccessdatabases .asp?frame=true>Choosing ADO or DAO for working with Access Databases</A>

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Mark,

    There's no database object in ADO. Recordsets, Fields, Parameters, Properties, Errors, etc., but no Databases.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Along with explicitly declaring either ADO or DAO, make sure that you have the DAO library referenced in the project.

    From the VBA Window, goto Tools --> References and check (select) the Microsoft DAO 2.6 reference.

    Cheers

    Jayden

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    DOH! <img src=/S/beep.gif border=0 alt=beep width=15 height=15> <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    As I clicked the "Post" button I knew something didn't sound quite right. That's what I get for not double-checking, especially when I'm somewhat green with ADO. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    My apologies <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

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

    Re: SQL Server / Access as FE (AXP/SQL2000)

    Don't worry about it. I still have ADO lapses. It is very different from working with DAO, despite the similarities in object names. I must have 6 books on ADO and still run into things that frustrate me, like trying to remember whether a particular object should be declared as an object or an AccessObject. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Charlotte

Posting Permissions

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