The expression On Click you entered....

[OhioSteve]

We have all seen this error message~

The expression On Click you entered as the event property setting produced the following error:
The expression may not result in the name of a macro, the name of a user-defined function, or a stored procedure
There may have been an error evaluating the function, event, or macro.

It usually happens when your vba code is seriously flawed.

I have a product that I distribute to dozens of laptop users. This is a COMPILED product (an mde). The error happens when the user presses a certain button. This button uploads records from their laptop to an SQL Server database. Here are some details:

1. There are two groups of users, group A and group B. Almost all of the errors happen to group B. Group A runs windowsXP group B runs Windows 2000. Group A are domain users on our lan. Group B are authenticated users.

2. Even with Group B, most of the uploads ARE successful. Their uploads fail about 40% of the time.

3. Here is a code snippet:

Public Function uploadComments()

'create some objects
Dim counter As Integer
counter = 0
Dim cmd As New ADODB.Command
Dim serverConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim localConn As New ADODB.Connection
Dim prmComments As New ADODB.Parameter
Dim prmWeekStartDate As New ADODB.Parameter
Dim prmWindowsID As New ADODB.Parameter

'Configure the parameters.
With prmComments
.name = "@comments"
.Type = adChar
.Direction = adParamInput
.Size = 2500
End With
With prmWeekStartDate
.name = "@weekStartDate"
.Type = adDate
.Direction = adParamInput
End With
With prmWindowsID
.name = "@windowsID"
.Type = adChar
.Direction = adParamInput
.Size = 50
End With

'Open the server connection.
'For some reason, you must do this prior to configuring cmd.
serverConn.Open [connection string]

'Configure the cmd.
With cmd
.ActiveConnection = serverConn
.CommandText = "[stored procedure name]"
.CommandType = adCmdStoredProc
.CommandTimeout = 10
.Parameters.Append prmComments
.Parameters.Append prmWeekStartDate
.Parameters.Append prmWindowsID
End With

'Open the LOCAL connection.
localConn.Open [connection string]

'Configure the recordset object, and fill it with records.
With rs
.ActiveConnection = localConn
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open "Select * from commentsFor1PayPeriod"
End With

'Actually upload the records.
Do While counter < rs.RecordCount
prmComments.value = rs(0).value
prmWeekStartDate.value = rs(1).value
prmWindowsID.value = [user ID]
cmd.execute
rs.Move 1
counter = counter + 1
Loop

'Clean up
Set cmd = Nothing
localConn.Close
serverConn.Close

End Function

 

[JerryKlmns]

Steve can't figure why the error happens but I see that you don't close rs nor destroy all ohter ADODB objects used.

And somethoughts: Why not use a client side updatebatch recordset to write all records from ms-access to SQL server or (even better) export all records to a txt file on a server and execute a DTS or BCP or BULK INSERT at the SQL server?

 

[OhioSteve]

1. I do close the recordsets I just didn't show you that part of the code.

2. I do NOT destroy them. I declare them within my function. I ASSUME that they will be destroyed when they go out of scope. Is that wrong?

3. I don't know the term "updatebatch recordset".

4. I am familiar with dts. I used it occasionally with SQL Server 2000. DTS does not exist in SQL Server 2005, and my server is on 2005.

5. Keep in mind that I am inserting a tiny number of records. The overhead caused by multiple sql statements in very small.

 

[JerryKlmns]

1. Ok

2. They should but again, you might enjoy a memory leak.

3. For a client side cursor type the LockType property set to be adUpdateBatch It 's like chewing the whole sandwich and swallowing it at once

4. I think it does 'cause there is mentioned in help on an SQL Server 2005 next to me. Haven't use yet none of them, though.

5. The recordset would be me prefferable.

 

[HarleyQuinn]

Steve & Jerry - in regard to point 4, DTS has been replaced with SSIS (SQLServer Intergration Services) in SQLServer2005. You can use old DTS packages within 2005 but you need to install a tool to do so (there's a useful SSIS FAQ faq1555-6242). There is also an SSIS forum (forum1555).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

HarleyQuinn

thanks for clarifying that.