item not found in collection error in qrydef 
Author Message
 item not found in collection error in qrydef

I am trying to run a series of update queries by defining them in sql stored
in a table.

When I try to set the qrydef I get the "item not found in collection" error.

Any help?...  Thanx... Here's code:

Public Function generatetap()

'dimension objects
Dim ws As Workspace
Dim db As Database
Dim rs As DAO.Recordset
Dim rssqlexe As DAO.Recordset
Dim qdf As QueryDef
Dim sql As String
Dim sqlqry As String
Dim tapdate As Date

Set ws = DBEngine.Workspaces(0)

'get sql from tbl_tap_generation
sql = "SELECT Tbl_Tap_Generation.tapid, Tbl_Tap_Generation.tapsqlstr,
Tbl_Tap_Generation.taporder FROM Tbl_Tap_Generation;"

Set db = CurrentDb

Set rs = db.OpenRecordset(sql)

rs.MoveFirst
sqlstring = rs![tapsqlstr]
Set qdf = db.QueryDefs(sqlstring)
'qdf.Parameters = 7
qdf.Execute

qdf.Close
rs.Close
rssqlexe.Close
db.Close



Sun, 26 Dec 2004 04:29:46 GMT  
 item not found in collection error in qrydef
instead of 'Set qdf = db.QueryDefs(sqlstring)' try

Set qdf = db.CreateQueryDef()
qdf.sql=strstring
qdf.execute

BTW, you probably should delete ' Set ws = DBEngine.Workspaces(0)', since
you are not usingit for anything
--

~~~~~~~~~~~~~
Make a Great Day
TomG


Quote:
> I am trying to run a series of update queries by defining them in sql
stored
> in a table.

> When I try to set the qrydef I get the "item not found in collection"
error.

> Any help?...  Thanx... Here's code:

> Public Function generatetap()

> 'dimension objects
> Dim ws As Workspace
> Dim db As Database
> Dim rs As DAO.Recordset
> Dim rssqlexe As DAO.Recordset
> Dim qdf As QueryDef
> Dim sql As String
> Dim sqlqry As String
> Dim tapdate As Date

> Set ws = DBEngine.Workspaces(0)

> 'get sql from tbl_tap_generation
> sql = "SELECT Tbl_Tap_Generation.tapid, Tbl_Tap_Generation.tapsqlstr,
> Tbl_Tap_Generation.taporder FROM Tbl_Tap_Generation;"

> Set db = CurrentDb

> Set rs = db.OpenRecordset(sql)

> rs.MoveFirst
> sqlstring = rs![tapsqlstr]
> Set qdf = db.QueryDefs(sqlstring)
> 'qdf.Parameters = 7
> qdf.Execute

> qdf.Close
> rs.Close
> rssqlexe.Close
> db.Close



Sun, 26 Dec 2004 04:46:10 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Item not found in Collection Error?

2. Item not found in collection error

3. Item Not Found in Collection (Error 3036)

4. Item not found in this collection error

5. Error 3265:Item not found in this collection

6. Error 3265 Item not found in this collection

7. recordset error - Item not found in this collection

8. 3265 runtime error, item not found in collection

9. Item not found in this collection error

10. Run-time error 3265: Item not found in this collection

11. Trouble with ADO Connection.Open() Error 3625 Item not found in collection

12. Recordset error - item not found in this collection

 

 
Powered by phpBB® Forum Software