Solved

SQL Server Approach

Posted on 2013-06-13
15
262 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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

815 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

8 Experts available now in Live!

Get 1:1 Help Now