Access + SQL Server: remote access

[adi2011]

Hi folks,

I have a small program built-in Access in which you now want to connect to SQL Server 2008 R2 database using upsize wizard which I have already done but I would also like to enable remote access to this program. For example, a client who is using my program can access a database from multiple locations. If central database is in Paris, my client wants to access that database/access program from New York, London and Chicago via Internet.

I'm not sure where to start and how to can I achomplish this, I tried using google to find some useful material / tutorial but I could not find anything concrete.
I hope to some of the more experienced users can give some advice, guidance on how to pull this off, or useful link or something.

Any help is apprecaited and many thanks in advance!

Cheers
Adi

 

[pbaldy]

I have a similar setup that works pretty well. My outside users first connect to the office network via VPN, at which point the program will work for them. I will say that you want to make sure the program is moving as little data back and forth as possible, for performance reasons. You don't want to open a form bound to a 100k record table.

It is possible to open up the SQL Server and connect without the VPN, but I don't think it would be as secure. Other options include using Remote Desktop, Terminal Services or similar technology to take over a computer sitting on the office network.

 

[adi2011]

Thank you very much for prompt reply pbaldy.

It looks like that VPN is best solution for me, same as your solution. When you say Remote desktop do you mean applications like TeamViewer? In case of larger professional business solutions where centralized database is located in one city and remote users are located in other cities all around the world, is most common solution VPN or some other type of soulution?

All the best,
Adi

 

[pbaldy]

Sounds like it, but I've never used TeamViewer. Remote Desktop is built into Windows. There are similar 3rd party options like GoToMyPC. I can't say what most large companies do, but my guess would be Terminal Services or Citrix. There's some info at the bottom of this:

http://www.kallal.ca/Wan/Wans.html

starting where it says "Use a true client to server setup"

 

[adi2011]

Thanks again pbaldy for your help, link you send me was very useful and I understand it much better now.

Greetings,
Adnan

 

[pbaldy]

No problem; others could have other thoughts. I'm certainly not an authority.

 

[bparkinson]

My SQL Server is in Texas in the USA. My users are all over the world. I'm in a different part of the USA doing development. My solution was to buy SQL Server hosting with one of the many companies that offer it, and make my Access app aware of the correct connection string for the SQL Server, in the case of just issuing SQL commands to the database, and to use linked tables. It works well. I use SSMA to manage databases as if they were local.

 

[adi2011]

Thanks for sharing this useful info bparkinson!