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 35

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 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