Access 2007 run time problem

[phantom29]

Hello all,

I've written an application that takes care of our employees working hours.
It was developed using MSACCESS 2003 and running under both the full version of access and run time version.

Everything works fine under the full version, but there is a piece of code that causes an error under Run Time version.

Here is the code :

Private Sub btnHistory_Click()
Dim ssql1, nm As String
Dim rs As ADODB.Recordset
Dim x, i As Integer
Set rs = CreateObject("ADODB.Recordset")

ssql1 = "SELECT Count(Query1.DateOfReport) AS CountOfDateOfReport, Query1.Employee, Query1.Project"
ssql1 = ssql1 & " FROM Query1"
ssql1 = ssql1 & " GROUP BY Query1.Employee, Query1.Project"
ssql1 = ssql1 & " HAVING (((Query1.Employee)=" & Me.cbEmployeeSelect & "))"

rs.Open ssql1, CurrentProject.Connection, 1, 3

x = rs.RecordCount
If x > 13 Then x = 13
rs.MoveFirst
For i = 0 To x
If Not (rs.EOF) Then
nm = "cbProjectName" & i
Me(nm).Value = rs.Fields("Project")
Call HoursReportChk(Me.cbMonthSelect, i, Me.cbEmployeeSelect, rs.Fields("Project"))
Call txEnableSerg(i)
rs.MoveNext
End If
Next i

rs.Close
Set rs = Nothing

End Sub

When i try to run it (button click), I get the followimg error message :
"There was a problem to access a property or method of the OLE object"

Could anyone help me with this problem?

P.S - I work in multi-user invironment, with access .mdb database on the back end

 

[Guus2005]

Don't know if this solves anything.
[

Dim rs As ADODB.Recordset
Dim x, i As Integer
Set rs = CreateObject("ADODB.Recordset")

rs is now expected to hold any object. However you dimensioned is as ADODB.recordset.

Dim rs As ADODB.Recordset
Dim x, i As Integer
Set rs = new ADODB.Recordset

Why use ADO while DAO is native? With DAO you don't have to add references.

 

[phantom29]

Dear Guus2005,

Thanks for taking a time to reply my thread.
You are absolutly right, the correct code that I use is :

Dim rs As Object
Dim x, i As Integer
Set rs = CreateObject("ADODB.Recordset")

I only dimention rs as recordset for the time being, and the reason is that it is easier to write code (Access pops up the menu of methods and properties that way).

Anyway, I've already tried to correct this and the problem still exists...

As for DAO - never used it untill now and frankly I do not familiar with it ... probably should study this a little bit.

Have you ever encountered such a problem?

 

[boblarson]

Not sure why you would get that error but I do have a couple of pieces of info about your code:

Dim ssql1, nm As String

I'm assuming you would want ssql1 to be a string, but as declared you are getting ssql1 as Variant and nm As String because Access VBA doesn't quite follow the same path as VB6 in that respect.

The same goes for

Dim x, i As Integer

x would come out a Variant and i would be an Integer.

I always thought you could do it that way too, but found out a while ago that it wasn't that way (can't remember from whom but it was a source I trust).

 

[RuralGuy]

Do you know which line in the code you posted is causing the problem?

 

[phantom29]

Yep, I've isolated it to the following line :

rs.Open ssql1, CurrentProject.Connection, 1, 3


My guess is that the problem lies in the SQL statement, but I havent been able to figure it out.

I'm trying to open a recordset to "Query1" which is quite complicated (for myself at least), but i'm going to post it here - maybe that will help:

SELECT tblHoursInvestLines.DateOfReport, tblHoursInvestLines.Employee, tblHoursInvestLines.Project, qryActiveProjects.ProjectName
FROM tblHoursInvestLines RIGHT JOIN qryActiveProjects ON tblHoursInvestLines.Project=qryActiveProjects.ProjectID
GROUP BY tblHoursInvestLines.DateOfReport, tblHoursInvestLines.Employee, tblHoursInvestLines.Project, qryActiveProjects.ProjectName
HAVING (((tblHoursInvestLines.DateOfReport) Between ServerGetTime(Environ$("LOGONSERVER")) And ServerGetTime(Environ$("LOGONSERVER"))-10) AND ((qryActiveProjects.ProjectName) Not Like "*ENGINEERING*"))
ORDER BY tblHoursInvestLines.DateOfReport DESC;

thanks for your help

 

[RuralGuy]

I'm afraid I can not be of further assistance here. Sorry.

 

[gemma-the-husky]

if you are using normal code, if you get an error, you get the debug code box popup - in a run time version the programme just crashes

now - you say that in the full version you run ok - does this mean you get no errors, or does it mean that you do get errors, but you are able to deal with them as they occur? - because in the run time version you get no error handling at all - you have to expect and allow for all errors, and make your code sufficiently robust to deal with anything and everything that could happen

eg
divide by zeros, empty files, missing files, erroneous sql statements etc

 

[phantom29]

Well, thanks for trying I realy appreciate that.

 

[phantom29]

if you are using normal code, if you get an error, you get the debug code box popup - in a run time version the programme just crashes

now - you say that in the full version you run ok - does this mean you get no errors, or does it mean that you do get errors, but you are able to deal with them as they occur? - because in the run time version you get no error handling at all - you have to expect and allow for all errors, and make your code sufficiently robust to deal with anything and everything that could happen

eg
divide by zeros, empty files, missing files, erroneous sql statements etc

Well, there are absolutly no errors in full version what so ever. And the Run-Time version surprisingly does not crash entirely, but pops up the error message and after "OK" button click just returns to the programm.

The error message is :
"There was a problem to access a property or method of the OLE object"

 

[cardgunner]

Phantom,

I'm not sure if it helps or means anything but I use SQL on our companies server everyday

In that enviroment I would have to use

Not Like '%ENGINEERING%')) instead of "*ENGINEERING*"))

I'm not sure if it makes a difference just thought i'd put it out there.

 

[Guus2005]

Dear Guus2005,

Thanks for taking a time to reply my thread.
You are absolutly right, the correct code that I use is :

Dim rs As Object
Dim x, i As Integer
Set rs = CreateObject("ADODB.Recordset")

I only dimention rs as recordset for the time being, and the reason is that it is easier to write code (Access pops up the menu of methods and properties that way).

Not completely correct. The intellisense shows you methods and properties of any common Object. Not ADODB.recordset specifically. However if you use

dim rs as adodb.recordset 'Need to add ADODB reference

you get the methods and properties for the adodb.recordset specifically.

I would suggest that you add a reference to your database and use the above notation instead use of the "CreateObject" function

Second i would suggest that you build a sql statement without Environ$("LOGONSERVER") but put the result of this function in your sql statement rather than the function itself. SQL is fast in queries, VB is fast in perfoming functions.

Ofcourse speed is relative...


Enjoy!

 

[phantom29]

Dear Guus2005,

I've given some thought to what you said about the Environ$("LOGONSERVER") statement, and came to solution that alowed me to discard it totally. Once I removed it from the query it all started working!!!

So, first of all thanks for the tip, it proved to be very helpful. And secondly, I think it would be a fair assumption not to use this line in queries, in case you will run under Run Time version of Access .

Thank you all for your help and time you dedicated to reply my thread.

 

[IainIDI]

Hey, I'm having this same problem porting my companies software to Access 2007 runtime. The code works fine on the full install of Access 2007 but errors on runtime. Is there anyone that can make any suggestions of what to try do? Either work arounds or fixes!

I've narrowed it down to this line of code:

rst.Open strSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

I checked all the variables and they print out fine in message boxes so the problem must be with rst.Open

I looked at opening a ticket with microsoft but they want me to pay for support for my product!

Any help would be greatly appreciated.

-Iain

 

[RuralGuy]

Have you specified the ADO library in your Dim statement?

 

[IainIDI]

I have, this self-same code works with the full install of access 2007 and previous versions of Access.

 

[boblarson]

Just a thought but why don't you try without this part:

, adCmdText

is there a reason it is there anyway?

 

[IainIDI]

I didn't write the code so I dont know why its there - the variables check out. I'm just reluctant to remove variables when I dont know why they were put there in the first place. If I cant come up with another fix I'll certainly try it though. Thanks for the responses

 

[boblarson]

Just comment out the part - you can always uncomment it if you find you need it. But, I've used ADO with Access for about 7 years and I've never used that part in the code.

 

[IainIDI]

I'll give it a go now