3

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
puneet.ajwani
  • 41
  • 2
  • 4

1 Answers1

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

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • I chose the "pass through" tab and then executed the query. Its now asking me to "Select data source" !! – puneet.ajwani Sep 28 '12 at 14:13
  • Did you set up the ODBC connection? – Fionnuala Sep 28 '12 at 14:13
  • 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 !!?? – puneet.ajwani Sep 28 '12 at 14:23
  • 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? – Fionnuala Sep 28 '12 at 14:26
  • No, I am not using linked tables. – puneet.ajwani Sep 28 '12 at 14:31
  • How are you connecting to SQL server? Are you using *.accdb or *.mdb? Any reason why you are not working using the MS recommended linked tables? – Fionnuala Sep 28 '12 at 14:33
  • How are you connecting to SQL server? – Fionnuala Sep 28 '12 at 14:42