Sage 100

In a recent thread (https://90minds-com.socialcast.com/messages/13354784), there was discussion about using Access linked to MAS90 data. At Summit in one of the CRM classes I attended, they used a linked server in SQL server to connect to the MAS database. Given the low cost of SQL Server Express, is there a reason that people use Access? I'm not saying that one way is better than another, just curious as to why we've ""standardized"" on Access.

I think that it is well known and it is super easy to set up the linked tables. Personally once the tables are in SQL, I think SQL is easier to create the views.

I've pretty much have converted all my Access links to SQL and take it one step further by using a stored procedure each night to pull MAS tables into SQL Server. The downsides are how dates are transferred and the additional memory required to run SQL Server. If MAS200_LOL was the linked server, then the SQL statement to transfer the AR_Customer would be thte following: DROP TABLE dbo.AR_Customer; SELECT * INTO dbo.AR_Customer FROM OPENQUERY(MAS200_LOL,'SELECT * FROM AR_Customer') Installing SQL Server Express on the server hosting MAS makes the transfer pretty quick.

Ease of use. You don't really need a copy of the Access executable on the server for PTQ's to work with Crystal or Excel. If you have SQL available to you (for my clients, it is the exception rather than the rule) and you are comfortable using it, then by all means go that route.

It's a matter of using the tools you know.

When the data sets are not too large and the table links are not complex, the performance with Access is very acceptable and is far easier to setup and use. To install SQL Server ""Express"" with the management tools, setup the silent ODBC connection, setup the linked server, setup the SQL Server databases, load up the stored procedures and setup the scheduled batch files to run the store procedure to do the transfer takes little over one hour, provided there are no issues. Should probably plan on at least 2 hours when you have to deal with SQL and Windows security. The Access solutions takes about 5 minutes and you don't have the SQL date formating issue and if you can access MAS, this solution will work. The SQL option requires support. I only use it when there is IT support available or the client has deeper pockets.

I believe more people are familiar with Access and is generally easier to setup.

Myron, Appreciate the analysis. Well said.

Great thoughts and comments. Thanks for the information. Anyone have any performance problems or issues over the long haul with either solution?

There are little quirks in Access PTQ's that you run into from time to time as there are with any software program. The biggest headache with Access PTQ's is setting up the PTQ for each table. You can set up a master copy with all of the frequently used tables (e.g. AR history_header/Detail, SO_OpenOrderHeader/Detail, ett). The issue then becomes modifying the ODBC Connection string for each company you install it at. My work around to that was to have a macro written that will replace each default connection string with one specific to the company at hand. Once I had this work around, life was much easier. BTW, Dan Burleson has developed a Access Shadow Database which is a very automated (turn key) db that is very useful in handling larger datasets. Drop him a note if you would like more information.

// cheap plug on // The other way to populate SQL tables is to use DSD's SQL Mirroring. It keeps the selected files always in sync. All versions of MS SQL are supported. // cheap plug off.//

My apologies to the award winning MD team at DSD for omitting their SQL mirror product. It is the third piece to the report writing/ data mining puzzle. Just as GM has a car for every budget, the 90Minds group has a solution for every company budget and user sophistication. For the DIY techies driving a Chevy, PTQ Access db, for those who would really rather have a Buick, Dan Burleson offiers a simple inexpensive turnkey approach with his Shadow Access DB. And finally, for those wanting the rich luxury and smooth quiet ride of a Cadillac, take a tour down the boulevard in DSD's SQL Mirroring solution. Add value to your client's bottom line (and to your bank account) with one of these innovative solutions!

Nothing like a good chuckle first thing in the morning. Thanks Jeff :)

As DSD is in San Diego, it would be more appropriate to compare the SQL Mirroring solution to a 700 series BMW rather than the caddy.

@MyronStevenson - Sorry, trying to keep a made in the US of A, Mom and apple pie theme (even though I believe BMW does have a US manufacturing facility. Also Sage NA is under the (heavy handed) influence of the continent already. @JohnLeonard - On important issues, important to maintain some humor. Does anyone remember aspect about Pascal's keynote other than ""fucus"". Because of that one word, I will remember his overall theme.

Thank you for clarifying that Jeff. So Cal is so different than the Midwest I get mixed up and think it is another country.

Oh @MyronStevenson, you make us Midwesterners seem so naive!

@JeffSchwenk . I don't remember much else, except the video. You bring up a good reminder of this oldie: ""I don't remember much of what you said.But I DO remember how you made me feel."" When dealing with difficult customers, it is worth remembering. (And this is coming from one who, sadly, rarely does!)

@JeffSchwenk's outline of our options regarding data mining is not only humorous but very helpful to those of us looking at options. Thanks Jeff!