Modifying/Altering an Access 97 Query w/ code 1

[DSect]

Hello.

I have a database that I need to modify alot of queries in. Is there any way to use VBA to modify the queries.

Example: I have a saved Access 97 query named qryTest that is
SELECT MYTABLE.MYFIELD1 FROM MYTABLE;

I want it to be:
SELECT MYTABLE.MYFIELD1, MYTABLE.MYFIELD2 FROM MYTABLE;

Same name, permissions, etc..

If I could get the SQL into a variable, I'd be able to modify it like I need to, but I can't fathom how to:

1. Get the actual SQL of query saved in Access 97 into a variable.

2. Output the modified SQL (from the variable) as a query that shows up in Access.

Thanks in advance!!

 

[SBendBuckeye]

Dump the following code into a procedure and execute it in single step mode. The stuff you don't need just comment out.

Once you get to the first qdf, open the locals window in view mode and dig around in the QueryDef collection. Note below the qdf.name and qdf.sql in the first section. I think that may be what you are looking for.

Good Luck!

Public Sub GetQueryDefInfo()
Dim db As DAO.Database
Dim con As DAO.Container
Dim prp As DAO.Property
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim rel As DAO.Relation
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim prm As DAO.Parameter
Dim strTemp As String

Set db = CurrentDb()
Debug.Print "Database", db.Name

'List the querydefs
If db.QueryDefs.Count > 0 Then
Debug.Print
Debug.Print Tab(5); "Queries", db.QueryDefs.Count
For Each qdf In db.QueryDefs
On Error Resume Next
Debug.Print Tab(10); qdf.Name, qdf.SQL
'List the querydef fields
If qdf.Fields.Count > 0 Then
Debug.Print Tab(10); "Fields",
qdf.Fields.Count
For Each fld In qdf.Fields
On Error Resume Next
Debug.Print Tab(15); fld.Name,
fld.Type, fld.Size
Next
End If
'List the querydef parameters
If qdf.Parameters.Count > 0 Then
Debug.Print Tab(10); "Parameters",
qdf.Parameters.Count
For Each prm In qdf.Parameters
On Error Resume Next
Debug.Print Tab(15); prm.Name
Next
End If
'List the querydef properties
If qdf.Properties.Count > 0 Then
Debug.Print Tab(10); "Properties",
qdf.Properties.Count
For Each prp In qdf.Properties
On Error Resume Next
Debug.Print Tab(15); prp.Name
Next
End If
Next
End If

End Sub

 

[DSect]

I tried stepping through it, but I have to tell you that I am a novice when it comes to alot of this stuff and I had no luck whatsoever with it. I know if I could get the stuff into a useable form for me, I'd be fine, but I am lost ATM.

I guess I have way more to learn before trying this.

Thanks for the help, though.

Back to the drawing board, for me.

 

[SBendBuckeye]

Did it work at all or were you getting errors? If it worked try using this scaled down version. Sorry, I wasn't trying to overwhelm you. It can be daunting at times when you are first learning.

What is happening here is that this code is spinning through the database query definitions one and a time and displaying information about them.

Public Sub GetQueryDefInfo()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strTemp As String

Set db = CurrentDb()
Debug.Print "Database", db.Name

'List the querydefs
If db.QueryDefs.Count > 0 Then
Debug.Print
Debug.Print Tab(5); "Queries", db.QueryDefs.Count
For Each qdf In db.QueryDefs
On Error Resume Next
Debug.Print Tab(10); qdf.Name, qdf.SQL
Next
End If

End Sub

Good Luck!

 

[DSect]

Thanks for the stripped-down version. Now I understand how to get the stuff I was looking for, but I have just one more question regarding this issue.

If I had 2 variables: strQryName and strQryDef

How can I write that out as an Access query?

Also - Am I correct in saying that a "QueryDef" = an Access query, so when I am talking to someone about doing something involving VB and an Access query, I should use the word QueryDef.

Like the title of this message should have been "Modifying QueryDef SQL property" (if that is indeed a property).

Hell, I don't even need to mess with the name. I thought I did, but now it looks like I can just modify the [objectname].SQL to update the queries.

Ok.. Maybe a few more questions:

If I had a query named "MARTHA" in my database, and I wanted to display the SQL of it, how would I do it? I have a hard time with addressing the names of things properly. I understand the FOR..EACH you did for the db.QueryDefs collection, but I don't know how to call or address an individual QueryDef. I am *guessing* that it'd be along the lines of:

Dim db As DAO.Database

Debug.Print db."MyQueryDef'sName (MARTHA)".SQL (psudeocode)
or
Debug.Print db.QueryDef("MARTHA&quot.SQL


Damm.. Anyways I am not frustrated, I am EXCITED, but I do have a hard time figuring out what the exact syntax is and what I should be looking for. You understand newbieness, I am sure.


Thanks tremendously for the help so far. Just by introducing me to the Querydef Object you have given me MUCH starting ground for researching solutions and provided me with EXCELLENT examples to start with. I truly appreciate your time and help!!!

 

[DSect]

Hey ! Disregard most of my last message! I understand the syntaX for fetching the SQL from a QueryDef, it goes as follows:

Dim db AS DAO.Database
Debug.Print db.QueryDefs("MYQUERYNAMEGOESHERE&quot.SQL

Ok. So thatks a MILLION for the previous info, I am still left wondering how to update the SQL now.

Could I do something like:
----------------------------------------------
Dim db AS DAO.Database
Dim strSQL as String

strSQL = "SELECT blah blah blah blah"

db.QueryDefs("MYQUERYNAMEGOESHERE&quot.SQL = strSQL
------------------------------------------------


EDIT: Yes I can! I just tested it. Jeeze, Thanks a ton. It's so exciting when you see the pieces fall together and without your assistance, I'd have been digging for MUCH longer!.

Praise TEK-TIPS!

hehe - Happy Days

 

[SBendBuckeye]

It's always fun to watch the lights go on with a new level of understanding. What you were/are actually doing is working with the various collections of objects that Access makes available to you (eg Forms, TableDefs, QueryDefs, etc).

I would continue to talk about queries and tables instead of querydefs and tabledefs since Microsoft themselves are inconsistent (you'll notice they don't have a Formdefs collection).

A collection has a plural name while an individual object in that collection is singular. So you have a TableDef within the collection of TableDefs, a Form within the collection of Forms, etc.

Here's where it gets cool. A collection can be thought of as an array since you can reference individual objects via an index. But, the object name itself acts as a special case index into the collection.

So, you can always refer to an individual item in a collection by CollectionName("IndividualObjectName&quot. You can use this to very easily access the properties of your items. For example, Forms("FormName&quot.Caption, QueryDefs("QueryName&quot.sql, etc.

Where it really gets helpful is that you can use variable names inside of the parenthesis. So if you were simulating an array of textbox controls on a form (txtText1, txtText2, etc), you could spin through them like the following:

Dim intX As Integer
Dim strForm As String

strForm = "FormName"
For intX = 0 to 9
Forms(strForm).Controls("txtText" & intX).locked =
True
Next intX

What you are doing is referencing the current form in the Forms collection and then using the Controls collection of that form to spin through and make changes.

Put some code into single step mode and open up the view locals window and check out the db object. Anything in the first level with a + next to it is a collection. Check out some of the easier ones like forms, tabledefs and querydefs. You'll learn a lot.

If you keep periodically going back to it, you will get more comfortable with the concepts of the collections. You spin them by using the For Each construct with a variable defined as a single object of the collection.

You're on the right track! Keep up the good work and let me know how your project turns out.

Good Luck!

 

[DSect]

Thanks again!