Re: Run action query in back-end from front-end database
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx>
- Date: Tue, 19 Dec 2006 21:14:52 -0500
"Does not work" doesn't give much to go on...
Rick's code uses DAO, and Access 2000 doesn't have a reference set by
default to DAO.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library
If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)
The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
I can understand not wanting "upload tables" (whatever they are) in the
front-end database, but what does that have to do with where the queries are
located? (If by "upload tables" you're talking about temporary tables,
consider putting them in a temporary database. Tony Toews has an example at
http://www.granite.ab.ca/access/temptables.htm)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Adam" <Adam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3FC84E53-A859-4C32-AB8C-539822A9806B@xxxxxxxxxxxxxxxx
Rick,
Thank you for your prompt response. The OpenDatabase function did not
work.
I am using Access 2000. Will this make a difference?
The reason I have many queries in the back end database is because it does
many financial calculations (taking raw text files/ODBC connections/Excel
upload files/etc., converting to proper format, then converting to Euro,
then
splitting into different views, etc.). I don't want to have all of these
queries and upload tables in the front end database.
Thank you once again for your help.
Adam
"Rick Brandt" wrote:
Adam wrote:
I have a back-end database that has many linked tables and queries to
update my raw data and then make report tables. Is there any way to
call action queries in the back-end from the front-end using VBA? I
will want to run certain queries based upon form choices.
Access queries are always executed on the local PC so there is no reason
to
store them in the back end (which should strictly have tables).
You could open the BE database in code, and then use the Execute method
of
the database object to run a QueryDef that is stored in that file...
Dim db as Database
Dim qry as QueryDef
Set db = OpenDatabase("Path to BE file")
Set qry = db.QueryDefs("NameOfQuery")
db.Execute qry, dbFailOnError
....BUT, this would be exactly the same result as having the query stored
in
the front end file and just running it against the links.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
.
- Follow-Ups:
- References:
- Re: Run action query in back-end from front-end database
- From: Rick Brandt
- Re: Run action query in back-end from front-end database
- Prev by Date: Re: Need to turn off key violation warning.
- Next by Date: Re: Accessing remote data using "IN" clause in query with INNER JOINs.
- Previous by thread: Re: Run action query in back-end from front-end database
- Next by thread: Re: Run action query in back-end from front-end database
- Index(es):
Relevant Pages
|
Loading
