Results 1 to 7 of 7
  1. #1
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206

    Access and SQL Server on a VPN: Possible?

    Hi everyone! I'm back!

    In the past I asked about setting up an Access front-end with a SQL Server backend, and the replies I got were quite satisfactory (pbaldy's was one of them.

    A few days before I tendered my resignation, my boss had given me an assignment to create a system for a financial services company (it would be the second project related to money-lending I have ever handled).

    Now, the client's requirement is to have its data centrally stored in a database located on a server in the client's main office, and all branches located in different provinces will enter transaction data into that database using some interface - which I thought of at first would be a VB. The branches and the main office computers will be linked via a VPN.

    But because I know that the development of a VB interface (either an ASP.NET Web interface or Windows application) takes so long, I'm considering using Microsoft Access 2007 as my development tool for the interface instead. It will use linked tables that access the SQL Server over a VPN.

    Is this setup even possible? I only have limited amount of time to finish this project. Have any of you guys embarked on a similar project? I really need your response.

    Details:
    Backhaul speed is 2 mbps.
    Client speed is 384 to 1 mbps.
    Number of clients is 8 computers.




    Thanks a lot!

    Jan

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I have a couple of applications that are used in that environment, and their performance is very good. I will say that you would want to write it in such a way as to minimize the amount of data that has to go over the wire. Too much going back and forth over the wire could really hurt performance.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    To pbaldy:

    Thanks for replying, Paul!
    It's great to know that Access and SQL Server can work that way.

    I'm going to make the Access front end in the same way I would use an Access database by itself: A form with a subform, some fields will be filled in with numeric codes so that the lookup value is displayed (which uses a query), and several line items (around 30 to 50) will be encoded in the subform. It's similar to an invoice window.

    So, if I follow the same approach in creating a database that will be implemented in a VPN, then data will transmitted to SQL Server over the wire for every new record in the form and subform, and data is received from SQL Server to retrieve the lookup values (which is accomplished by a query) each time I make a new row of data. Is this a performance issue you warned me about? I'm not sure if I can optimize an Access front-end to handle this kind of issue.

    I can only be certain that the database will not be used in high-volume transactions like a Walmart POS counter.

    And regarding minimizing data, I think I'll have an issue in generating the balance of my receivables, since I have to sum up all sales and collections to obtain the balance. Or is it just the final summarized result of the query that goes over the wire, and not the individual rows on which I applied the GROUP BY clause? I am not so much aware how data travels over the network, but someone can help me out with technical matters.

    Paul, I'll mark this thread as solved, coz your answer is the exact point I want to know. Please be so kind to answer my follow-up questions though. I got eager to type all this when I saw your nickname on my thread.

    Thanks a lot! Cheers!

    Jan

  4. #4
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    To pbaldy:

    Thanks for replying, Paul!
    It's great to know that Access and SQL Server can work that way.

    I'm going to make the Access front end in the same way I would use an Access database by itself: A form with a subform, some fields will be filled in with numeric codes so that the lookup value is displayed (which uses a query), and several line items (around 30 to 50) will be encoded in the subform. It's similar to an invoice window.

    So, if I follow the same approach in creating a database that will be implemented in a VPN, then data will transmitted to SQL Server over the wire for every new record in the form and subform, and data is received from SQL Server to retrieve the lookup values (which is accomplished by a query) each time I make a new row of data. Is this a performance issue you warned me about? I'm not sure if I can optimize an Access front-end to handle this kind of issue.

    I can only be certain that the database will not be used in high-volume transactions like a Walmart POS counter.

    And regarding minimizing data, I think I'll have an issue in generating the balance of my receivables, since I have to sum up all sales and collections to obtain the balance. Or is it just the final summarized result of the query that goes over the wire, and not the individual rows on which I applied the GROUP BY clause? I am not so much aware how data travels over the network, but someone can help me out with technical matters.

    Paul, I'll mark this thread as solved, coz your answer is the exact point I want to know. Please be so kind to answer my follow-up questions though. I got eager to type all this when I saw your nickname on my thread.

    Thanks a lot! Cheers!

    Jan

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This was one of the rare instances where I created an unbound application. Not sure if I needed to, but I did. I didn't want a bound form trying to talk to the server, I wanted to control it. When the application loads, it populates local tables for the lookup fields (customers, vehicle types, etc). That only takes it a couple of extra seconds, and I put up a form telling the user what's going on. That means none of my lookups go back to the server. I either use a local table or pass-through query as the source for reports.

    I use a mix of stored procedures, views, pass-through queries and recordsets to accomplish everything. Regarding your receivables, the key is to get SQL Server to do most of the work. I have a parts inventory application and I use a SQL Server view to calculate quantity on hand (which is also a sum of purchases and sales). My front end just gets the value for any given part from the view; that makes the server do all the work of calculating the value. All that goes across the wire is the final value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    Thanks for the reply, Paul!

    Paul said: My front end just gets the value for any given part from the view; that makes the server do all the work of calculating the value. All that goes across the wire is the final value.

    Oh, I got it! So your view summarizes the list of inventories with their final quantity balances. And you send data across the wire one inventory part at a time.

    Thanks Paul for sharing your experience. I will apply your concept in my projects.

    Cheers!

    Jan

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Jan!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2010 vs SQL Server
    By mwolfod in forum Access
    Replies: 3
    Last Post: 10-18-2010, 04:25 PM
  2. Access and SQL Server?
    By evander in forum Database Design
    Replies: 2
    Last Post: 07-31-2010, 07:47 PM
  3. Access Frontend to Sql server db
    By ksukat in forum Import/Export Data
    Replies: 0
    Last Post: 06-08-2010, 01:35 PM
  4. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM
  5. Access / SQl Server Advantage.
    By caljohn527 in forum Queries
    Replies: 0
    Last Post: 01-24-2009, 06:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums