Update a Query through VB Code? 1

[Mavors]

Hello all!

I am trying to find a way to change a Access query using only Visual Basic. I am missing a field in one of my queries and would like to be able to change the query or maybe delete the old one and replace it with a new one.

Any Ideas?

Thanx in advance,
Mavors

 

[ciaranr]

look at ADOX and its capabilities. search in msdn.microsoft.com for help on this matter.

Basically utilizing ADOX (a set of ADO) will achieve your goals. there is a great article at

http://msdn.microsoft.com/library/techart/daotoadoupdate_topic6.htm

good luck

ciaran
ciaranr@albany.jrc.net.au

 

[almm]

We delete and recreate a query based on what table the user wants to see. Here is the basics of that code:

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef

'Create the new record set based on our new SELECT statement
Set dbs = OpenDatabase(App.Path & "\safety.mdb&quot
'Delete the old query
dbs.QueryDefs.Delete "ReportQuery"
'Recreate the query with the new criteria
Set qdf = dbs.CreateQueryDef("ReportQuery&quot
qdf.SQL = "SELECT * FROM " & TABLE & strCriteria & " ORDER BY Year, Month ;"
'Open the query recordset
Set rst = dbs.OpenRecordset("ReportQuery&quot

Of course, your "qdf.SQL" statement will be the SQL that creates the query you are looking for.

Hope this helps!

 

[MichaelRed]

Mavors, almm

Just a thought (or two?) on the soloution.

You really do not need to delete the "old" query, it will be replaced wheather or not the old query is deleted.

If (as noted in the post), you only need to do this operation a single time, it is probably easier to just open up Ms. Access w/ the db, open the query in design view, add the field and save the query.

If it is a 'exercise' or needs to be done often (or on a LOT of queries), you may find it easier to get the "Current" sql property, and then parse the existing sql string to get the correct location and add the new field in the string before re-assigning the string to the SQL property.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Mavors]

Thank you all for the great help! Ciaranr, what a great page. I have been going thrugh the msdn for anything and everything and have found nothing till now.

I should have mentioned this before, but it figures that I would forget something. I have to do this in ADO.(requirement from above) I am finding now that while I can creat the query I can not find a visible copy of it in access. I know it is there since I can use it in VB. Is there a reason that it is not visible and might there be a way to create it so that it is also usable within Access?

Thanx,
Mavors

 

[MichaelRed]

Mavors,

It may (or MAY NOT) be there. Depends on HOW (and where?)you create it. From VB, you could just set it to a "recordset" based on the querydef object where you have manipulated teh querydef.sql. VB WOULD care less how the final recordset was created - as long as it was "In Scope".

Another (of MANY) options. You have more than one db "dimmed" in VB - and what you are creating is in some "other" db.

To create "it" in Ms. Access (MDB file), you need to explicitly declare the data base (Dim & Set) and make sure that the Quserdef object is also explicitly declared (Dim And Set) as part of THAT db object.

Again, I am somewhat confused as to the reason(s) behind doing this "in Code". It is much easier to modify the "Query" in Ms. Access using the 'querybuilder' than to generate the code. This would also clearly leave the query where you can "see" and "use" it from within Ms. Access. Unless the process is 'an exercise', it resembles "using an elephant gun as a fly swatter".




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ciaranr]

mavors

I can see quite clearly why you would want to modify the query in VB Code if, and only if, you are inexperienced in adcmdtext recordset.open command strings. Try something different. If the system that you are using runs under VB on an access database, get use to using either stored parameterised procedures or compile your query in access, view the sql, copy it and paste it into your .open statement. this means that you do not need to learn the finer points of jet sql and you restrict your queries to simple code. I prefer to open record sets (joined and all) with and only with code and use the Jet database for data storage only.
Maybe if you define you requirements of the database as separate to your application then you will progress one more step in the heady world of database application writing.

One more thing. If your application is based within access then you have every right to use vb to modify your query.

Oh - and finally. Some will say that memory allocation is more efficient with stored procedures and fewer global definitions and the like. listen not! Having developed many systems I have only once needed to restict memory usage but that was a monster critical project that required a team of 5 to complete. Dont worry. Work with ADO (its more fun) and do have fun.

regards

ciaran

ciaran
ciaranr@albany.jrc.net.au

 

[Mavors]

Well MichaelRed the reason we need to do this is that we have sold a product and do to some changes need to change a couple base queries. These queries need to be in the database so that we can place them in other queries that we create through code or even utilize with yet other queries in access.

Ciaran, I'd like to personally thank you for being thoughtful and curtious in your responses to a beginning ADO programmer like myself. I have only been programming for 2 years and have just got into ADO as well as access programming so much is still new to me. Again thank you for your time and aid.

Man this has become a harder question than I thought, not only to ask, but to answer.

Bring back my good ol' RPG!!!....<chuckles>

Thanx again,
Mavors