Using SQL user defined function in MS Access 2007

Question

I am very new to Access and need a bit help over here. I was working on MS Access 2007 (in VB) with SQL 2008 Server as my database.

I wanted to show a SQL table in the frontend of Access but faced the problem while using SQL user defined function with my SQL query in Access 2007. I wrote this function and the query in SQL Query design of Access and tried to Run it.. but its showing error (Userdefined "timepart" function not recognized) !!

CREATE function  dbo.timepart (@date as SMALLDATETIME) 
RETURNS SMALLDATETIME 
AS 
BEGIN 
RETURN @date - DATEADD(d, DATEDIFF(d,0,@date), 0) 
END 

SELECT * FROM TABLENAME WHERE dbo.timepart(FIELDNAME) Between '9:30' AND '17:30';

I guess MS Access doesn't allow to write UDF SQL functions! I tried this same query in my local SQL Server 2008 database (SQL management studio) and is working perfectly fine.

Community · Accepted Answer · 2017-05-23 12:13:30Z

1

You should create a pass-through query. With a pass-through query, the code is run on SQL Server and the results are returned to MS Access. You can therefore use SQL Server T-SQL.

Also access-SQL pass-through query (creating SP) error

set up odbc

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Sep 28, 2012 at 14:08

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

puneet.ajwani Over a year ago

I chose the "pass through" tab and then executed the query. Its now asking me to "Select data source" !!

Fionnuala Over a year ago

Did you set up the ODBC connection?

puneet.ajwani Over a year ago

Yes I did !! Is there any other option?? Can I log into my online database server, create this function and use it directly in the SQL Query Desing of Access !!??

Fionnuala Over a year ago

You can create a view in SQL Server and link that and you can run a stored procedure with ADO or in the query design window as a pass-through query. However, if you cannot get a simple pass-through query to work, such as shown above, you need to sort out that problem. For a start, are you using linked tables?

puneet.ajwani Over a year ago

No, I am not using linked tables.

|

Collectives™ on Stack Overflow

Using SQL user defined function in MS Access 2007

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related