Access, SQL Server on a local network

Posted on 2013-12-09
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
  • 2
  • 2
  • 2
  • +2
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 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 125 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 36

Assisted Solution

PatHartman earned 125 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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 125 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 65

Expert Comment

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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