Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2006
    Location
    Indiana
    Posts
    7

    Unanswered: Making my Database global

    I am looking for the best way to make my database GLOBAL. Right now there are 2 databases one in Indianapolis and one in Geneva. We want to combine the data and have one GLOBAL database. I would love to hear ideas on how to do this. I have heard web based but we don't have the time to make all the pages ASP, we have discussed Citrix, does anyoe know of more options or some good reason to either use or not use the 2 I have presented. Any information will be appreciated. The application is in MSAccess 2000.

    Thank you


  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No one can make a specific recommendation without understanding a bit more regarding the purpose and functionality of your database.

    In general, a "split" database provides a solid basis for distributed database applications. Depending on the practical application, there may be other alternatives worth persuing.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Location
    Indiana
    Posts
    7
    Quote Originally Posted by Teddy
    No one can make a specific recommendation without understanding a bit more regarding the purpose and functionality of your database.

    In general, a "split" database provides a solid basis for distributed database applications. Depending on the practical application, there may be other alternatives worth persuing.
    The database is already split into a front and back at both sites. I believe I states in the first request that it is in MS Access 2000. There will be over 100 users hitting this databse most times once it is combines. We will be taking the backend to SQL to take care of this. My question has to do with distance of part o the users being over seas. What would you recommend to keep down on signal degredation.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hah... ok, now this makes a bit more sense. Your database is NOT ms access, it is MS SQL leveraging Ms Access as a front end, do I understand that correctly?

    Given this, there are a good many things you could do, ranging from how you hit the database to using clustered servers with push/pull subscriptions to nightly DTS packages for replication...

    In reality, distance is no issue. Assuming you have a respectable pipe running to your SQL server, you'll be just fine. I would recommend going with a disconnected architecture of course (think ADO), but other than that it's hard to say.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2006
    Location
    Indiana
    Posts
    7
    It is not SQL yet but will be. What do you mean a disconnect architure?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It means pulling relevant data into a client dataset and pushing it back when you're finished as opposed to always working directly on the server.

    Google can explain it better than I can...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by tair23
    It is not SQL yet but will be. What do you mean a disconnect architure?
    I'd be very surprised if you data wasn't SQL. After all there is no other way that I'm aware of talking to a JET database. Mebbe you mean its not SQL Server yet - just becasue Microsoft call their productr SQL server doesn't mean its SQL, after all its not as if Micsrosoft invented SQL - can't remember who did though - wasn't it some company who specialised in typewriters?

  8. #8
    Join Date
    Jan 2006
    Posts
    2
    Dear Tair23,

    OK..... so your data is in Access (although you plan to move it to SQL) and your program is in Access. I have a few clients doing similar things, so I hope some practical experience will be of help.

    I have used Citrix in the past and found it worked very well. The product lets you place everything on a single server, or a group of servers, and run everything from one place. The benefits of this for you is ease of integration. You don't need to do any Access/SQL development work to give all of you applications to all of your users.

    Response is fast as all the processing is done on the server and only screen images, keyboard and mouse movements (plus printing output) need to be moved to the remote locations.

    But, Citrix is expensive .... and you can achieve much the same result using Microsoft's Terminal Services add on to Windows Server operating system. Terminal services is not as clever in some areas as Citrix but for my money it's a very good solution for 95% of this type of requirement.

    I have two clients in particular which are relevent.....
    One has 35 remote sites with 100+ users with full Windows desktop including MS Office and custom Access applications using Terminal Server to do the hosting.
    They use Access to hold their data.
    The other client has approx 20 user many of which are field based and come in using VPN links over DSL (broadband) links. This client uses Access for the program and SQL for the data store.

    Both of these work well with happy users and good data integrity.

    I usually find that I can get about 25 users on a server using terminal services to run everything, although obviously this would depend on the applications being run and the workload of the users.

    A few general comments:

    1. Probably best to avoid Terminal Services or Citrix if your applications have heavy graphics content.

    2. Access as a data store does have an upper limit on file size, but this varies widely on how the application is used. If you have big data storage needs them SQL will serve you well, and keep your data uncorrupted.

    3. People will tell you that Citrix has a much small bandwidth requirement. However, I think Terminal server is only a little bit bigger (at about 25k per user) so unless you have very slow comms links don't be put off by this.

    I hope that the above is of help.

    Let me know what you think,

    Peter Merry

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That was all good stuff. However, I would highly advise rolling out with MSSQL or something comparable for your backend right off the bat. Access may SAY it handles 255 concurrent users, but it never does. Anything more than 5-10 will begin producing performance and stability issues.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And perhaps this may hopefully give you some ideas:

    Web Database Concept
    http://www.bullschmidt.com/concept.asp

    This is something I put together about ASP that hopefully might help somehow.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Remote accessing

    We use Citrix and Terminal Server for remote accessing. Citrix works well but we did have a few headaches with the print drivers and you are right that it is expensive. We also use SQL Server as a backend.

    If you are using MSAccess tables, I might recommend writing your code using unbound forms and functions to writeRec, updateRec, retrieveRec, and deleteRec records to speed things up and make it more usable for multiple users (you'll have to compare this verses writing ASP pages). But it is a way to work around the multiple concurrent users (combined with the vbs script below if you go the SQL Server/MySQL route). This has worked VERY well for us with db's over 1 gig in size and speeds things up considerably for the users (I've argued that MSAccess is not just limited to a few users using this technique)!

    When a record is retrieved, the retrieveRec function is called which retrieves the record and writes the values to the form. An unbound checkbox is on the form called: DataUpdated so when someone updates a field, this box is checked and it knows whether to call the updateRec function (or a "Save Changes" button is pushed). We use this technique on several db's over 1 gig and with only 512 meg of memory on our SQL Server box and a really slow Terminal Server (really pushing it.) The only thing which has made things crawl is when the users start exporting large tables to their drive.

    If you do decide to go to SQL Server or MySQL as a backend, you can also write a vbs script to make a clone of the mde/mdb file and add in the users last name so the user is working only in their db (don't try this using MSAccess tables though or you'll end up having to combine them together.)

    Here's the vbs script to clone an mde with the user's login name and then open up that mde (copy to notepad, change the file location/name and save as *.vbs):

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing

    LUName = "\\SQLServer\Databases\Databases\TimeSheet2003\Hou rs2003.mde"

    oldname = LUName
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    retval = objFSO.CopyFile(oldname, newName, True)

    Set objFSO = Nothing

    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")

    objShell.Run "MSAccess.exe " & newName, 1

    Set objShell = Nothing
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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