Statistical Analysis Software., AKA SAS (1 Viewer)

[NauticalGent]

Does anyone have any experience with using SAS as a BE? There are only 3 threads on this forum and they are quite old...

 

[Pat Hartman]

No, but I've used other similar applications. Usually they refuse to let me link to "their" tables and insist on doing exports. What is your question?

 

[NauticalGent]

No, but I've used other similar applications. Usually they refuse to let me link to "their" tables and insist on doing exports. What is your question?

Can they be used like SQL Server tables?

 

[Pat Hartman]

Assuming the DBA will even allow you to link to them, yes. You do need to know what RDBMS is being used so you can select the correct ODBC driver. Most ERP applications use unnormalized tables and fields defined as text rather than numbers. They rely on their internal validation rather than RI so you may not know what you are getting if you link directly.

 

[NauticalGent]

"They" offered us "space" in lieu of an SQL instance. I did find the connection strings needed but I couldn't see anything definitive as far as Pass-Thru queries, Views and Stored Procedures, which is real reason I want to migrate the BE from SharePoint.

 

[Pat Hartman]

I've been using various RDBMS BE's since the mid-90's. In fact, my discovery that I could link Access to DB2 running on our mainframe and actually update the data is what made me a convert.

I'm not saying that you never need to use stored procedures, pass-through queries or views. But, I will say, don't start there. Convert the tables. Make sure the indexes needed for searching are created and RI is enforced. All tables MUST have a primary key. Use an autonumber if you don't have a candidate key.

If your forms are bound directly to tables or to queries with no criteria, that is where you start making changes. You will get NO speed improvement and in fact many applications slow down even more if the forms are not fixed. The point is that you want the server to do the heavy lifting and you don't want to be dragging excess data over the network so you would never want to bring down all the rows and filter locally as most people do with Access linked to Jet/ACE because that is how the samples MS creates work. The key is to have search fields on the header of a form if you have only a couple or make a form devoted to searching. The RecordSource query has a where clause that refers to the search fields in the form's header so the form always opens empty. The user enters his criteria and the form is required to select the record he wants. The idea is to bring back ONE record for a main form. You don't want your main forms to scroll unless they are data entry forms. When I do a search form, I build the WHERE clause on the fly and then run a count. If one record is returned, I open the edit form directly, otherwise I open a non-updatable list form that can be further searched and then choose from it.

If you find that you need to speed up performance, i start with views. Those do a lot to optimize joins since they are pre-compiled and the execution plan is already created. For certain types of bulk updates, I use pass-through queries, especially for deletes. The only time I've ever had to resort to stored procedures is for some complex reports with a lot of tables involved as well as sub reports.

Another thing that can speed up an app is to store the lookup tables locally. When I use this technique, I download the the lookup table (I only use one, I've posted it a few times). That way all the combos populate with local data. Since these tables don't get updated often, there isn't a problem. I also include an option to download them again on the menu in case they've updated something and so need the new version. Of course all queries that join to the lookup table use the server side version not the local version.

 

[AccessBlaster]

Wow, that information needs to be placed in a section that has a larger audience.