Re: Run action query in back-end from front-end database
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx>
- Date: Wed, 20 Dec 2006 08:59:47 -0500
DAO was designed specifically for use with Jet databases (i.e.: MDBs or
MDEs), ADO is a more generic approach.
Since DAO was designed specifically for use with Jet databases, it's almost
always going to be more efficient when going against Jet databases (you go
through more layers of abstraction using ADO).
You might also check what MichKa wrote on this subject at
http://www.trigeminal.com/usenet/usenet025.asp
And I still don't buy your argument about why you have the queries in the
backend, but that's your decision!
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Adam" <Adam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EBEC9A16-A736-408D-9C10-652414CD30C0@xxxxxxxxxxxxxxxx
Doug,
Thank you for your help. I am currently using an ADO connection. Is
there
an advantage to using a DAO over and ADO?
To clarify:
I have users who use excel uploads to input their financial forecasts into
the database, text files downloaded from SAP, .dbf files from COGNOS, and
whatnot in the back end database. The queries are housed in the back end
because the calculating and manipulating of this data is done using a few
hundred queries (complicated process). I don't want to reference foreign
tables with each action/union/select query.
The reason I have the upload tables in the back end is that it will be on
one server which everyone will be able to access, but the upload files
will
be on a server that only the User (modify data) group will be able to
access.
I could disconnect the tables based upon their security level, but it is
easier with the tables and queries located in the back end.
Thank you once again for all your help!
"Douglas J. Steele" wrote:
"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
.
- 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
- From: Douglas J. Steele
- Re: Run action query in back-end from front-end database
- From: Adam
- Re: Run action query in back-end from front-end database
- Prev by Date: Re: Accessing remote data using "IN" clause in query with INNER JO
- Next by Date: Re: Accessing remote data using "IN" clause in query with INNER JO
- 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
