Solved

SQL Server Approach

Posted on 2013-06-13
15
261 Views
Last Modified: 2013-06-20
I have an outside office that has access to our servers here at that main office. I tried to put an Access app I made that works great in the main office. All the tables are in a BE Access. When I put the FE on the satellite office it takes forever to open a form with a linked BE table. I am sure it is connectivity between the offices. That said I can do nothing about this at this time. So I thought maybe I would go to MS SQL Server. When I put the tables in the BE will that be any faster? Do I need to link to the BE on SQL Server? What is the best approach in a case like this?
0
Comment
Question by:JArndt42
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246116
Hi JArndt42,

you can use replications of your access database, this will use less network because it transfer just the modified data. ask explicit for how to create an access database replication.

Regards
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246122
PS. What about you question Q:28148977, do you got access to the ms sql server? is this access database the same?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39246123
Hello BAKADY. what if two people make changes to the same field to the same record?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39246126
I have the tables on MS SQL Server. I always had that. What I did not have was a connection to the AS400 in SQL Server. Still working on that.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246160
what if two people make changes to the same field to the same record?
this problem happends if you have just one access file too, if 2 person open the same record at different workstations.
with replications they don't do that, each database hat a copy of the data changes are logged in background with a timestamp, you can sync manually this makes the replicated database can works offline. if some data get corrupted (differences) you will be notify.
Sync time could be longer as just the copy time between main and satellite office
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39246199
will the users need to be logged off the database in order to synch? I ask because we have a well known customer database, ACT!, that works that way and That is one of the draw backs of the program.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246228
ACT!??? ach scheiße!!!

i don't have good experiences with ACT!... 4 year ago i was working with the version 8 (because a Client), it was terrible...

can be you are from Germany??

About Access, mmmm yes, maybe...
0
Do email signature updates give you a headache?

Constantly trying to correctly format email signatures? Spending all of your time at every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

 
LVL 1

Author Comment

by:JArndt42
ID: 39246237
LOL. You know exactly what I mean then. No I am in US. Oregon. Can you believe that software is so popular? Well then you understand what I DON'T want it to do.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246269
how many people have use this at your satelite office?
it is posible to do more than one replication which sync to master, maybe one for user (if they are 3-5)

do you really need to ask again... maybe someone else has a better idea about the posiblities and capabilities of access database replications... i have used it just twice in my live...
ask about pros and contras... maybe you became a interesting answer...
0
 
LVL 1

Author Comment

by:JArndt42
ID: 39246272
There are five in the satellite office. Does that sound good?
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246301
i doesn't sound bad... but i recommend you to get more information about...
this time i'm a little bit stumped... but i think it could be posible...
if you try this make a copy and work with it around a week for test reasons, parallel to the original... one of the contras of access replication is that you cann't undo it.

Regards.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39246707
You can set a terminal server at the main office, then let the users at the satellite office reach that via Remote Desktop.
Requires, of course, a terminal server, but zero changes on your application.

/gustav
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39247801
Hi,

I would also recommend to use a Terminal Server for remote workstations. Replication is an abandoned feature of older Access versions, not implemented in current versions anymore so this is not very future compatible. Replication would only make sense if you need to access the data in situations where absolutely no online connection can be made available.

SQL Server has a compact edition which is able to replicate with it's "big brother" so in case you need replication it would be probably better to implement a compact edition on the workstation and connect the frontend with this. I personally have never done a replication so I cannot tell you much more about that.

The other advantage of a Terminal Server is the better performance as this runs on the server itself which normally has a better performance as any workstation. We made such solution in our current project (Access FE/SQL Server BE) and it works even in very problematic locations with only 1MBit online connection speed (shared with x other services like Internet, VoIP and so on).

Cheers,

Christian
0
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 39261023
I spoke the IT department and he likes the idea of Terminal Server. Not too hip about the price tag for 5 users but much better than what we have now. Thank you.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39261691
You are welcome!

/gustav
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now