Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Red face Unanswered: VPN / Remote access strategy for Microsoft Access 2000 with ODBC links to SQL

    I would like to request advice on a strategy for implementing something I have been working on for a while...

    I have developed an application in MS Access 2000 which currently uses ODBC Links to a SQL "Orders" database on a server in our head office.

    (The application does not write anything into the SQL database directly - The SQL Database is a backend to our accounts software.

    (New orders created in the access database are identified by a 3rd party piece of software and inserted into the SQL database by this to ensure a fully audited path - this 3rd party software is written by the same people who wrote our accounts package.)

    The question I have is this - up until now, the application has been written with this location (Head office) in mind only - We now have an additional requirement that the application is used at two other locations.

    Things to bear in mind are that;

    >the application needs to be able to link to the SQL tables on the server
    >sites 2 and 3 connect to site 1 via VPN
    >only a handful of users at each location using the app

    My intention is to upzise the application to an Access data project stored on the server x:\

    (Its currently still just a single MDB as I am still writing bits)

    SO HERE IS THE QUESTION
    Remote connections are a new thing for me, although my access programming is pretty good, I have never configured an app in such an environment.

    Generally speaking, does the suggested configuration sound like it will work and perform ok? (The VPN has a 256k connection)

    IE Will the remote sites be able to utilise the ODBC links to the SQL backend etc.

    Or could someone suggest a better way to configure this app.
    (We also have Citrix but the version we have does not support access 2000)

    Sorry if this thread is a bit long-winded and I hope you understand my question!!!

    I do appreciate any comments and in an effort to make things a little clearer (hopefully) I have sketched out our basic configuration as a .bmp.

    Look forward to comments - Cheers!
    Attached Thumbnails Attached Thumbnails diagrambmp.bmp  

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: VPN / Remote access strategy for Microsoft Access 2000 with ODBC links to SQL

    Originally posted by garethdart
    I would like to request advice on a strategy for implementing something I have been working on for a while...

    I have developed an application in MS Access 2000 which currently uses ODBC Links to a SQL "Orders" database on a server in our head office.

    (The application does not write anything into the SQL database directly - The SQL Database is a backend to our accounts software.

    (New orders created in the access database are identified by a 3rd party piece of software and inserted into the SQL database by this to ensure a fully audited path - this 3rd party software is written by the same people who wrote our accounts package.)

    The question I have is this - up until now, the application has been written with this location (Head office) in mind only - We now have an additional requirement that the application is used at two other locations.

    Things to bear in mind are that;

    >the application needs to be able to link to the SQL tables on the server
    >sites 2 and 3 connect to site 1 via VPN
    >only a handful of users at each location using the app

    My intention is to upzise the application to an Access data project stored on the server x:\

    (Its currently still just a single MDB as I am still writing bits)

    SO HERE IS THE QUESTION
    Remote connections are a new thing for me, although my access programming is pretty good, I have never configured an app in such an environment.

    Generally speaking, does the suggested configuration sound like it will work and perform ok? (The VPN has a 256k connection)

    IE Will the remote sites be able to utilise the ODBC links to the SQL backend etc.

    Or could someone suggest a better way to configure this app.
    (We also have Citrix but the version we have does not support access 2000)

    Sorry if this thread is a bit long-winded and I hope you understand my question!!!

    I do appreciate any comments and in an effort to make things a little clearer (hopefully) I have sketched out our basic configuration as a .bmp.

    Look forward to comments - Cheers!
    If your network is set up like the one I work on then the vpn should just act like an extention of the local network. If that is the case and your odbc is working correctly now, it should work when someone is dialed in threw the vpn software. one easy way to find out....try it. Im sure you can find a test user at the other location that will give you a few mins to query a simple db.

    hth
    Jim

  3. #3
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    yes and no

    256kbs is slow connection and I can tell you why. You said you have 256kbs VPN connection, right? If you say VPN I expect two locations connected to the internet and VPN over internet. I that case expect about 100kbs if you are lucky. Everything depends on all users going to the internet and back..... some users will use internet radio, some download huge movies. I had little bit faster VPN and I got 100kbs... maybe you have direct link (fractional T1 line) - it's litte bit different situation and speed is not influenced by internet users.... anyway 256kbs is not blazing speed.

    Your task is much easier cauze you don't need to write anything to the server (or do you? I'm not sure if you write to some temp table on SQL or if you upload comma delimited text file or....) ... I'm not sure if you really need Access, but there are some better and faster solutions - web server and ASP pages. If you still want to use Access, try to UNBOUND all forms, when you open forms, read ONLY what you have to read - this means two things. First, forget all queries SELECT * FROM table and really specify fiends, it also means force users to select some records (small portion) and do not show all records when they open FORM. Microsoft Access Project can really help and it's fast, but Access2000 version is not the best. AccessXP is much better.

    I'm using my large Access 2000 application on Terminal Server and users LOVE IT. Even users in the main office use it, cauze it's faster than Access on their desktops. Generally say, don't go this way if you don't really HAVE TO.


    so, the summary? Yes, if you have 256kbs you can have good unbound or SQL server backend app. If you have few forms and need to show few fields from your accounting system, try to learn ASP and put everything and IIS server, you will spend more time during development, deployment and maintenance is much easier and you don't care so much about the speed....

    Also try MS Access Web Pages - for READ ONLY app it can be your way... again AccessXP is much better. Also OfficeXP has different policy and you can put Office ActiveX controls even on PC, where you do not have MS Office. If you want to go this way, I really recommend a book or a manual. Putting controls on web page can be tricky and without a book you won't discover all secrets...



    jiri
    Last edited by playernovis; 01-13-03 at 11:49.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Book tip on Access Data Pages

    Book tip:

    Alison Balter's Guide to Access 2002 Enterprise Development:
    http://www.amazon.com/gp/reader/0672...03#reader-link

    The desktop dev. counterpart to this book is exellent, I just ordered the enterprise book myself. That is the one handling Access Data Pages (writing apps for using/accessing mdb databases via the browser) in detail.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Talking Thanks

    The app has been running now for a while:

    Set up with front-end on users desktops (Head Office)

    Remote users now configured on Terminal services - runs great!

    Thanks for your tip about ABs book. I have the 2000 developers edition which I have had for a few years - Bit dog eared but handy reference book.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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