Solved

I am trying to change a bunch of queries (200 of them) to be snapshots not dynasets.  can i do this in code?

Posted on 2008-06-16
9
353 Views
Last Modified: 2008-06-16
I am trying to change a bunch of queries (200 of them) to be snapshots not dynasets.  The property is the recordset type.  Can i change them all at one time from code?  all the queries names have the first character as an underscore.

I think i should cycle through the queries in code then change the recordset type attribute for each.  but i don't know how to get to that attribute.  I declared an object as allqueries but it doesn't have that attribute.

any help would be great.
0
Comment
Question by:saxophobe60
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21796461
Doing this from a command button cmdDyna2Snap on a form, enter this code in the click event:

Private Sub cmdDyna2Snap()
Dim db as DataBase, qds a querydef, qd as querydef, qcount as Integer, i as integer
Set db=CurrentDB
qcount=db.querydefs.count

for i=0 to qcount-1
qd=db.querydefs(i)
if left(qd,1)="_" and qd.type = 2
  qd.Type = 4  ' opendynaset = 2
end if
Next i

db.close
End Sub
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21796476
Lets try that again:

Private Sub cmdDyna2Snap()
Dim db as DataBase, qd as querydef, qcount as Integer, i as integer
Set db=CurrentDB
qcount=db.querydefs.count

for i=0 to qcount-1
qd=db.querydefs(i)
if left(qd,1)="_" and qd.type = 2
  qd.Type = 4  ' opendynaset = 2
end if
Next i

db.close
End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21796484
the properties snapshots and dynasets are for recordsets not for queries.

you can use the queries to open recordset like this

dim rs as dao.recordsets

set rs=currentdb.openrecordset("queryName",dbopensnapshot)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:saxophobe60
ID: 21797264
I changed it to a function so i wouldnt have to make a button on a form but it tells me that
 qd.Type is a read only property.

when this code runs
qd.Type = 4   ' opendynaset = 2



Function UpdateUserQueriesToSnapShots()

    Dim db As Database, qd As QueryDef, qcount As Integer, i As Integer
    Set db = CurrentDb
    qcount = db.QueryDefs.Count
   
    For i = 0 To qcount - 1
    Set qd = db.QueryDefs(i)
        If Left(qd, 1) = "_" And qd.Type = 2 Then
   
         qd.Type = 4   ' opendynaset = 2
        End If
    Next i
   
    db.Close
End Function
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21797420
saxophobe60: I sent you a post an hour ago acknowledging the observation of capricorn1 and advising you to disregard my second post.  cap is right - those enumerations are only for recordsets - they are not the property of a query definition.  
0
 

Author Comment

by:saxophobe60
ID: 21797515
I can see them as properties of the querdefs.  if you open one in design mode and open the properties the "RecordSet Type" is a property.  

It acknowledges them as properties but says they are read only.  i thought if i open them in code to design view i could access them somehow there.  

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 21797725
hmm .looks like we  can change it using codes

try this


On Error Resume Next
Dim qd As DAO.QueryDef, prp As Property

For Each qd In CurrentDb.QueryDefs
    If Left(qd.Name, 1) = "_" Then
    DoCmd.OpenQuery qd.Name, acViewDesign
        For Each prp In qd.Properties
            If prp.Name = "recordsettype" Then
            prp.Value = 2
            Exit For
            End If
        Next
   
    DoCmd.Close acQuery, qd.Name, acSaveYes
    End If
Next



0
 

Author Comment

by:saxophobe60
ID: 21797938
Excellent that worked!
You da Man!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21798610
Strange, I cannot find a property name RecordSetType in a query opened in acViewDesign
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question