Linking to SQL Server

Question

I've developed an Access 2010 application that, upon launch, links to a SQL Server 2008 Express Edition database.  All is working well for my user within his LAN.  However, he now wants to install the Access application at 10 remote locations and link to the server via his VPN.  As such, I have several questions for the Forum:

1. How many concurrent links can SQL Server 2008 Express Edition support?

2. Do I want to continue linking or should I open a connection, grab/post my data and then close the connection?  If so, is DAO okay to use?

3. Any other words of advice?

Thanks.

Answer 1

I've developed an Access 2010 application that, upon launch, links to a SQL Server 2008 Express Edition database.  All is working well for my user within his LAN.  However, he now wants to install the Access application at 10 remote locations and link to the server via his VPN.  As such, I have several questions for the Forum:

1. How many concurrent links can SQL Server 2008 Express Edition support?

2. Do I want to continue linking or should I open a connection, grab/post my data and then close the connection?  If so, is DAO okay to use?

3. Any other words of advice?

Thanks.

1. I can't tell you the number of concurrent links, but it'll be more than able to handle 10.

2. I've only worked with Access/SQL Server with the tables actually linked so I can't answer this.

3. Being a former Access developer that has repented of his wayward life of sin and embraced the truth and purity of ASP.NET, I'd migrate to an ASP.NET based application as the pro's outweight the con's. For example, you would never have asked these three questions. You'll ask different questions, but not these. :)

---

David H

Answer 2

Dave,

Thanks for your reply.  With regard to #3, I really need a "thick" client for this app.

Ken

Answer 3

>1. How many concurrent links can SQL Server 2008 Express Edition support?

 

There's no hard limit.  Express is limited to 1 CPU and 1GB of RAM,

which should be plenty to support at least 10 users.  It all depends

on how demanding your processes are.

 

>2. Do I want to continue linking or should I open a connection, grab/post my data and then close the connection?  If so, is DAO okay to use?

 

For our applications, we use a combination of normal linked tables,

linked views, passthrough queries and ADO command objects (to use

stored procedures directly).  We also open a global DAO recordset when

the application opens.  This helps prevent additional security prompts

and may improve performance.

 

>3. Any other words of advice?

 

I've written a PowerPoint presentation on techniques for using Access

as a client-server front-end to SQL Server databases.  It's called

"Best of Both Worlds" at our free J Street Downloads Page:

 

http://www.JStreetTech.com/downloads

 

It includes some thoughts on when to use SQL Server, performance and

security considerations, concurrency approaches, and techniques to

help everything run smoothly.

 

Cheers,

 

Armen Stein

Microsoft Access MVP

www.JStreetTech.com

 

 

---

Armen Stein, Access MVP J Street Technology www.JStreetTech.com

Answer 4

There is may be a hard coded limit of active network connections on a share folder for a non-server OS. As example, for XP, which is not a server OS, the limit is of 10 users (try the detailled dialog for a share on a file or folder) (Haven't found such limit on 7). So, be sure that the OS having the MS SQL Server engine supports the minimum required connections (even though it may be less of a problem with MS SQL Server than with Jet which effectively share "a" file, not a service). Also, with a server OS, you can somehow give priority to requests coming from the network over some of those coming from the PC itself, giving a better response time to remote requests.

Answer 5

David Holley wrote:

3. Being a former Access developer that has repented of his wayward life of sin and embraced the truth and purity of ASP.NET, I'd migrate to an ASP.NET based application as the pro's outweight the con's.

Why?   What are the pro's vs con's?

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

Answer 6

Armen,

Thanks.  Very useful.  I liked your PowerPoint - only wish I had been there to hear you present it.

Thanks again!

Ken