Solved

Access, SQL Server on a local network

Posted on 2013-12-09
8
602 Views
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!!!
0
Comment
Question by:slightlyoff
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
Comment Utility
>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.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 125 total points
Comment Utility
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
Comment Utility
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.
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 125 total points
Comment Utility
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 9

Expert Comment

by:Armen Stein - Microsoft Access MVP since 2006
Comment Utility
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
0
 
LVL 1

Author Comment

by:slightlyoff
Comment Utility
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!
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Armen - Nice layout of downloads you have there.  I must give these a read sometime.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now