Access, SQL Server on a local network

Posted on 2013-12-09
Medium Priority
Last Modified: 2013-12-10
Right now I'm using Access 2010 to serve as a database for a report application I wrote.   Every morning, QuickBooks updates the Access 2010 database using QODBC (an ODBC interface).

Because one of our tables is so big (250,000 records), I'm getting random errors such as Out of System Resources and sometimes the importing just doesn't work.

I'd like to go to SQL Server Express, but I've never set it up on a local network before.  Normally I've created SQL databases using a web host.

Can anyone point me in the right direction to get started?  I am on a network and other people will need to have access to the database via my program.

Thanks for your help!!!
Question by:slightlyoff
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
  • 2
  • 2
  • 2
  • +2
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 39706832
>Can anyone point me in the right direction to get started?
For starters, does your application NEED all 250k rows, or can some of them be moved to a backup table(s) such that the Access app is a more manageable size.

Also, it's worth looking at all of the column data types and seeing if they can be modeled to take up less space, i.e. is a two-letter state code column a Text(255) or a Text(2), is a yes or no column numeric (up to 8 bytes) or Boolean (1 byte), what are the format of dates that are just date-only and without a time component, you get the idea.
LVL 32

Accepted Solution

Daniel Wilson earned 500 total points
ID: 39706922
Jim has brought up good points, but if you do need SQL Server Express, installation is about like installing most other Windows software.  Though there are a lot of settings, the defaults are good for most jobs.

I think that installation will include SQL Server Management Studio Express.  If no management tool is added to your Start menu, that is the tool you need to download and install.

The paradigm shift from Access to SQL Server is in the thinking about the database engine.  Access encourages one to think about the DB as a special kind of document one opens or copies around.  With SQL Server, you need to think of the server much like you would a web server.  That service runs on your computer and you access certain content through it.  The content in this case is your database ... and SQL Server is the only portal you will use to get to it.
LVL 38

Assisted Solution

PatHartman earned 500 total points
ID: 39707021
Is QuickBooks replacing the entire table or simply updating rows that changed?  I was not even aware you could drive the update from QuickBooks.  I thought you had to drive it from Access.

SQL Server Express needs to be installed on a server.  Not many folks have peer-to-peer networks anymore.  I have only set it up for local use on my own computer but give it a go.  Just carefully read al the options and if you don't understand the question, take the default.  Of course, it would be better to hire a DBA to set it up in your production environment.  That way you know it will be done correctly and efficiently.  You would also want him to set up back up jobs and reorgs to keep the database healthy.

Converting to SQL Server will also require some application changes to take advantage of having a "real" RDBMS as the BE.
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more


Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 500 total points
ID: 39708551
To clarify one of Pat's comments, SQL Server Express can be installed on any Windows PC, it doesn't have to be a "server".  But for multiple people to use it, that PC does need to be up and running consistently, and accessible to them across your LAN.

Depending on the version you install, you may need to check the configuration to make sure it is open to other PCs.  Some versions of SQL Server default to only permit access by the same machine.  For example, you may need to open up TCP/IP connections.

Armen Stein
ID: 39708555
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:


It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Armen Stein

Author Comment

ID: 39708642
Thank you all for your suggestions and replies.

To clarify and answer some of the questions:

1.  Each morning Access erases all the tables in the database and then, via QODBC, recreates and populates them.  When the process is complete, the database closes and I have a "compact on close" option set up.  

2.  I don't need 250,000 rows of data - actually only about 60,000 rows (the past 180 days of transactions) are used in the reports.  That being said, I'm not sure how to pull just the rows I need in the Access Import Command:  
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", False

Open in new window

3. We are on a Windows Network, not a peer-peer.  We are using Windows Server 2012 (I believe).  I can check with our Network company - maybe they can suggest a place to put SQL Server Express that would provide the access to all the stations.

4.  Is it possible the SQL Server Express is not the best option out there?  The portability and easy of use that Access provides made it seem like the perfect choice a few months ago.  I'm wondering if there's a database similar to Access (accessible over the network rather easily) that isn't as limited performance wise?

Thanks again for everyone's help!
LVL 66

Expert Comment

by:Jim Horn
ID: 39708972
Armen - Nice layout of downloads you have there.  I must give these a read sometime.
LVL 38

Expert Comment

ID: 39709631
1.  Rather than using the main BE for this, I would use a separate database that I create on the fly.  You would delete the old version and create an empty one and then import the data from QuickBooks.  This eliminates the need for compact and repair.
2. Rather than importing the QuickBooks table, I would link to it.  Then create a query that selects the records you want and use that in step 1.
3. They can tell you which server you should use to install SQL Server.  This is not the same as adding a file to a directory.  An install modifies the registry and in this case will also create services that need to be restarted when the server reboots, etc.  These are the "listener" programs that listen for queries sent from Access and other applications.
4. I have Jet/ACE tables with several million rows and linked SQL Server tables that have even more rows and don't have a problem.  250,000 is a substantial number of rows but well within the capabilities of Access.  The out of resources message might be coming from someplace else.  There are a couple of competitors for Access such as Alpha Five and FileMakerPro but neither is anywhere near as feature rich.  Access got its name because it provides "Access" to data anywhere as long as there is an ODBC driver for the data source.  That is a huge selling point.  The fact that you can pretty much run a wizard to transfer your tables and data to SQL Server and be up and running in minutes (not that you actually want to do this) says a lot for the flexibility of Access.  Don't fall into the trap of confusing Access, the rapid application development tool with its database engines Jet and ACE

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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