<> </P> d- O+ A" x# P; \* e" d* A
<>Creates a new Recordset object and appends it to the Recordsets collection. </P>3 E+ t* r% V( y; w+ m+ i3 t
<> </P> # p; |, ?9 H7 G- C4 M7 B1 V<>Syntax </P>$ F! L, b% n8 v- g0 y0 T
<> </P>! s6 c7 w8 c B
<>For Connection and Database objects: </P>8 X3 F. q% p9 ^. ]- i _4 b
<> </P>1 W- {7 ~; M+ @$ ^( ]$ q) P$ F
<>Set recordset = object.OpenRecordset (source, type, options, lockedits) </P> & E$ w( M, x1 G) P: f9 ]' e0 k<> </P> ( m# t1 ~% q; ] C& L<>For QueryDef, Recordset, and TableDef objects: </P>5 C h" N) q2 |+ D) v) S
<> </P> R- J7 Q) _2 F
<>Set recordset = object.OpenRecordset (type, options, lockedits) </P> ) P# V1 a. c! k) L8 m: B! s/ X+ a<> </P> 8 G1 t! u8 P9 X3 J) T( W+ g1 v<>The OpenRecordset method syntax has these parts. </P> q( ^/ [$ z" u- H7 K- r
<> </P> 6 H% J1 r1 ~; N1 @0 s) x<>art Description </P>. [+ }5 w$ N* R& F6 w0 v/ x: _! d
<>recordset An object variable that represents the Recordset object you wantt to </P> - v, u- X6 N# [" ~' [<>open. </P>3 H4 C& V+ [/ x. _; i9 u( B" ~
<>object An object variable that represents an existing object from which you </P>' f. W8 b7 J" E. X G& F Q+ q
<>want to create the new Recordset. </P> . ]6 h6 v: C, _+ ?9 _<>source A String specifying the source of the records for the new Recordset. </P> # E3 L& _: P' T2 Y0 ~( f0 z h* T<>The source can be a table name, a query name, or an SQL statement that </P>0 U# l1 b! T+ T# d1 K. c
<>returns records. For table-type Recordset objects in Microsoft Jet databases, </P> 6 e' k' X3 R; Y1 H0 D" a<>the source can only be a table name. </P>) J- I1 `( F5 W F v/ H+ [! o1 G# v
<>type Optional. A constant that indicates the type of Recordset to open, as </P>8 i& d9 N1 B' V) n5 Z
<>specified in Settings. </P> ' z& t# b5 \. v4 n2 W<>options Optional. A combination of constants that specify characteristics of </P> 7 Y: p' i5 k) } q<>the new Recordset, as listed in Settings. </P> 0 @+ J# g1 K0 J<>lockedits Optional. A constant that determines the locking for the Recordsset, </P> 1 a1 N2 m: \; W9 f<P>as specified in Settings. </P> 8 R" Z8 Y8 L' p1 D1 B! i<P>Settings </P> 1 X6 ?8 `. N9 \- U7 L! g' l<P> </P> / q5 F0 u! `/ U ~' g0 l6 [! d<P>You can use one of the following constants for the type argument. </P> : w: D/ M- o, i, {5 w- a<P> </P> 8 ^: H. s& E/ M" _<P>Constant Description </P>! I9 u% r4 }; @) K" [
<P> </P> 9 l& p, ~9 X3 f$ V: k+ ^<P> </P> / q# y: N6 a9 m0 F$ Q$ w<P>dbOpenTable Opens a table-type Recordset object (Microsoft Jet workspaces </P> - F/ P5 E8 u& [( b7 V<P>only). </P>: Q9 S2 }1 i/ k) ]6 P
<P>dbOpenDynamic Opens a dynamic-type Recordset object, which is similar to an </P># F8 q6 @4 I) A; S! F
<P>ODBC dynamic cursor. (ODBCDirect workspaces only) </P>8 I [4 O4 c* E7 {% l. h9 M
<P>dbOpenDynaset Opens a dynaset-type Recordset object, which is similar to an </P> 7 G: ]9 w( m9 h5 J( q; N' ?<P>ODBC keyset cursor. </P> - J- i. ]- Q- V2 v) t% E<P>dbOpenSnapshot Opens a snapshot-type Recordset object, which is similar to an </P>6 M6 h7 C) c& k- W$ P1 X0 n
<P>ODBC static cursor. </P>' t" o q* V Q1 F
<P>dbOpenForwardOnly?Opens a forward-only-type Recordset object. </P> # o" n9 [/ k2 B4 H9 e<P>Note If you open a Recordset in a Microsoft Jet workspace and you don't </P>+ G' p2 K$ I% W+ Y C& S/ N( c% s/ n
<P>specify a type, OpenRecordset creates a table-type Recordset, if possible. If </P>& B. q" d" r$ L9 K
<P>you specify a linked table or query, OpenRecordset creates a dynaset-type </P>8 R# e6 Q* z% G% f& N
<P>Recordset. In an ODBCDirect workspace, the default setting is dbOpenForwardOnl </P> + \* `" _! P. A( u6 d" `) n* i, M9 ]<P>y. </P> 8 a! _5 `9 r1 g' h2 q# m<P> </P>; D1 f9 q& U9 P, q8 W
<P>You can use a combination of the following constants for the options </P> ! N8 W5 T# u9 r; U<P>argument. </P>2 A9 _/ W0 R8 ^3 T
<P> </P># c" s% S/ D* `$ j8 E
<P>Constant Description </P>9 _+ P# H) [0 H9 b1 t, x$ h/ }( v
<P>dbAppendOnly?Allows users to append new records to the Recordset, but </P>4 J3 O" _ f% X# S% k
<P>prevents them from editing or deleting existing records (Microsoft Jet </P>! y$ @. t& u1 _+ K5 j- ] N
<P>dynaset-type Recordset only). </P> ! m) H; ]+ V5 D/ K<P>dbSQLPassThrough?Passes an SQL statement to a Microsoft Jet-connected ODBC </P>) a; A) y! F2 f k8 ?! J$ f; c! {
<P>data source for processing (Microsoft Jet snapshot-type Recordset only). </P>( C# c9 B# T$ {) A3 J/ k1 e) F
<P>dbSeeChanges Generates a run-time error if one user is changing data that </P>/ [. O" Z- G2 k/ c1 w' F+ |
<P>another user is editing (Microsoft Jet dynaset-type Recordset only). This is </P> ; Q4 N G2 O) X1 s( Y: M/ S<P>useful in applications where multiple users have simultaneous read/write </P> : K) I" G* G& ?+ v8 Q, W2 @<P>access to the same data. </P> 2 x% ]) a3 k! \<P>dbDenyWrite?Prevents other users from modifying or adding records (Microsoft </P> 1 n3 b, P7 v; `$ | ^& e<P>Jet Recordset objects only). </P>$ L [) T7 C2 N, d
<P>dbDenyRead?Prevents other users from reading data in a table (Microsoft Jet </P> A6 `8 E/ H, w" C<P>table-type Recordset only). </P># O/ v- x; O" s% V
<P>dbForwardOnly?Creates a forward-only Recordset (Microsoft Jet snapshot-type </P> , [; _( k& V, H! l8 S6 A<P>Recordset only). It is provided only for backward compatibility, and you </P> ; j$ G6 Z* t; j2 p( t+ M<P>should use the dbOpenForwardOnly constant in the type argument instead of </P> # f# M3 E/ {; }. P$ J<P>using this option. </P> O6 ^. O8 h9 A* n5 e7 h# M<P>dbReadOnly?Prevents users from making changes to the Recordset (Microsoft Jet </P> + n; z2 Y1 ]3 z<P>only). The dbReadOnly constant in the lockedits argument replaces this </P>6 A9 `) n$ g7 f
<P>option, which is provided only for backward compatibility. </P>/ F! `" g3 p- h: b- o H
<P>dbRunAsync Runs an asynchronous query (ODBCDirect workspaces only). </P> - d2 A1 O: p: U) t" Z<P>dbExecDirect?Runs a query by skipping SQLPrepare and directly calling </P>$ E1 f/ S3 x# b
<P>SQLExecDirect (ODBCDirect workspaces only). Use this option only when you抮e </P>3 k6 t# P. N7 {* ^/ E* d" Z: U
<P>not opening a Recordset based on a parameter query. For more information, see </P>8 Z0 ~& v/ o1 ]. L, r" O) n
<P>the "Microsoft ODBC 3.0 Programmer抯 Reference." </P>, k" A5 z4 _! `! {
<P>dbInconsistent?Allows inconsistent updates (Microsoft Jet dynaset-type and </P> 2 E% A" f6 R, c* Z" \4 F( \& A/ H% C<P>snapshot-type Recordset objects only). </P># o/ l% E' G, Q% l& M' N
<P>dbConsistent?Allows only consistent updates (Microsoft Jet dynaset-type and </P> z4 }5 T [0 M
<P>snapshot-type Recordset objects only). </P># u# _6 _ z7 |
<P>Note The constants dbConsistent and dbInconsistent are mutually exclusive, </P> 4 z! L& x) }# [' d& @' K<P>and using both causes an error. Supplying a lockedits argument when options </P> 2 a& s) [% k7 s$ x( X- `<P>uses the dbReadOnly constant also causes an error. </P> f2 ]% `! Y3 R& I' s* ^
<P> </P>* p* M) O6 v* [, u- w
<P>You can use the following constants for the lockedits argument. </P>- _; `* s+ C, W3 c: ?! x
<P> </P>5 u3 o1 s; \0 Z
<P>Constant Description </P> 1 S4 D- m8 v2 Q3 K, I: P<P>dbReadOnly Prevents users from making changes to the Recordset (default for </P>: J7 Y0 A: c. f. P- d9 L$ |
<P>ODBCDirect workspaces). You can use dbReadOnly in either the options argument </P> 8 I; M6 }0 G* x# h# p' A5 R<P>or the lockedits argument, but not both. If you use it for both arguments, a </P> $ Q4 E4 u5 N/ V! v* H, C<P>run-time error occurs. </P>5 x; n1 x8 _0 {1 D
<P>dbPessimistic?Uses pessimistic locking to determine how changes are made to </P> 3 s) T+ E6 g8 \& h3 W6 g; }<P>the Recordset in a multiuser environment. The page containing the record </P>6 u* u' W) O/ _
<P>you're editing is locked as soon as you use the Edit method (default for </P>: @; J5 F; T2 x+ F6 u9 I& f* ]+ J
<P>Microsoft Jet workspaces). </P>4 g" b4 j- G4 z6 j _/ s! W' F1 j
<P>dbOptimistic?Uses optimistic locking to determine how changes are made to the </P> & V& W& Y }& M5 X: D! m( k<P>Recordset in a multiuser environment. The page containing the record is not </P>" M4 {) k* d, o$ ^- o! a. C9 J
<P>locked until the Update method is executed. </P> - e; w+ {1 L3 W2 c# O3 Z2 o; |<P>dbOptimisticValue?Uses optimistic concurrency based on row values (ODBCDirect </P>5 u0 W$ L+ V% _7 T- g) f
<P>workspaces only). </P>) n$ ^; s! k" K% U+ L
<P>dbOptimisticBatch?Enables batch optimistic updating (ODBCDirect workspaces </P> : `7 q% p! N/ @<P>only). </P>- {6 O4 c- a/ p7 Q9 F A
<P>Remarks </P>& N8 Y+ o5 ^3 W" h- k3 f% u
<P> </P> ; B( H/ }) K6 R$ ?7 f! [<P>In a Microsoft Jet workspace, if object refers to a QueryDef object, or a </P> 4 G$ e1 `- i l5 w+ A( w<P>dynaset- or snapshot-type Recordset, or if source refers to an SQL statement </P>& H. J; \5 g0 u* m6 T
<P>or a TableDef that represents a linked table, you can't use dbOpenTable for </P>( @& E. ]$ ~1 E- b6 Q7 x
<P>the type argument; if you do, a run-time error occurs. If you want to use an </P> 2 N7 @. T' o4 I1 |) w<P>SQL pass-through query on a linked table in a Microsoft Jet-connected ODBC </P> / y! v: y# |/ l ]! ^ ~% ?<P>data source, you must first set the Connect property of the linked table's </P> 5 E1 H# O7 F( b2 } |2 V& }0 ]<P>database to a valid ODBC connection string. If you only need to make a single </P> # G% O+ q# Q- n* A<P>pass through a Recordset opened from a Microsoft Jet-connected ODBC data </P> 6 T9 w* Y, p* { J<P>source, you can improve performance by using dbOpenForwardOnly for the type </P> # Q" ~# h C9 ?- F m: v( e# B<P>argument. </P>% W! t6 y+ e% k k l
<P> </P> 8 l) q a6 Z9 B5 v: s<P>If object refers to a dynaset- or snapshot-type Recordset, the new Recordset </P> ( [& u) p' x8 q V! ^<P>is of the same type object. If object </P> 5 T# {: X; h% D<P> refers to a table-type Recordset object, the type of the new object is a </P>! d/ k3 j4 K0 w3 H3 T
<P>dynaset-type Recordset. You can't open new Recordset objects from forward-only </P> 1 a( a' `( ]% O* B7 ~( \ B5 x<P>杢ype or ODBCDirect Recordset objects. </P> 6 p( S3 S$ U! \ M5 K<P>In an ODBCDirect workspace, you can open a Recordset containing more than one </P>& k: b) Y7 T% a2 M) f
<P>select query in the source argument, such as </P>* p& t, e* h5 Q. v3 m+ M% ]
<P> </P> 5 o5 P/ I/ W* e$ w+ Y% a3 H<P>"SELECT LastName, FirstName FROM Authors </P> + ^! T5 i* G* I/ t; V9 I5 D |2 J0 t<P>WHERE LastName = 'Smith'; </P> , q# K: U7 c: v; A. j<P>SELECT Title, ISBN FROM Titles </P>! M% `0 Z8 R$ `& t3 q
<P>WHERE ISBN Like '1-55615-*'" </P> F7 k! G' U1 [ O# G
<P> </P> x& Z" k D& B5 y
<P>The returned Recordset will open with the results of the first query. To </P> Y* _8 ?& k5 h/ p+ A
<P>obtain the result sets of records from subsequent queries, use the </P> # Z8 |$ o; C+ c/ w8 C% Y<P>NextRecordset method. </P> 9 {' s5 M/ n# u, @$ V# k<P> </P> Z4 y @! m% r* {8 s
<P>Note You can send DAO queries to a variety of different database servers </P> " U4 E: [3 O" N% k. D<P>with ODBCDirect, and different servers will recognize slightly different </P> & n5 x1 N7 M$ L; W0 I<P>dialects of SQL. Therefore, context-sensitive Help is no longer provided for </P>! p/ u5 L0 J0 l' `+ g/ g
<P>Microsoft Jet SQL, although online Help for Microsoft Jet SQL is still </P>9 ~0 a# R$ q- |& p
<P>included through the Help menu. Be sure to check the appropriate reference </P>+ x% o- ? U b6 b4 l4 H* x
<P>documentation for the SQL dialect of your database server when using either </P> 9 c9 j9 O- X+ K0 b! r" A# G<P>ODBCDirect connections or pass-through queries in Microsoft Jet-connected </P> # W! O+ v. k x0 v0 w<P>client/server applications. </P>; p9 U9 L) o7 a* p0 B' o0 t( D( ^ S8 J
<P> </P> ) ^: \% P& r" f2 S$ o2 M9 e: f<P>Use the dbSeeChanges constant in a Microsoft Jet workspace if you want to </P> : n; ]9 p* {2 V( _- a" T1 a# Y) A<P>trap changes while two or more users are editing or deleting the same record. </P> : B9 @ l7 I6 O9 X6 ]- }<P>For example, if two users start editing the same record, the first user to </P> , p( R E- W$ G$ \ u/ w+ z2 N<P>execute the Update method succeeds. When the second user invokes the Update </P> - ?: I, t7 y; H' o G<P>method, a run-time error occurs. Similarly, if the second user tries to use </P> 7 W* g% p# @% d8 }7 X9 u$ i<P>the Delete method to delete the record, and the first user has already </P>8 H$ n7 I% i; K/ z7 z* k& u
<P>changed it, a run-time error occurs. </P>3 M7 Z$ p; {) q+ S- s' M |
<P> </P> " {* L1 Z# z0 U& s, }<P>Typically, if the user gets this error while updating a record, your code </P>" e) l" y. p$ E, T' w. q
<P>should refresh the contents of the fields and retrieve the newly modified </P>9 w& A/ k- c y: s2 S
<P>values. If the error occurs while deleting a record, your code could display </P> : A' [$ k; E, \) O<P>the new record data to the user and a message indicating that the data has </P> 8 P7 h% r4 h2 v3 {<P>recently changed. At this point, your code can request a confirmation that </P> 6 V+ N$ b. ?/ ?% f<P>the user still wants to delete the record. </P>9 E# ]# y. s! B6 |
<P> </P># v! T3 n( S& d7 B$ [
<P>You should also use the dbSeeChanges constant if you open a Recordset in a </P>) |4 u' F. H2 {3 s
<P>Microsoft Jet-connected ODBC workspace against a Microsoft SQL Server 6.0 (or </P> / y1 t" x: y) p% S<P>later) table that has an IDENTITY column, otherwise an error may result. </P> # _( V" M3 f M* Y! P<P> </P>9 M9 p4 n0 O* h" ?0 U
<P>In an ODBCDirect workspace, you can execute asynchronous queries by setting </P>/ B. f; a) k; d$ K3 O2 t
<P>the dbRunAsync constant in the options argument. This allows your application </P>$ C. x+ U' N" U" L) G' `8 ?
<P>to continue processing other statements while the query runs in the </P> 4 r: [( L9 _* I<P>background. But, you cannot access the Recordset data until the query has </P> 0 K; @" x' c8 Q; m<P>completed. To determine whether the query has finished executing, check the </P> ; I. k4 G) a3 @<P>StillExecuting property of the new Recordset. If the query takes longer to </P> @+ }& P: p& V& e* W+ z' {<P>complete than you anticipated, you can terminate execution of the query with </P>/ G1 Z+ j* |0 ~& K- u4 G1 l3 @7 ~
<P>the Cancel method. </P> 0 N. [1 d7 g7 Z- s' v# X<P> </P>9 X( D1 _$ _7 n& x
<P>Opening more than one Recordset on an ODBC data source may fail because the </P>0 t( \9 Q4 u, y$ m$ }, ^, o% w! T- |
<P>connection is busy with a prior </P> + I# G% z6 F$ ]; o4 ?% o<P>OpenRecordset call. One way around this is to use a server-side cursor and </P>3 g; Z, ^: O( y( q- |$ V
<P>ODBCDirect, if the server supports this. Another solution is to fully </P> " _7 M* R* o' m<P>populate the Recordset by using the MoveLast method as soon as the Recordset </P> $ C2 b+ _& u. {1 _+ {1 C3 d I<P>is opened. </P>' }! v. R9 ^! m0 e
<P> </P> 8 U- N- O4 v: Q# O' ^5 L<P>If you open a Connection object with DefaultCursorDriver set to </P> ; L* Y [& F/ `; y, l5 t<P>dbUseClientBatchCursor, you can open a Recordset to cache changes to the data </P># i; V% ]6 b9 r8 K, F, W. O
<P>(known as batch updating) in an ODBCDirect workspace. Include dbOptimisticBatc </P> ' g7 q5 |$ o8 N6 X, R# O<P>h in the lockedits argument to enable update caching. See the Update method </P> ?/ a0 |' E) o! G C0 c: }+ n<P>topic for details about how to write changes to disk immediately, or to cache </P> 3 G2 v4 U4 h1 i1 m! Y5 K<P>changes and write them to disk as a batch. </P> + W2 C* I, {3 [+ Q3 a<P> </P> 0 E" R9 s. p& e n<P>Closing a Recordset with the Close method automatically deletes it from the </P>1 {3 J: [# e- Z; E
" K) g0 l; d* M( d<P>Recordsets collection. </P> ) Y& _1 L" ]# [+ y" c) [1 \<P> </P>, Z3 e1 U: t- D% T' |, B1 B
<P>Note If source refers to an SQL statement composed of a string concatenated </P>4 t3 s% _1 {7 I5 z' W9 N
<P>with a non-integer value, and the system parameters specify a non-U.S. </P>/ {7 c$ H; x' D, Y( o; h
<P>decimal character such as a comma (for example, strSQL = "PRICE > " & </P> 0 v4 v' K$ B9 o2 a8 Q2 B<P>lngPrice, and lngPrice = 125,50), an error occurs when you try to open the </P> % W9 @4 H( B3 X4 M& y8 X. Y" j8 Q<P>Recordset. This is because during concatenation, the number will be converted </P>* y' A' p+ ~' @3 }8 q
<P>to a string using your system's default decimal character, and SQL only </P> 0 H6 a& o1 K/ w; } u<P>accepts U.S. decimal characters.</P>