Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
379 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
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 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