[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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 39

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 39

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…
Suggested Courses

650 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