ActiveX exe with ADODB Connection

[Jimmer]

I'm trying to write an ActiveX exe that will perform a check on a database. But I would like to pass my ADODB connection to the ActiveX, so that I don't need to re-establish a connection to the database. I keep getting an "Invalid Property Value" error. Any ideas would be great.

Thanks in advance,
Jimmer

[Edneeis]

That should be possible, post your code and maybe we can help.

[Jimmer]

Heres the code....

Code from calling exe:

-----------------------------------------------------------------------
Public WithEvents MyMailObject As MailCheck

Private Sub Form_Load()

Set MyMailObject = New MailCheck


*********** Problem Exists
Call frmMain.MyMailObject.MonitorMail(dbConnect, userName)

end sub


Code from the ActiveX exe:

-----------------------------------------------------------------------
Dim WithEvents objMailCheck As frmMailCheck
Public Event NewMailFound()

Private Sub Class_Initialize()

'Set the Mail Check Object to Mail Check Form
Set objMailCheck = New frmMailCheck

End Sub

Public Sub MonitorMail(dbConnect As ADODB.Connection, Username As String)

'Set the Mail Check Variables
Set objMailCheck.dbConnect = dbConnect
objMailCheck.Username = Username
objMailCheck.MailTimer.Interval = 600000

End Sub

'Mail Check Object New Mail Found Event
Private Sub objMailCheck_NewMailFound()

'Raise the New Mail Found Event
RaiseEvent NewMailFound

End Sub
------------------------------------------------------------------------------

Form Code:

------------------------------------------------------------------------------
Public dbConnect As ADODB.Connection 'Variable For Connection
Public Username As String 'Variable For User Name
Public Event NewMailFound() 'Event For New Mail
Dim sqlString As String 'Sql Statement

Private Sub MailTimer_Timer()

'Call Function to See If New Mail Exists
If NewMailExists(GetLastMailNumber) = True Then

'Raise the New Mail Found Event
RaiseEvent NewMailFound

MailTimer.Interval = 0

End If

End Sub

'Function to Open the Recordset
Private Function NewMailExists(lastCommunRef As Long) As Boolean
Dim rsCommunication As New ADODB.Recordset

'Initialize Function to False
NewMailExists = False

'Check to See If Last Communication Reference is Zero
If lastCommunRef = 0 Then

'Exit the Function
GoTo Exit_Function

End If

'If An Error Occurs Opening the Recordset
On Error GoTo Exit_Function

'Set the Sql Statement
sqlString = "SELECT * " & _
"FROM [COMMUNICATION] " & _
"WHERE [USERNAME] = '" & Username & "' " & _
"AND [COMMUNREF] > " & lastCommunRef & " " & _
"ORDER BY [COMMUNREF] DESC"

'Open the Current recordset with the passed values
'And the Open Connection
rsCommunication.Open sqlString, dbConnect, adOpenKeyset, adLockReadOnly

'Check the Recordcount
If rsCommunication.RecordCount <> 0 Then

'Call Procedure to Set the Last Mail Number
Call setLastMailNumber(rsCommunication![communRef])

'Set the Function to True
NewMailExists = True

End If

'Exit Function
Exit_Function:

'Close the Recordset
rsCommunication.Close

'Set the Recordset to Nothing
Set rsCommunication = Nothing

End Function

'Function to Open the Recordset
Private Function GetLastMailNumber() As Long
Dim rsPersonnel As New ADODB.Recordset

'Initialize Function to Zero
GetLastMailNumber = 0

'If An Error Occurs Opening the Recordset
On Error GoTo Exit_Function

'Set the Sql Statement
sqlString = "SELECT [LASTCOMMUNREF] " & _
"FROM [PERSONNEL] " & _
"WHERE [USERNAME] = '" & Username & "'"

'Open the Current recordset with the passed values
'And the Open Connection
rsPersonnel.Open sqlString, dbConnect, adOpenKeyset, adLockReadOnly

'Check the Recordcount
If rsPersonnel.RecordCount <> 0 Then

'Set the Function to the Last Communication Reference
GetLastMailNumber = rsPersonnel![lastCommunRef]

End If

'Exit Function
Exit_Function:

'Close the Recordset
rsPersonnel.Close

'Set the Recordset to Nothing
Set rsPersonnel = Nothing

End Function

'Function to Set the Last Mail Number
Private Function setLastMailNumber(communRef As Long)

'If An Error Occurs Opening the Recordset
On Error Resume Next

'Set the Sql Statement
sqlString = "UPDATE [PERSONNEL] " & _
"SET [LASTCOMMUNREF] = " & communRef & " " & _
"WHERE [USERNAME] = '" & Username & "'"

'Execute the Statement
dbConnect.Execute sqlString

End Function

[Edneeis]

Ok I am a bit confused is frmMain in the calling project or the ActiveX EXE?

It seems like its in the ActiveX Exe but you are referencing it in your call from the calling project? Also this doesn't seem complete:

Public WithEvents MyMailObject As MailCheck 
 
Private Sub Form_Load() 
 
Set MyMailObject = New MailCheck 
 
 
*********** Problem Exists 
Call frmMain.MyMailObject.MonitorMail(dbConnect, userName) 
 
end sub

For instance where is the dbConnect and username varaibles declared in the calling project?

Also to show code formatted in code in the forum you can use [ vbcode ] and [ /vbcode ] without the spaces by the bracets.

[Edneeis]

If frmMain is in the calling project you may want to try calling it like this:

MyMailObject.MonitorMail dbConnect, userName

Also you never unload the frmMailCheck or objMailCheck or at least didn't show the Class_Terminate event. Although when you access it's properties then it would load so you really should unload any instances you create in the Terminate event of the class.

[Edneeis]

I think it may be because you have the interval for the timer set too high. The interval property is a can't be past 65,535 and you hva it set to 600000 and that is in the MonitorMail sub.

When working with classes you may want to change your ErrorHandling to break in Class Module so it will bring you to the exact line that is having problems.

[Jimmer]

That did it, thanks alot...

But I'm not sure how to step through my
ActiveX exe code when running and testing my main project.
Any help would be great.

[Edneeis]

With ActiveX Exe's its kinda dumb you have to run your activeX in own instance of VB then run your project in another instance of VB at the sametime. Then you should be able to debug.

[Jimmer]

Thanks again, That works like a charm.
The only thing I am having a problem with now is that
the connection object in the ActiveX exe doesn't seem to
allow me to open recordsets, and perform execute statements.

Still looking but not to sure what else I can try.

Anyway, thanks again Edneeis

[Edneeis]

Does it give an error or it just doesn't do anyting?

[Jimmer]

Actually I'm finding that I can use:

'dbconnect.execute sqlStatement'

but cannot get a recordset to open, I really confused with this.
If the execute statement works then the connection must be
working and active, but not sure why I can't open a recordset with this connection????

Any help would be great. Thanks

[Edneeis]

So what does happen when you try dbconnect.Open strSQL?

[Jimmer]

When the ActiveX exe performs the line:

'Open the Current recordset
rsCurrent.Open sqlString, dbConnect, adOpenKeyset, adLockReadOnly

This uses the passed connection to the exe. The error is:

Number:3001, Arguments are of wrong type, are out of acceptable range, or are in conflict with one another.

I also found that if I set the recordset values in seperate lines like below:

rsCurrent.activeconnection = dbconnect
rscurrent.cursortype = adopenkeyset
rscurrent.locktype = adlockreadonly
rscurrent.open sqlstring

The same error occurs as soon as it exe hits the activeconnection line. So I think that the connection is passed to the exe. But for some reason, I can't open recordsets with it. I'm thinking I need to refresh the connection or something. Not sure how to though.

Any help would be appreciated.

[okipwn]

Hi Jim,
rsCurrent.activeconnection = dbconnect
should be
Set rsCurrent.activeconnection = dbconnect
Use set because you're using a class object.

[Jimmer]

Hi okipwn,

I tried using that, but exactly the same thing happens. As soon as I hit that line, it jumps to the error handler. And gives me the error.

[Jimmer]

Hi okipwn,

Check the third post made, it contains all the code in my activex exe.

Thanks
Jim