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
366 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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

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!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

615 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