slightlyoff
asked on
Access, SQL Server on a local network
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!!!
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!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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!
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", False3. 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!
Armen - Nice layout of downloads you have there. I must give these a read sometime.
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
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
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