Object no longer valid

[fiep]

If I execute the code underneath I always get a object no longer valid error. Can anybody tell me why?
The variables are declared so that can not be the problem. I do not have a clue as to why it does not work. If I copy the query string into a query it runs without any problem so the syntax of the query does not seem to be the problem.


Dim dbsPut As Database
Dim qdfPut As QueryDef
dim strQuery As string

' Get all the records of the requested articles and insert them into the temporary table
strQuery = "INSERT INTO [" & strTable & "] ( ProdID, [Requested Amount], [Request Number], [CBM Req Number], [Project Name], City, Country ) SELECT ReqA.ProdID, ReqA.ReqAmount, Reqs.ReqNumber, Reqs.ReqCBMno, Proj.PrName, Proj.PrCity, Lan.Landnaam FROM Tbl_Landen AS Lan INNER JOIN (Tbl_Projects AS Proj INNER JOIN (Tbl_Requests AS Reqs INNER JOIN Tbl_RequestedArticles AS ReqA ON Reqs.ReqID = ReqA.ReqID) ON Proj.ProjectID = Reqs.ReqPrID) ON Lan.landID = Proj.PrCountry WHERE (((ReqA.ProdID)='" & lngProdID & "')) ORDER BY Lan.Landnaam;"

Set dbsPut = CurrentDb()
Set qdfPut = dbsPut.CreateQueryDef()
qdfPut.SQL = strQuery
qdfPut.Execute dbFailOnError



Thansk in advance

 

[jrbarnett]

Hi,

Try changing the code to the following:

Set dbsPut = CurrentDb()
Set qdfPut = dbsPut.CreateQueryDef("", strQuery)
qdfPut.Execute dbFailOnError

The only other thing is that if you are using Access 2000 or 2002 (XP) you will need to go to tools -> references and tick the Microsoft DAO 3.6 Object library, then change your declarations to

Dim dbsPut As DAO.Database
Dim qdfPut As DAO.QueryDef
dim strQuery As string

to stop Access getting confused between DAO and ADO.

John

 

[fiep]

John,

The reference is already made. But I did not know that I should define the type of object I used. Sounds sensible though. I will try it immediately.

Thanks.