473,692 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating Queries in DAO vs ADOX

Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.App end "qryTempRptOrde rMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

--
Regards,

Greg Strong
Nov 13 '05 #1
44 4555
I never use ADOX for creating queries (I never use ADOX for anything).

Private Sub CreateQuery(ByV al Name As Variant, ByVal SQL As String)
With CurrentProject. Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "Create Procedure " & Name & " AS " & SQL
End With
End Sub

Sub test()
CreateQuery "QueryTemp" , "SELECT * FROM Table1"
End Sub

Of course, one can also specify parameters as in (from the help file):

CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

*****

CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

(TTBOMK this has the same result as

CREATE Procedure procedure [(field1[, field2[, ...]])] AS
selectstatement

*****

I have, of course, a reference to an up-to-date version of ADO.

Nov 13 '05 #2
Perhaps you can explain why you wanted to port everything to ADO when it's
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?

Also -
Do your queries really need to be created at runtime? Wouldn't
parameter queries do the trick?

"Greg Strong" <NoJunk@NoJunk4 U².com> wrote in message
news:qn******** *************** *********@4ax.c om...
Hello All,

Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?

From what I've read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?

Why do I ask the question? I've created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:

catDB.Views.App end "qryTempRptOrde rMTD", cmd1

The error reads as follows:

Run-time error: Object or provider is not capable of performing
requested operation.

After stepping out of the line in VB it appears the code runs to
completion stopping in the 'end if' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp. Hence my original question.

For the original error I've created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I'm see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I'm still back to
square one and my question.

TIA!

--
Regards,

Greg Strong

Nov 13 '05 #3
On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott " <ma********@nos pam.com>
wrote:
Perhaps you can explain why you wanted to port everything to ADO when it's
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?


To learn of the differences. I had working MDB with DAO so decided to
make switch. Anyhow from what I've read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.

--
Regards,

Greg Strong
Nov 13 '05 #4
On 5 Nov 2005 15:59:54 -0800, "lylefair" <ly***********@ aim.com> wrote:
I never use ADOX for creating queries (I never use ADOX for anything).


From what I recall reading DAO is no longer being developed. As you
know I'm learning, so I thought I'd try to change everything over to
ADO. So if you have to create a query that is the record source of a
report what do you use? TIA!

--
Regards,

Greg Strong
Nov 13 '05 #5
CurrentProject. Connection.Exec ute "CREATE PROCEDURE QueryName AS
SELECT Redheads, Telephone FROM Girls WHERE Proclivity = 'Many'"

Nov 13 '05 #6
On 5 Nov 2005 15:59:54 -0800, "lylefair" <ly***********@ aim.com> wrote:
I never use ADOX for creating queries (I never use ADOX for anything).
Ok!
Private Sub CreateQuery(ByV al Name As Variant, ByVal SQL As String)
With CurrentProject. Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "Create Procedure " & Name & " AS " & SQL
End With
End Sub

Sub test()
CreateQuery "QueryTemp" , "SELECT * FROM Table1"
End Sub


I used this for my code with minor changes. Works fine.

Back to the my original trend of thought. Do you use DAO or ADO when
you have to create queries in code? Or does it depend upon the
circumstances? BTW we are even up in the humor department. :)

TIA!

--
Regards,

Greg Strong

Nov 13 '05 #7
I use ADO by choice for everything or almost everything. It has many
capabilities that DAO does not have. It can be used easily within other
technologies such as ASP, HTAs and JScript, so I do not have to switch
from DAO to ADO when using these; I just always use ADO. It can be used
to connect to and modify MS-SQL directly through OLEDB connections. Its
providers enable FTP-like file manipulation, recordset saving to XML or
text files (and loading from same as well), and querying Indexing
Service Catalogs. Its Schema provide almost complete information about
the database to which it is connected. It can disconnect its
recordsets, modify them, reconnect and batch update the changed
records. Even within JET, it has some capabilities in column creation
that DAO does not. Its objects are released without the SET
Object=Nothing requirement of DAO.
When dealing with JET, DAO may have a minimal speed advantage. I am
unaware of any situation in my work where this speed advantage is
noticeable.

I have programmed very extensively in DAO, but I've stopped doing so.

I have programmed very extensively in ADO and I continue to do so.

Nov 13 '05 #8
Greg, that information is out of date. Microsoft was pushing that line 5
years ago, so will still read it some places. Back then, they dropped the
DAO library as a default on new A2000 and A2002 databases. In Access 2003,
DAO is back by default.

ADO is now dead (replaced by the very different ADO.NET), and DAO lives on.

There are some things you can only do in DAO, such as setting the Format or
DisplayControl of a field. There are some things you can only do in ADOX,
such as setting the Seed of an Autoincrement column. There are some things
you can only do via DDL, such as changing the data type of an existing
field.

Unfortunately, this means that if you want to know everything, you have to
learn the entire hotchpotch. But if you are working with Access (JET) tables
in an Access database (mdb), DAO is the native Access library, and will
therefore be the most natural approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Greg Strong" <NoJunk@NoJunk4 U².com> replied in message
news:au******** *************** *********@4ax.c om...
On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott " <ma********@nos pam.com>
wrote:

... Anyhow from what I've read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.

Nov 13 '05 #9
On 5 Nov 2005 19:33:51 -0800, "lylefair" <ly***********@ aim.com> wrote:
I have programmed very extensively in DAO, but I've stopped doing so.

I have programmed very extensively in ADO and I continue to do so.


Well I guess this says it all. Thanks for all of the information.

--
Regards,

Greg Strong
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7277
by: JBAdamsJr | last post by:
I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to save an array in an Excel .XLS format?
1
1818
by: Craig Washington | last post by:
How in code can you use the Microsoft Access Queries with ADO in code? Anyone???
2
6114
by: Gandalf | last post by:
I'm creating relationships between tables using VBA and ADOX. I can create one-to-one relationships with an inner join, but I can't figure out how to create these relationships with an outer join (specifically a left outer join). I'm including the code that creates the relationships with the inner join. Any help or suggestions would be greatly appreciated. Thanks! <----- CODE FOLLOWS ----->
5
2070
by: Wayne Wengert | last post by:
I am getting an error that "object no longer valid" at the point indicated in the code below - I am trying to build a table in an Access 2000 database using ADOX. Any thoughts on what might cause this? ==================== code ================= Sub CreateADOUnitsTable(ByVal sTablename As String) Dim oDB As ADOX.Catalog Dim oUnits As ADOX.Table
18
2461
by: Ken Kazinski | last post by:
Does anyone know of a good example for creating a access database and then tables within that database. All the examples I have found so far use a SQL database. Thanks, Ken
10
2953
by: C# Beginner | last post by:
Hi there, I'm currently trying to create a MS access file at runtime, but I stumble into some problems. 1. When I use Datatype adUnsignedInt I get an error (invalid type). 2. Which datatype must I use to create an autonumbering field? 3. Which datatype must I use to create an OLE-object field? I'm currently using Microsoft ActiveX Data Objects Library 2.8 and using engine type 5.
5
5060
by: BillCo | last post by:
I've encountered a problem while using ADO to save query objects. Union queries created normally (via the interface) appear in adox catelog.procedures rather than catelog.views. This is reasonably well documented and I can live with it. The below seems to be the only way to add queries to a database using ADO: Set cmd = New ADODB.Command cmd.CommandType = adCmdText
4
2910
devonknows
by: devonknows | last post by:
Hi can any one please help me with creating a secure database with ADO or ADOX if possible, ive got this code already but its not accessible through VisData so i cant easily modify it at all. Public Function CreateDatabase() Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim con As ADODB.Connection On Error Resume Next Kill txtDatabaseName.Text
5
6320
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had pass-through queries fail due to intermittent connection failures. I can easily restablish a connection for ADO. My problem is with pass-through queries.
0
8540
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9086
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8963
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8806
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7629
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5820
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4324
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.