Can you give me a small piece of DAO/ADO code on QueryDefs? 
Author Message
 Can you give me a small piece of DAO/ADO code on QueryDefs?

From a VB6 front end, I need to replace the SQL in a query which is in the
Access97 back end MDB with some new SQL. Do I have to create a new temp
query, delete the old one and rename the new to the old??? Or can I just
overwrite the SQL?

Anyway, can someone please post the code I might need to do this
programmatically or send me to a website, book, or someplace?

Thanks,

Paul



Fri, 02 Nov 2001 03:00:00 GMT  
 Can you give me a small piece of DAO/ADO code on QueryDefs?
Is there any special reason why you need an Access query? Could you just
create a query on the fly and use it? If you need to persist the data, you
could create a temp table using Automation. Or could you use a parameterized
query and use variables for the parameter(s)?
Quote:

>From a VB6 front end, I need to replace the SQL in a query which is in the
>Access97 back end MDB with some new SQL. Do I have to create a new temp
>query, delete the old one and rename the new to the old??? Or can I just
>overwrite the SQL?

>Anyway, can someone please post the code I might need to do this
>programmatically or send me to a website, book, or someplace?

>Thanks,

>Paul



Sun, 04 Nov 2001 03:00:00 GMT  
 Can you give me a small piece of DAO/ADO code on QueryDefs?

Quote:

>From a VB6 front end, I need to replace the SQL in a query which is in the
>Access97 back end MDB with some new SQL. Do I have to create a new temp
>query, delete the old one and rename the new to the old??? Or can I just
>overwrite the SQL?

>Anyway, can someone please post the code I might need to do this
>programmatically or send me to a website, book, or someplace?

>Thanks,

>Paul

Paul,

Let's assume your data control is dt1 and that your primary SQL is SELECT *
FROM [SALES]. On the Click event of a button, let's say you want to change
the SQL to find all records with a TotalSales of 100, this is what I would
do:

'declare a common SQL string in general declarations e.g.
dim SQLString as string
SQLString = "SELECT * FROM [SALES]"

On the Form.Activate() event you would open a recordset where the SQL
matched that of SQLString using the following code:

dt1.recordsource = SQLString
dt1.refresh        'open recordset
dt1.recordset.movefirst    'move to first record

Then on a query button change use the following code at the start:

dt1.recordsource = SQLString & "WHERE [SALES].[TOTAL] = 100"
dt1.refresh
dt1.recordset.movefirst

Remember to change the recordsource back using the same code as in the
Form.Activate() event when you have finished querying your database.

If your giving the user the option of a sales total then use the following
code instead:

dim answer as integer
answer=0
answer=inputbox("Please enter a number")
dt1.recordsource = SQLString & "WHERE [SALES].[TOTAL] = " & chr(34) & answer
& chr(34)
dt1.refresh
dt1.recordset.movefirst

I hope this helps.

Huw



Thu, 08 Nov 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Can you give me a small piece of DAO/ADO code on QueryDefs?

2. Can someone debug this small piece of code?

3. Can someone debug this small piece of code?

4. Convert DAO Querydefs to ADO

5. Crystal Reports Canned Code

6. QUESTION: DAO QueryDefs Collection

7. Migrate from DAO Querydefs to ADODB Procedures

8. DAO to ADO coding help :(

9. If Not Found Then: FindRecord code in DAO/ADO

10. Can't access DAO/ADO DC Recordset From Code

11. Converting DAO code to ADO 2.0, problem encountered

12. help with this piece of code

 

 
Powered by phpBB® Forum Software