Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Azure SQL Server in the cloud with an Access 2007 FE

    Has anyone tried this?
    Are there any good document references on this?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Somehow I don't really believe that will be efficient. Access was not really conceived for such purposes.

    Any reference on how to use Access on a client server mode should do. Unfortunately the only reference I know that covers that is the Access 2000 Developer's Handbook, volume 2.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    When you say Client/Server, do you mean linking Access to SQL Server say?

    Unfortunately i don't have that book.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by patt View Post
    When you say Client/Server, do you mean linking Access to SQL Server say?

    Unfortunately i don't have that book.
    Yes, I mean that, especially residing on a machine that can present some latency when responding to data requests.

    If you have user Access with SQL Server, but on your own network, the issue here is that the network can potentially perform worse than your own. This can cause issues with bounded forms or queries that return a big number of records, especially in terms of a slow responding UI.

    I never needed to rely on other references, even considering this is an old reference, but the problems are pretty much the same in more recent versions of Access.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    If it is just linking to SQL Server tables what does that book spell out?
    Isn't it just setting up a DSN then linking through it to the tables in SQL Azure?

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Well, in what relates to connecting to the server and get the tables data, yes, absolutely. The problem is, as I said, how the fact that you are connection through the internet may affect how your application behaves, in terms of responsiveness (form loading, reacting to user interaction, etc). Bounded forms can be problematic, for example. There are a set of precautions and options to minimize the effect the internet can have on the performance of your app. I fear it really is hard to express them all here.

    Have you use linked tables from intranet SQL Server databases?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Yes i have used linked SQL Server tables across an intranet, in fact i had to convert a backend Access database to SQL Server. It works well.
    Thanks for your insights.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    From a strictly technical point to view, there will be no difference using Azure or an intranet SQL Server server. Performance may be an issue and if it will, then you need to address it. If it happens, just post here about it .

  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
    Quote Originally Posted by ruirib View Post
    Somehow I don't really believe that will be efficient. Access was not really conceived for such purposes.
    I think it depends on how you do it. I agree that if you just try to create linked tables you will have problems.

    However, I've been able to take a different approach that seems to work quite successfully. The intent of the app was to give a user full capabilities to see and update info on the server, even from a laptop while sitting at Panera Bread!

    In this situation I was actually working with a mySQL database sitting on the web (but SQL Azure should work the same). I did not link any tables! I had local Access tables that had the same structure as the mySQL tables and I would populate a local table from the MySQL table as I needed it and only with the record I needed. All activity with the MySQL tables was done with passthrough queries and Stored Procedures.

    For example, here is how I would add a new record to table tblClient on server:
    - Delete all records from local tblClient.
    - The Client maintenance form is bound to local tblClient, so I get all the advantages of using an Access bound form.
    - User adds the record via the form, but the Primary Key value is "-1".
    - The AfterUpdate event passes the field values of the record to a Stored Procedure on server.
    - Stored Procedure detects the "-1" and knows it is a new record (otherwise, it is an update).
    - Stored Procedure adds new record to server table, then returns a recordset containing that record with the new Primary Key (and some DateChanged fields).
    - Local table is updated with new PK.

    Quite frankly, I was surprised by the performance! Using the app, you can't tell you are getting and maintaining data on a remote server. Maintaining records, populating forms, subforms, and combo/listboxes; all operated as quickly as if the all the data was local. We even tested it by linking a laptop to a 4G cell phone! A little slower, but it still works fine.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    That's a very interesting approach. I suppose the key to that performance is the small amount of data exchanged between your client and the server. With small amounts of data the "slowness" of the connection matters less.

    Thanks for posting about it. It's really a very interesting approach.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    @ruirib:
    It may not work for every circumstance, but for those circumstances where it does work it opens up some new possibilities (including jobs that you couldn't really do before, but now you can offer a mobile solution).

    The situation I was testing had some 13,000 customers with over 100,000 Work Orders. Obviously not a really big database, but not tiny either. It did require some compromises, but I don't think they really impact the user that much. For example, a subform based on a pass-through query can't be modified directly; so I just require the user to double-click anywhere on a record to pop a form to maintain just that record (using the stored procedure method I described earlier).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    @MarkLiquorman,

    Indeed. Anyway, the way you handle it showed nicely the kind of concerns that a client server solution needs to address in order not to be affected by the network. It's no out of the box solution, though .

Posting Permissions

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