Re: Connect to Access Data from various regional locations

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul Proefrock (proefrock_at_NO_heartlandgroup_JUNKMAIL.com)
Date: 12/01/04

  • Next message: Sylvain Lafontaine: "Re: Connect to Access Data from various regional locations" 
    Date: Tue, 30 Nov 2021 20:15:32 -0600

    Sylvain,
    Learning, slowly but surely.

    Is it necessary that the host server the BE resides on support SQL or is it
    just necessary that we have a folder that we can put the BE in and that
    folder be accessible with Port 1433?

    We have a host who supports MySQL but not SQL, says the license is too
    expensive. They say port 1433 is only for SQL. It might be a losing
    arguement but I don't want to spend a lot of time setting it up to find out
    it won't work. They want their hosting money up front and you know what it
    is like to get refunds ;-(

    Is there a specific knowledgebase article that references this MDSE/Access
    procedure or did you sort it out by yourself?

    Thanks

    Paul P

    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:uoQVv9c1EHA.2292@TK2MSFTNGP15.phx.gbl...
    > An FTP folder? I'm not sure of fully understanding what you would have
    > wanted to achieve with this; unless each user would his own copy of the
    > mdb files.
    >
    > dbSeeChanges is also required for some .Execute command.
    >
    > The performance can be very good (and in fact practically as fast as local
    > database on some occasion) but may require using Views for queries using
    > join. In the later case, you will have to look at the following article
    > to make them updatable: http://support.microsoft.com/?kbid=209123 .
    >
    > Using databases over the Internet is a complex thing; you will have to
    > learn your lessons.
    >
    > S. L.
    >
    > "Paul Proefrock" <proefrock@NO_heartlandgroup_JUNKMAIL.com> wrote in
    > message news:%23$aBg1b1EHA.2572@tk2msftngp13.phx.gbl...
    >> Sylvain,
    >> Thanks for your solution, looks like this is the answer we were looking
    >> for. Do you mind a couple questions:
    >>
    >> 1) Our plan would be to put the data files in a ftp folder on a web
    >> host. That keeps them off the internet yet still accessible or would this
    >> not work?
    >>
    >> 2) I will query about the port 1433.
    >>
    >> 3) is the dbSeeChanges associated with any other command other than
    >> OpenRecordset? Can I shorten the broken links by searching for the spots
    >> that will require the additional setting?
    >>
    >> 4) All users will be connecting via broadband. What can we expect for
    >> performance issues from those who do a lot of data input who are used to
    >> having the data actually on their machine? Will the page refresh or data
    >> input slow down appreciably?
    >>
    >> Are there any issues or items I should be aware of or search out as I
    >> work on this transition?
    >>
    >> Thanks for your help
    >>
    >> Paul P
    >>
    >>
    >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    >> wrote in message news:eHiyTUY1EHA.2112@TK2MSFTNGP15.phx.gbl...
    >>> Split your Access database into a Frontend (FE) with tables linked to a
    >>> Backend (BE) and use MSDE as the BE instead of Access. When using MSDE
    >>> instead of Access as the Backend, all you will have to will be to add
    >>> the parameter "dbSeeChanges" here and there. For example, instead of
    >>> writing:
    >>>
    >>> Dim rf As DAO.Recordset
    >>> Set rf = qf.OpenRecordset(DAO.dbOpenDynaset)
    >>>
    >>> you will have to write:
    >>>
    >>> Dim rf As DAO.Recordset
    >>> Set rf = qf.OpenRecordset(DAO.dbOpenDynaset, dbSeeChanges)
    >>>
    >>> Finding where to put these changes will be easy because Access will
    >>> generate an error and tell you where the first time you will try to
    >>> execute a piece of code without this parameter.
    >>>
    >>> There is a wizard to split your database into a FE and a BE; so except
    >>> for the small change of code above; there is nearly no work to do at
    >>> all.
    >>>
    >>> Finally, if you don't have a static IP address for the machine where the
    >>> MSDE database will be located, you may use a service like www.no-ip.com
    >>> . This will enable you to use nearly any machine as the database server;
    >>> without the need to buy space. The only required thing to do will be to
    >>> make that the port number 1433 isn't blocked by a firewall and correctly
    >>> forwarded if the machine is connected to the internet via a router
    >>> instead of having a direct connection. (Many providers will offer MSDE
    >>> or SQL-Server as part of their offering; however, many of them will also
    >>> block the port 1433 for a « better » security; so you cannot use them
    >>> for this kind of service.)
    >>>
    >>> Total cost: 0$.
    >>>
    >>> S. L.
    >>>
    >>> "Paul Proefrock" <proefrock@NO_heartlandgroup_JUNKMAIL.com> wrote in
    >>> message news:ec8kj1W1EHA.3120@TK2MSFTNGP12.phx.gbl...
    >>>> We are a non-profit organization that keeps all our membership info in
    >>>> an Access Database. We would like to be able to put the back-end(data)
    >>>> in a centralized location and have various users connect, real-time, to
    >>>> update their portion. Users are located in Florida, New York, Missouri
    >>>> and Nevada.
    >>>>
    >>>> We do not have an organization owned server and we buy space for our
    >>>> hosted website and email.
    >>>>
    >>>> We don't want to go to Data Access Pages (certain access functions not
    >>>> supported) or ASP, (Non of us are proficient in ASP), at least not yet.
    >>>> I understand that Access only likes to connect to data when on a true
    >>>> network.
    >>>>
    >>>> What various solutions may be available to us? VPN, hosted server, ? ?
    >>>> ? Mind you, we are a non-profit so money/cost is an issue.
    >>>>
    >>>> I would appreciate any ideas on how to accomplish this
    >>>>
    >>>> Thanks
    >>>>
    >>>> Paul P
    >>>>
    >>>
    >>>
    >>
    >>
    >
    >

  • Next message: Sylvain Lafontaine: "Re: Connect to Access Data from various regional locations"

    Relevant Pages

    • Re: Connect to Access Data from various regional locations
      ... Is it necessary that the host server the BE resides on support SQL or is it ... folder be accessible with Port 1433? ... That keeps them off the internet yet still accessible or would this ...
      (microsoft.public.access.externaldata)
    • Re: External v. Private DNS Resolution Puzzler
      ... > resolve the name and go out through our Internet link to get there. ... > But if I create an xyzcust.com zone on my DNS server, ... Create a forward lookup zone using the host names you need, ... folder) is not a valid host name, click OK to create the record anyway. ...
      (microsoft.public.win2000.dns)
    • Re: External v. Private DNS Resolution Puzzler
      ... >> resolve the name and go out through our Internet link to get there. ... >> But if I create an xyzcust.com zone on my DNS server, ... > folder) is not a valid host name, click OK to create the record anyway. ...
      (microsoft.public.win2000.dns)
    • Re: Add new cluster and use existing LUNs?
      ... Meta-volumes are dedicated to a single host, single activity (OS, swap file, ... varied activity (DBMSes, Messaging Server, File Server, Web Servers, ... Some of the physical spindle limitations can be addressed through the SAN ... >> test would be to run a simulated SQL I/O load on the newly created LUNs ...
      (microsoft.public.sqlserver.clustering)
    • Re: Creating individual .pub files
      ... the host, and go back and rename the original files and folder, remove "Old" ... Remember that the code snippet has to be inserted on each page...just stick ... One subfolder will be called "aboutus", ...
      (microsoft.public.publisher.webdesign)