ADP support in Access 2010

Question

We are upgrading to Access 2010 from 2003 soon and I have been tasked with creating another small application.  For several projects I have used Access as a front end for a SQL database and have used the adp file type to do this. 

Should I continue to utilize the adp file type or switch to a different method?  I can't find any concrete information from MS about the future of adp but I did notice that it is a somewhat hidden file type in 2010 and it is listed as a 2002-2003 file format.  I'm looking for input from other folks who have made adp programs in the past and what they plan to do in the future. 

If someone can point me to information from MS about the future of adp it would be greatly appreciated.

Answer 1

Microsoft has not upgraded ADP since the A2003 timeframe. I think the writing is on the wall. They are also promoting using accdb with odbc-linked tables. In this environment passthrough queries are supported.

---

-Tom. Microsoft Access MVP

Answer 2

Tom,  That's the general vibe I'm getting, which is why I started this topic. 

If I switch to a linked tables accdb will I

1) be able to also link to stored procedures? 

2) Will security from the SQL server be enforced?

3) When a user opens the accdb with linked tables is the entire table loaded when the program starts or is Access only retrieving the currently requested records?

2 & 3 are some of the main reasons I began to develop ADPs.

Maybe I was just misinformed along the way, but I was under the impression that a standard mdb (now accdb) with linked tables did no allow for these features.

Answer 3

 

 

"neilo13" wrote in message news:[e-mail]...

Tom,  That's the general vibe I'm getting, which is why I started this topic. 

If I switch to a linked tables accdb will I

1) be able to also link to stored procedures? 

 

Yes, but you can't edit the results. You have to create a pass-though query, so the SP's don't appear as a an option when linking. However, views do, and I tend to prefer views in places of SP's anyway. (I mean, why link to the SP that just a sql query anyway? That really almost like writing VBA with some sql inside of the Sub you write each time. There very little need to do this, but you can if you wish.

 

 

2) Will security from the SQL server be enforced?

 

Yes, it don't matter if Excel, word, Access or a web site is pulling data from sql server, the security settings of sql server don't change in regards to the client pulling out the data.

 

 

 

3) When a user opens the accdb with linked tables is the entire table loaded when the program starts or is Access only retrieving the currently requested records?

2 & 3 are some of the main reasons I began to develop ADPs.

Maybe I was just misinformed along the way, but I was under the impression that a standard mdb (now accdb) with linked tables did no allow for these features.

 

That is incorrect. (so, yes you been misinformed). In fact, even when you not using sql server, and ask a standard split access database to pull out one customer record, the whole table is not pulled. So, even without sql server, this is NOT the case. So, in the case of without sql server, or when using linked tables to SQL server, only the one record is pulled and not the whole table.

 

This whole table issue is often miss-reported. I think ADP's are more forgiving in terms of writing queries then that of linked tables. (it harder to mess up a query built in an ADP as opposed to using linked tables in terms of performance). For complex queries with group-by + aggregates, I create a view in sql server, and then setup an link in access to that view.

 

--
Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada
[e-mail]

 

 

Answer 4

Re 1: No. But you can execute stored procedures, and capture the data in a recordset. You can link a form to a view.

Re 2: Of course.

Re 3: Access is smart about retrieving the minimum needed. If there is an index it will use it to fetch the subset.

---

-Tom. Microsoft Access MVP

Answer 5

Yes, but you can't edit the results. You have to create a pass-though query, so the SP's don't appear as a an option when linking. However, views do, and I tend to prefer views in places of SP's anyway. (I mean, why link to the SP that just a sql query anyway? That really almost like writing VBA with some sql inside of the Sub you write each time. There very little need to do this, but you can if you wish.

 

I have a lot of SP's written to run queries with user entered parameters.  As far as I know views cannot have variables.

How would I achieve this type of functionality using a accdb?

Answer 6

I have a lot of SP's written to run queries with user entered parameters.  As far as I know views cannot have variables.

How would I achieve this type of functionality using a accdb?

accdb's (and .mdb's since Access 1.0) support parameter queries. You can base a parameter query on a linked table or on a linked view, and the query engine will make the best use it can of indexes on the view or on the table.

There have been a LOT of false impressions - and for that matter outright lies - about Access being a "toy" database; the one about "pulls down the entire table" has never been true but is very common.

 

---

John W. Vinson/MVP

Answer 7

Are you talking about filters for reports?

 

I just go:

 

dim   strWhere       as string

 

 'select sales rep combo

 

if isnull(cboSalesRep) = false then

 

   strWhere = "SalesRep = " & cboSalesRep & ""

 

end if

 

 'select what City for the report

 

if isnull(cboCity) = false then
   if strWhere <> "" then
      strWhere = strWhere " and "
   endif
   strWhere = strWhere & "City = " & cobCity & ""
end if

 

docmd.OpenReport "MyReport",acViewPreview,,strWhere

 

Note how the 2nd combo test is setup and is optional (so, user can leave out the value). This FAR MORE flexible then having to pass parameters to a form/report. Thus you can on the fly add as "many" more conditions you want (and without knowing ahead at query design time). It also gets rid of huge amounts of queries (and sp's) in the application as now you don't need to build a new SP every time you want a filter on a report.

 

So, passing a "where" clause when opening a report (or form) eliminates the need for parameters anyway.

 

It not clear if you were talking about reports/forms and restricting the output as above, or passing values to some SP for processing of data? In the case of SP's that don't return values, then I just go:

 

dim qdf as DAO.QueryDef
Set qdf = currentdb.QueryDefs("qryExecuteMySP")
qdf.SQL = "exec MySP "
qdf.execute

 

Or:

 

qdf.SQL = "exec MySP " & "param1" & "," & strP2 

 

In fact, in code, if there is no params to the sp, then I just go:

 

Currentdb.QueryDefs("qryPassSetup").Execute

 

And, in code, I often don't even declare ANY variables. I go:

 

   With CurrentDb.QueryDefs("qryPass")
      .SQL = "exec sp_cust1 'Kallal'"
      .Execute
   End With

In the above, I assume that you created ONE pass-through query that you can then use for the whole application, and you can stuff/use any SQL text you want. So the above sql text is not limited to the exec command and passing of parameters. You can well execute any sql command you could/would type in sql management studio. And, the above shows you re-use that one pass-though over and over, and not mess with even having to declare a variable in code. This approach not only eliminates declaring variables, but also eliminates the need for a zillion links to a zillion sp's

 

--
Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada
[e-mail]

 

Answer 8

John, I was unaware that you could have parameter queries with linked tables.  I just assumed it could not work, never tried it.  I started learning databases with Access and have found that it does a lot more than most people think.  The real hurdle for me has been multi-user, multi-site applications.

Albert, I am talking about creating parameterized queries (or stored procedures) then basing reports on those.  In a way you are just doing the inverse of that and instead using Access' report filtering capabilities instead of saving the query.  I think either way code needs to be changed somewhere if additional parameters are needed, so it looks like a matter of personal preference.  I do appreciate your input and may try and use your approach in the future.

Here's another question: When using linked tables I had to setup an ODBC driver/source on my machine.  Will I have to do this on each client machine?  That adds some extra and manual steps to rolling out the application.  I see that Access 2010 has some options for publishing the application as a self-installing exe file.  Would that include the ODBC configuration?  If not that may be a good reason for me to continue using ADPs.

Answer 9

Yes, no question about the parameters - this mileage and how well this works for you will vary.

 

 

"neilo13" wrote in message news:[e-mail]...

Here's another question: When using linked tables I had to setup an ODBC driver/source on my machine.  Will I have to do this on each client machine?  That adds some extra and manual steps to rolling out the application.  I see that Access 2010 has some options for publishing the application as a self-installing exe file.  Would that include the ODBC configuration?  If not that may be a good reason for me to continue using ADPs.

We usually setup our own linking code and use a DSN-less connection. Sample here:

 

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

However, there is ONE BIG downside, and that is the uses password is stored in the table links (and it not encrypted). Now, if your application in general does a good job of hiding the access interface, then this is not too big of a problem. However, in some cases this is a real deal breaker for some. In this regards, the ADP is better.

 

About the only solution to the above is to use a DSN, which is a pain.

 

So, in most cases, we distribute the application, and do us a DSN-less linking on startup.

 

--
Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Answer 10

I've discovered that linked tables will not work with fields of the bigint data type (needed for numbers over 2.1 billion) and  I have several id fields that use numbers in this range.  In addition to setting up ODBC I need to use an ADP. 

Thank you all for your input on this issue.  I hope this thread can assist others in the decision between linked tables and ADP.