Solved

SQL Server Approach

Posted on 2013-06-13
15
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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 50

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 50

Expert Comment

by:Gustav Brock
ID: 39261691
You are welcome!

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 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