ADP or MDB

[HBWAL]

I need to make a choice between a mdb linked to my sql server or an ADP. At the PRESENT time the objectives are to process incoming data, analyze it, and report it (using excel or whatever). The goal is to use Sql Reporting Tools in the future but meanwhile I need to choose. My data is getting to large for Access and I need to process and analyze it sql and let the end-user choose how to report it. A great deal of the reporting is in Modules in the existing mdb. In your opinion, which is the better option and why?

 

[BobJacksonNCI]

It depends: (of course this is MHO)

I have Access 2000 and SQL 7 ... and have wrestled with numerous workarounds due to Access 2000 being the first version that offered ADP.

If you have Access 2000, I'd stick with MDB and link to SQL tables.


If your version of Access is newer:

Then it mostly depends on your knowledge level. ADP will process faster, but then you have to handle both dialects, Access and SQL. Making a distinction here between SQL code view in an Access query design view versus the syntax differences to deal with when you write Stored Procedures to be run on the server. In case you're not SQL server experienced, a few of the differences you immediately run into include: No IIF, % instead of *, single quotes instead of double quotes and quotes instead of # to identify dates. Then a different way of passing parameters from forms to the procedure. Nothing overwhelming, so far, but enough to learn that you have to budget more development time.

If you're already familiar with SQL - ADP. If you have the luxery of extending implementation dates while you learn - ADP. Otherwise, brace yourself for longer hours with ADP.


HTH,
Bob

 

[cmmrfrds]

Generally speaking, an access project, is a client server implementation to sql server. It uses an OLEDB connection to sql server and makes it easy to bind stored procedures to Forms and Reports. So, it restricts the developer from treating sql server like a file server as can be done with an access mdb. You can achieve treating an mdb as a client to sql server by NOT linking sql server tables, instead connect through OLEDB using ADO and binding recordsets to your Forms and Reports. Since, I use access 2000, it is not easy (or as robust) to do this through an mdb. It is my understanding that with access 2002 and above it is easier to bind Forms and Reports to recordsets. Another thing, is that an adp is not multi-user, but then an mdb should not be multi-user either. So, either can work effectively, but the biggest thing is how free of access you have to the sql server environment including tools such as Query Analyzer and Enterprise Manager. If you are controlled by a restrictive DBA either access product will be more difficult than the single mdb environment.