By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Using SQL Server functions for a report in a linked MDB

P: n/a

I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC
linked tables. The MDE is shared among approximately 20 users.

This is the environment; I don't have the ability to change it at this
time.

New reporting requirements really need to use an UDF to supply the
result set. Which I believe requires a pass-through query.

After changes are made to the development MDB by the development staff
and approved for deployment, the MDE is compiled, the table links are
updated to point to the production database, and the shared MDE is
replaced.

But the pass-through queries wouldn't be updated with the existing
workflow and I'd like to have a technique to ensure that the queries
are updated.

I can examine the connection strings against the linked tables' and,
assuming that the tables' connection are correct, identify the need to
update the query connections.

But I'm concerned that updating the query connection string via VBA
will cause problems with sharing the MDE, even though it would only
happen the first time someone tried to run a report that used a
pass-through query.

Has anyone had a similar problem and, if so, how did you resolve it?
Apr 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
me******@comcast.net wrote:
I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC
linked tables. The MDE is shared among approximately 20 users.

This is the environment; I don't have the ability to change it at this
time.

New reporting requirements really need to use an UDF to supply the
result set. Which I believe requires a pass-through query.

After changes are made to the development MDB by the development staff
and approved for deployment, the MDE is compiled, the table links are
updated to point to the production database, and the shared MDE is
replaced.

But the pass-through queries wouldn't be updated with the existing
workflow and I'd like to have a technique to ensure that the queries
are updated.

I can examine the connection strings against the linked tables' and,
assuming that the tables' connection are correct, identify the need to
update the query connections.

But I'm concerned that updating the query connection string via VBA
will cause problems with sharing the MDE, even though it would only
happen the first time someone tried to run a report that used a
pass-through query.

Has anyone had a similar problem and, if so, how did you resolve it?


Your problem is that you are sharing a common MDE. Each user should have their
own local copy of the MDE. Then making modifications to queires
programmatically is no problem at all.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 26 '06 #2

P: n/a
> But I'm concerned that updating the query connection string via VBA
will cause problems with sharing the MDE, even though it would only

No more problem than updating any other shared data in a shared database.

(david)
<me******@comcast.net> wrote in message
news:pk********************************@4ax.com...
I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC
linked tables. The MDE is shared among approximately 20 users.

This is the environment; I don't have the ability to change it at this
time.

New reporting requirements really need to use an UDF to supply the
result set. Which I believe requires a pass-through query.

After changes are made to the development MDB by the development staff
and approved for deployment, the MDE is compiled, the table links are
updated to point to the production database, and the shared MDE is
replaced.

But the pass-through queries wouldn't be updated with the existing
workflow and I'd like to have a technique to ensure that the queries
are updated.

I can examine the connection strings against the linked tables' and,
assuming that the tables' connection are correct, identify the need to
update the query connections.

But I'm concerned that updating the query connection string via VBA
will cause problems with sharing the MDE, even though it would only
happen the first time someone tried to run a report that used a
pass-through query.

Has anyone had a similar problem and, if so, how did you resolve it?

Apr 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.