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

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.
saxophobe60Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
GRayLCommented:
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
 
GRayLCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rey Obrero (Capricorn1)Commented:
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
 
saxophobe60Author Commented:
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
 
GRayLCommented:
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
 
saxophobe60Author Commented:
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
 
saxophobe60Author Commented:
Excellent that worked!
You da Man!
0
 
GRayLCommented:
Strange, I cannot find a property name RecordSetType in a query opened in acViewDesign
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.