Rob: MS Access imports data easily from dBase (*.dbf) files. I see no advantage in moving to SAS for that feature, especially if you would prefer to use the basic DE form controls in MS Access to develop what should be a simple DE capability. Although ODBC transfers of data from MS Access (*.mdb) databases has some pitfalls, as does OLEdb or other middleware (in particular, conversions of integers to floating point numbers, date/time values, and nulls/missings), a bit of Q/C will help you iron out the wrinkles and set up a reliable connection. It often helps as well to separate a data collection operation from a data analysis group. Even on a small scale, periodic publishing of analytic datasets usually works better than giving researchers continuous access to primary data files. Continuous access makes it more difficult to check estimates for consistency and to replicate earlier results. A separate database for data collection operations also makes it easier to implement a sound database design for operations. Data analysts prefer to restructure data to fit analytic procedures, and have little appreciation of data integrity constraints and data validation. Fortunately you do not have to choose between MS Access (or another DBMS) and SAS. SAS has particular value on the analytic side of your project(s). Access to a data source with a good relational database design guarantees that a SAS programmer can restructure data quickly and accurately into the structures that analysts need. Analysts will likely want to 'snapshot' data into SAS datasets so they can run frequencies and create statistical models. Starting from where you are, I would agree that you should keep it simple at this stage. When the project evolves to the point that you will need Web DE and distributed databases, SAS may have the integrated system components that you will need. Of course, others on the list have different views and may convince you to develop a SAS database. I have done that many times as well, but I usually reserve that effort for very large and complex databases that serve as data warehouses for disparate data from many different sources. Sig -----Original Message----- From: Rob Cheshire [mailto:rob.cheshire@NOAA.GOV] Sent: Tuesday, March 02, 2004 10:27 AM To: SAS-L@LISTSERV.UGA.EDU Subject: help SAS/MS ACCESS, general database questions Hi to all, I need some help deciding on the best database system for our specific application. OPTIONAL INFO: We currently have 2 separate projects that need to be updated from dos-based dBase. I'll only describe one of these but the other is comparable in size. The project currently has about 32 files. The 2 largest are about 30,000 records and 5 columns include data from the last 30 years. The other 30 files are created by year and are about 1000 records and 10 columns. There are only 2 or 3 people keying in data, never at the same time. We also have a sonic digitizer (measures distance between age rings on fish scales) from which data is fed into the dBase form. I think the data transfer program is written in C and the dBase form is written in Clipper. We currently have MS Access and SAS. My suggestion was to design the same type of user entry forms in MS Access and place the files on a shared drive on our network server (already in place and routinely backed up). If the amount of data or number of users increased we could consider going to a database server with Access as the "front end." Most of the analysis is performed in SAS which can readily import Access tables. I was met with some opposition: SAS/MS ACCESSS/SERVER QUESTIONS: There was a strong objection to using ODBC to get data into SAS (when discussing database servers). Does SAS Import wizard use ODBC? Are there inherent problems with ODBC? Is there any advantage to having data analyzed in SAS in SAS data sets? Any problems adding a database server in the future? SAS QUESTIONS: Some of the work creating the SAS data entry forms is already completed (by someone outside our agency who is unavailable now). I did not know SAS even had database software until I started on this project. I currently have used SAS only for statistics and have only the intro. Programming course. What SAS training would I need to get up to speed on creating forms and using SAS to manage this type of database? The SAS packages we currently have are: STAT, AF, ETS, IML, SHARE GIS, SHARE*NET, INTEGRATION TECH.,APPDEV STUDIO, AND ACC/ORACLE. Would we need to purchase other packages to use SAS as our database software? The last piece of advice given to me at the meeting was "keep it simple." Obviously I am not a database administrator (nor am I paid like one). I am an entry level biologist that has used MS Access to manage a desktop database. I don't mind learning the SAS database system if it provides a better solution. OR IS THERE A BETTER SOLUTION OUT THERE? Should we consider hiring a consultant? I am posting this to both the SAS and ACCESS user groups to get the range of responses. Thanks in advance and sorry for the long post, Rob