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
314 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:saxophobe60
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
Excellent that worked!
You da Man!
0
 
LVL 44

Expert Comment

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now