Passing variables to SQL Server 2005 function from Access

Question

Passing variables to SQL Server 2005 function from Access

arkadyzanadze

SSC Enthusiast

Points: 165
More actions
October 13, 2010 at 8:10 am

#157255

Hi,
I have been trying to pass 'int' and 'date' variables from Access module to SQL Server 2005 function and getting the error: "Invalid column name 'MyVariable'
The number is 7 digit number, so Access converts this to Long Integer. The function parameter in SQL Server receives 'int' type. I have tried everythign I could think of, but no luck.
After spending hours on Internet I have found that "adInteger" could be an answer. But could not find anywhere how I can use it.
I'll be grateful for any suggestions and directions.
thank you

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

arkadyzanadze (10/13/2010)
Hi,
I have been trying to pass 'int' and 'date' variables from Access module to SQL Server 2005 function and getting the error: "Invalid column name 'MyVariable'
The number is 7 digit number, so Access converts this to Long Integer. The function parameter in SQL Server receives 'int' type. I have tried everythign I could think of, but no luck.
After spending hours on Internet I have found that "adInteger" could be an answer. But could not find anywhere how I can use it.
I'll be grateful for any suggestions and directions.
thank you

Questions:

1. Are you getting the error from Access, or from SQL?

2. Have you tried running Profiler to capture what Access is actually sending to SQL? This ought to be really telling.

Note that dates in Access are different than dates in SQL: in Access (if my memory holds true), dates are surrounded by braces. In SQL, they are surrounded by single-quotes.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

arkadyzanadze SSC Enthusiast Points: 165 More actions · Answer 2

Thanks for a reply,

The errors I am getting is in Access. I am now creating parameters and passing them via ADO from Access. From Access I am calling a function in SQL server which returns table. So I have created two parameters and getting this error: "The request for procedure 'MYFUNCTION' failed because 'MYFUNCTION' is a table valued function object.

Any help would be much appreciated.

Dim myParamIn As New ADODB.Parameter

Dim myParamReturn As New ADODB.Parameter

Dim cmd As New ADODB.Command

'Set up the command object and execute

With cmd

.ActiveConnection = cn

.CommandText = "MyFunctionName"

.CommandType = adCmdStoredProc

Set myParamReturn = .CreateParameter(, adInteger, adParamReturnValue, , Null)

.Parameters.Append myParamReturn

Set myParam = .CreateParameter("@intAccountNo", adInteger, adParamInput, , intAccountNo)

.Parameters.Append myParamIn

.Execute

End With