passing Access.Application object to a function: Dim, Set, Object how to make it work? -

i came upon (modified) function in stack overflow page , have been trying work without giving on passed object (if handle access.application strictly within first routine work).

yes know of number of ways same answer (mostly other posts on stack), but there general concept here of passing objects functions master--please forget moment function checks existence of table.

function fcn_checktblsexist(thedatabase access.application, _ tablename string) boolean    'access.application.currentdata.alltables.count 'etc 'workaround enabling disposal of  'the "thedatabase" object variable      ' presume table not exist.     fcn_checktblsexist = false      ' define iterator query object model.     dim itable integer      ' loop through object catalogue , compare search term.       itable = 0 thedatabase.currentdata.alltables.count - 1         if thedatabase.currentdata.alltables(itable).name = tablename             fcn_checktblsexist = true             exit function         end if     next itable   end function   function callfcn_checktblsexist(tablename string)   'this example of curried function?--step down in dimensionality  dim bo0 string     dim object set = createobject("access.application")  bo0 = fcn_checktblsexist(a, tablename)  msgbox tablename & " exists " & bo0  end function 

i don't know if (thedatabase access.application, . ) part correct, may root of problem, rather dim, set, object (new?) gymnastics may required in auxiliary procedure. maybe there reference library problem (i'm running access 2013).

update: not sure following robust enough meant earlier in post, being put here completeness. btw, not split application maybe why following works. appreciate hansup's post, not enough can said on subject. anyway

public function fcn_checktblsexist(tablename string) boolean   'call function once every table  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' dim appaccess new access.application dim thedatabase access.application      ' presume table not exist.     fcn_checktblsexist = false      ' define iterator query object model.     dim itable integer      itable = 0 access.application.currentdata.alltables.count - 1         if access.application.currentdata.alltables(itable).name = tablename             fcn_checktblsexist = true             exit function         end if     next itable  end function 

just wanted add last function posted technically considered partial or no currying depending on how scope of function limited invoking "access.application.currentdata.alltables." substitute "thedatabase", substituting specific string created access.application.currentdb.name original function ...(thedatabse,... true full currying.

anyway passing objects functions , libraries , methods primary focus of discussion. when dao issue worked should have better feel may going on , i'll post , mark best solution accordingly.

the problem not passing access.application object other function. instead create access.application , later check existence of table without having opened database within access session. in situation, thedatabase.currentdata.alltables.count should trigger error 2467, "the expression entered refers object closed or doesn't exist."

i revised both procedures , tested them in access 2010. both compile , run without errors , produce result think want.

function fcn_checktblsexist(thedatabase access.application, _         tablename string) boolean      dim tdf dao.tabledef     dim blnreturn boolean      blnreturn = false     each tdf in thedatabase.currentdb.tabledefs         if tdf.name = tablename             blnreturn = true             exit         end if     next ' tdf     fcn_checktblsexist = blnreturn end function  function callfcn_checktblsexist(dbpath string, tablename string)     dim bo0 boolean     dim object      set = createobject("access.application")     a.opencurrentdatabase dbpath     bo0 = fcn_checktblsexist(a, tablename)     msgbox tablename & " exists " & bo0     debug.print tablename & " exists " & bo0     a.quit     set = nothing end function 

note didn't include provision check whether dbpath database exists before attempting open it. error if give path database not exist.

dao reference issues:

dao 3.6 last of older dao series. supports older mdb type databases. when access 2007 introduced accdb database type, new dao library (access database engine object library, referred acedao) introduced. in addition supporting accdb databases, acedao can support older mdb types.

when setting references, don't attempt choose both.

here screenshot of project references:

when examine project references in immediate window, notice acedao referred dao. ran callfcn_checktblsexist procedure demonstrate works without dao 3.6 reference:

that based on access 2010. you're using access 2013, acedao version number may different, else should same.