Function performance different when run from an Access form -

i have sql stored procedure call using vba function populates 2 ms access form fields. whole purpose in using stored procedure call select query, when run in access takes 8-10 secs return values. stored procedure= when run in sql mgt studio retruns value in < 1sec. function returns values in visual basic "immediate" window in 1-2 seconds. but, when call function (which calls stored procedure) takes 8-10 seconds return values access form- defeating whole purpose of using stored procedure. here function:

option compare database option explicit  public strtrack string public strbar string public strprof string public strfac string  public function sp_barcode(myparam string)     dim mydb dao.database     dim qdf querydef     dim sqlx string     dim rs dao.recordset     strtrack = ""     strbar = ""     strprof = ""     strfac = ""     set mydb = currentdb()     set qdf = mydb.createquerydef("")     sqlx = "exec dbo.spwise_wastemanifestinfobybarcode '" & myparam & "'"     qdf.connect = "odbc;description=imdb_dev;driver=sqlserver;server=server\dev;uid=mmmmmm;trusted_connection=yes;database=imdb_dev"     qdf.sql = sqlx     qdf.returnsrecords = true     set rs = qdf.openrecordset()     if not (rs.eof , rs.bof)         strtrack = rs.fields(0)         strbar = rs.fields(1)         strprof = rs.fields(2)         strfac = rs.fields(3)     else         exit function     end if     debug.print strtrack, strbar, strprof, strfac     rs.close     set mydb = nothing     set qdf = nothing end function 

is there reason have poor performance when running function access form?

thanks!

it sounds stored proc working fine if can call function, sp_barcode, in vba immediate window , takes 2 seconds return values.

there overhead when calling stored proc access, translation (access odbc sql server , again) , creating database connection. sounds isn't bad if runs same speed immediate window in vba sql server.

a few obvious things check, first form unbound or bound datasource, i.e. making more connections local , or sql server tables/queries/stored procs bottleneck? if have subforms check bindings well. when open form vba window open when run benchmarks? close it. speed things up.

is single record form or multiple record form? calling more 1 time populate multiple record form. increase number of connections made sql db.

are doing function call field or load event? if being called on fields may called multiple times creating multiple connections.

if simple form no other datasource build existing passthrough query , modify sql param parent form (i'm assuming front-end access database on each individual's pc , not shared adp, mdb, accessed multi users). if still slow isn't function.

create passthrough query "wastemainfestinfo_passthru" prefilled in parameter , defined connection string via properties window , save it:

 exec [dbo].[spwise_wastemanifestinfobybarcode ] @myparmname = n'teststringvalue'; 

set form's bindings wastemainfestinfo_passthru

in parent form's event opens form following:

 set mydb = currentdb() set qdf = mydb.querydefs("wastemainfestinfo_passthru") sqlx = "exec [dbo].[spwise_wastemanifestinfobybarcode] @myparmname = n'" & myparam & "';"  qdf.sql = sqlx      docmd.openform "wastemainfestinfo_passthru", acnormal .... set qdf = nothing set mydb = nothing 

your code looks okay don't think problem. add , endwith qdf object won't increase performance much.