Is there any reason that I shouldn't be making this report tool as an .adp?
I've just never dealt with this before, but if it works............
I personally use much prefer the ADP format for working with SQL server and I,ve been using MDBs for yeeeears. I see no reason for NOT using the ADP format for producing your reports with a possible exception of crosstab reports which you
'can' do but its not quite so easy (unless of course you have SQL server 2005 which goes some way to addressing that using pivot etc).
Processing is mostly centralised on the server as opposed to the client, your data is centralised making it available for other things should you so require it and the list goes on.
There are fundamental differences in both formats, one relies on what is called the JET engine thats Access standard 'brain' if you like where,
not always but mostly processing is done on the client machine whereas the other (ADP format) communicates directly with the SQL Server using a standard UDL file connection from the client machine with SQL server returning only those records from the server that you need. This is for me absolutely the way to go when on a network with multiple users.
Tables, Queries, views and stored procedures are all centralised on the SQL server making for easier mainenance. The ADP merely looks at them making alterations if so required from the client in order to return your data.
You currently have a connection problem with ODBC it seems and none with UDL
For me ADP has provided for much more stability on a network particularly across a WIDE area network with multiples of concurrent users hitting on the database, never experienced corruption problems, a horrible feature of MDB (it which never intended for more than a few users using at any one time), much quicker etc etc etc.
In fairness to advocates of the MDB You can STILL communicate with SQL server with an MDB using what is called 'pass through' queries. But I simply say if the MDB were so easily successful and seemless why did Microsoft deploy the ADP format strongly advocating useage when working with server supplied records.
Access 2007 on the other hand, prefers the MDB format (arguably a step backwards some say but in actual fact JET was intended for Access when manipulating data in code on the client machine) I'm afraid to say that sometimes its simply a case of whose football team are you on as there are passionate supporters for both and fears from both camps as to whether Microsoft are going to DUMP one or the other formats as they did Visual basic 6
Myyyyyyyyyy this can be a techie debating parliament can't it
You simply want to get the job done!!! I can see that. I suggest you have a go creating some simple reports using the ADP investigate it, if you have the time that is, have a look at how to create a query (view) etc and eventually a stored procedure. Change or new ways of doing things is never for the faint hearted but then again the same was said the first time one picked up a mouse.
The language is a consideration with the ADP you will be using what is called TRANSACT SQL which is SQL servers language to create your queries or VIEWS as they are called and the same on STORED PROCEDURES. If you are merely a standard user you might say phew! what a learning curve but if you are power user (report creator,query maker etc etc) you might well appreciate it and certainly if you have a penchant overall for data handling on the technical side then knowing SQL server language is a positive advantage.
The differences precisely one could write a novel about, what I suggest you do is that you google
'Access versus SQL Server' or
'MDB versus ADP' there are a mirad of topics out there debating the differences and preferences.
The short answer to your question......Yes....your reporting tool can be done with the ADP
I hope this helps you
Jim
6
