Results 1 to 14 of 14
  1. #1
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7

    "Stored Procedure" with dynamic parameters; can it be done?

    Greetings everyone,



    I haven't coded in Access in quite a while and I'm needing some functionality that doesn't appear to exist. I have some very complex, conditional queries I need to run in Access for the time being. By "complex", I mean things like defining parameters at run-time, based on the contents of certain fields (typical Access parameters, using user prompts, will absolutely NOT work for my situation). I know how to do this using VBA, but, I need to be able to run these procedures as queries from external applications. As far as I know, or have been able to determine, there is no way to run VBA from a query. Does anyone know if it IS possible to call a VBA procedure from an Access query? If so, how? If not, is there some other way to accomplish this? Any suggestions would be appreciated.

    Thanks!

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You can call a VBA Function from a Query.

    Create a Function [Eg: AddThreeValues(Value1, Value2, Value3) As Long . . . to add three passed-in numbers]

    In a Query add a field to call the function and pass numbers from three fields to it.
    Eg:
    VBAResult: AddThreeValues(FieldWithFirstValue, FieldWithSecondValue, FieldWithThirdValue)
    When you run the Query, each row will have the result of the addition of the values in the three fields that you want to Add.

    That's how I use VBA Code from a Query.

    I'm trying to see if I have ever called/invoked a Procedure from inside a Query. I don't believe I ever have.

    Hope this helps.

  3. #3
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Sweet, thanks! I'm heading into a meeting, but I'll try this approach when I get back.

  4. #4
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Okay, that approach does work and will definitely come in handy. However, I guess what I really need to know is how to query a function result as if it were a table (i.e., Function will combine values from multiple tables into an array; Query will be something along the lines of "SELECT * FROM Function Results ..."). I've been playing with it for a bit, but I haven't had any success. Any ideas?

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Instead of putting the values from your function into an array, you could write them to a table and then you can have the Query either in the Function itself [as an SQL statement] or a separate query [that you can also run from within your function].

    Will your array exist anywhere other than in the function [in memory]?

    I have also created output from inside functions that would have been too complex [for me] to do with queries or reports.
    For instance, I might read a line of data from a fixed width or tab-delimited text file into variables and then, based on data in the variables, increment totals, add to a group, start a new group . . . and create either a .txt report [using File I/O] - or just write data to a table that I can query later.

    If you give me a simplified example of your process - it would be easier to help.

  6. #6
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    The scenario is this:

    • I have an employer table, an employee table, and a dependents (spouses or children) table.
    • Dependents are linked to Employees by a cross-reference (junction) table.
    • Employees are linked to Employers by another cross-reference table.
    • I have a list of ID's provided by a process in another application
    • The ID #'s on the list of ID's can be either Employer, Employee, or Dependent ID's and I have nothing to indicate which
    • My end query needs to include the Employer ID's for each entity
    • My function or procedure needs to determine whether to I'm starting with the Employee ID or the Dependent ID, and cross-reference up the the Employer
    • If the original ID is an Employee ID, one join will get me my Employer ID
    • If the original ID is a Dependent ID, I first need to join to the Employee ID, then up to the Employer
    • The method I'm currently developing joins all the appropriate tables and gets me a result, but I'm joining the same pairs of tables multiple times and using a bunch of SWITCH statements to sort out the end results


    If I were doing this in SQL Server, for example, I'd create a stored procedure that identified what type of ID I was starting with and then created the appropriate joins, as needed, through a switch statement. Ultimately, I guess it's the same actions, but the multiple-self-join is very complex, so much so that it keeps confusing me and I just wrote it. My co-workers, who have very little sql experience would never be able to follow what is happening. They wouldn't understand the VBA any better, but I could at least separate the query logic they might need to alter from the processes which sort out where the data comes from.

    The scenario is actually much more complicated than I listed above, because there are multiple types of employees stored in separate tables (e.g., active employees, retirees, disabled employees, etc.; all with their own, separate cross-references). We are working on creating a data warehouse in which we won't have the employees scattered out over multiple tables, but, in the meantime, I need to work within the existing schema. I cannot create any new tables that will persist beyond query execution, but temp tables are okay.

    I can easily handle the VBA to create, fill, and delete the temp table, but I still don't know how I would call the VBA procedures from a query, other than on a record-by-record basis as in your above example. In other words, ALL the data in the query results is coming from the VBA processes and needs to be compiled by the VBA processes; so, the end query needs to create the temp table, fill it with the appropriate data, deliver that data to the end-user, and then delete the temp table.

    Thanks!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Query can only call Function not Sub. Trigger a Sub from form - maybe button click event.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    Trigger a Sub from form - maybe button click event.
    Thanks, but not an option. Access is only being used as a middleman; no one is actually opening the database directly (long story).

    I suppose I could call a Sub from within the function, but, I still need to know how to retrieve the results from the function, if it's possible. Ignoring everything else I've written and simplifying completely, is it possible to use SQL code to return multiple records from a VBA function, as in "Select * From FunctionResults", where the FunctionResults are an array, recordset, table, or other multi-record object? If so, please provide me an example of the SQL syntax?

    Thanks!

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In your case, problem with calling a function from query is that the function is executed for every single record. What you seem to want is a sub that builds a temp table of the desired records and you don't want the procedure to run 10,000 times.

    So, the function would have to be called from a query that consists of a single record. Example using a real table in my database:

    SELECT DISTINCT "x" As Dummy FROM Airports;

    The full table is 55 records but that query returns only 1 record of dummy data.

    Now I call a UDF in that query:

    SELECT DISTINCT "x" As Dummy, Test() As Dummy2 FROM Airports;

    The function code:

    Function Test()
    Test = "Done"
    End Function

    The function executes once. So build your function to run the code for compiling the temp table dataset and return "Done" to the query.

    Is that what you are looking for? Now the question is how to open the query so the function will run? If the database is not opened, I don't know that can be done. And if the db were opened, these shenanigans with dummy query would not be needed. Major Catch-22.

    EDIT: did quick google of 'vbscript access database function' and found
    http://social.technet.microsoft.com/...1-ad4d2507117a
    http://stackoverflow.com/questions/8...-any-sql-query
    Maybe can execute a function in a closed Access file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    You are able to call passthough queries to TSQL in access. Let the SQL box do all the work.

  11. #11
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Thanks, all, but it seems as though I've lost you.

    @alcapps: There is no SQL Server involved here; I only mentioned it as an illustration of how I would do it, if I were in a system that had a robust stored procedure capability.

    @June7: My source data is coming from some text files, some Excel files, a Pervasive SQL database, and multiple other Access databases, all via ODBC connections, as linked tables. My users are interfacing with the data through a custom built, third-party application that has no means of accessing those other data sources, but can issue simple SQL queries against an Access database. That is why I, ideally, need a single query that can return all the necessary data. I need to join several tables together, but I won't know which tables until run-time, because I have a single incoming ID that could match to any of several types of entities. My query/procedure needs to determine what type of entity I'm handling, which tables I need to join, and which key fields to use (the field names are different in each table), produce the entire recordset, and return the ENTIRE recordset to the front-end program. I would prefer not to leave any persistent tables, due to political reasons within my organization, but, whether the tables are persistent, temporary, or virtual, I need to build and export the recordset at run-time.

    I don't think it would be practical for me to run multiple queries from the front-end, like, one to build the recordset, one to read it, and one to delete it, or some combination thereof. I have 80% of a query that will do everything I need to do, but it contains multiple self-joins and switch statements and it is a confusing mess. I'd much rather use a method that allows me to run multiple sql queries and pass parameters between them, all in one process, like I'd be able to do in SQL Server Stored Procedures, or through VBA.

    Hope that clears things up a little ... or at least doesn't make matters worse. :-)

  12. #12
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    good luck to you. if every thing is a dynamic you are fighting a losing battle. Sounds like a manual process to me.

  13. #13
    stmoong is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Location
    Singapore
    Posts
    108
    You may want to consider doing batch processing of the data from various sources and storing the processed data into a single database, then have the 3rd party application issue queries against the database.

    This gives you the opportunity to consolidate and possibly cleanup any bad data.

    Since you already know the relationship of the data in the various data sources, you can run the batch processing every night to consolidate the data. From your description, the data does not seem to be highly transactional, so you can do scheduled batch processing, for e.g. nightly.

  14. #14
    gyclone is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    7
    Quote Originally Posted by stmoong View Post
    You may want to consider doing batch processing of the data from various sources and storing the processed data into a single database, then have the 3rd party application issue queries against the database.
    This is exactly what is occurring, but all the batch processing does is move the data into this Access database and changing the batch processing is not an option. So, still, the data must be processed by either my query, or a function that my query can run against. This is a simplified version of my current query, omitting several of the entity types:

    Code:
    SELECT NewNotes.BSI_ID
     , NewNotes.Narrative
     , NewNotes.Create_Date
     , NewNotes.Trust_ID
    
    
    ,SWITCH( 
    	[ER_Master].[erm;ID] IS NOT NULL,  "ER"
    	, [EE_Master].[eem;ID] IS NOT NULL,  "EE"
    	, [DEP_Master].[dpm;ID], "DEP"
    	, RET_MAST2.[rmID], "RET" 
    	) AS [Entity_Type]
    
    
    , SWITCH(
    		[ER_Master].[erm;ID] IS NOT NULL, [ER_Master].[erm;ID]
    		,[ER_EE_Xref_MAIN2].[eex_Employer ID] IS NOT NULL, [ER_EE_Xref_MAIN2].[eex_Employer ID]
    		, RET_MAST2.[rmEmployer] IS NOT NULL, RET_MAST2.[rmEmployer]
    		, [ER_EE_Xref_MAIN4].[eex_Employer ID] IS NOT NULL, [ER_EE_Xref_MAIN4].[eex_Employer ID]
    		) AS [Employer_ID]
    		
    , SWITCH(
    	[EE_Master].[eem;ID] IS NOT NULL, [EE_Master].[eem;ID]
    	, RET_MAST2.[rmEmployee] IS NOT NULL, RET_MAST2.[rmEmployee]
    	, [EE_DEP_Xref_MAIN2].[edx_Employee ID] IS NOT NULL, [EE_DEP_Xref_MAIN2].[edx_Employee ID]
    	) AS [Employee_ID]
    	
    , [DEP_Master].[dpm;ID]
    
    
    
    
    , IIF (INSTR(NewNotes.Narrative, "(")>0, 
    		IIF(INSTR(NewNotes.Narrative, ")") > 0, 
    			IIF(ISNUMERIC(Mid(NewNotes.Narrative, (instr(NewNotes.Narrative,"(")+1), (instr(NewNotes.Narrative,")") - (instr(NewNotes.Narrative,"(")+1)))), Mid(NewNotes.Narrative, (instr(NewNotes.Narrative,"(")+1), (instr(NewNotes.Narrative,")") - (instr(NewNotes.Narrative,"(")+1))), NULL)
    		, null)
    	, null) AS [Call Category ID]
     
     
     
    FROM ((((((((((NewNotes 
    	-- Join to each of the entity master tables
    	LEFT OUTER JOIN BSFIL020 AS [ER_Master] ON NewNotes.BSI_ID = [ER_Master].[erm;ID])
    	LEFT OUTER JOIN BSFIL022 AS [EE_Master] ON NewNotes.BSI_ID = [EE_Master].[eem;ID])
    	LEFT OUTER JOIN BSFIL023 AS [DEP_Master] ON NewNotes.BSI_ID = [DEP_Master].[dpm;ID])
    	LEFT OUTER JOIN RET_MAST2 ON NewNotes.BSI_ID = RET_MAST2.[rmID])
    	
    	
    	-- Get's most recent dependent entry, must join again to get employee ID
    	LEFT OUTER JOIN
    				(SELECT [EE_DEP_Xref_SUB].[edx_Dependent ID], MAX([EE_DEP_Xref_SUB].[edx_Effective Date]) AS [Max_Date]
    				FROM BSFIL069 AS [EE_DEP_Xref_SUB]
    				GROUP BY [EE_DEP_Xref_SUB].[edx_Dependent ID]) AS [EE_DEP_Xref_MAIN]
    				ON [DEP_Master].[dpm;ID] = [EE_DEP_Xref_MAIN].[edx_Dependent ID])
    	
    	-- Get employee ID from Dependents			
    	LEFT OUTER JOIN BSFIL069 AS [EE_DEP_Xref_MAIN2] ON (([EE_DEP_Xref_MAIN].[edx_Dependent ID] = [EE_DEP_Xref_MAIN2].[edx_Dependent ID]) AND ([EE_DEP_Xref_MAIN].[Max_Date] = [EE_DEP_Xref_MAIN2].[edx_Effective Date])))
    				
    	-- Get's most recent employee entry (starting from Dependent), must join again to get employer
    	LEFT OUTER JOIN
    				(SELECT [ER_EE_Xref_SUB2].[eex_Employee ID], MAX([ER_EE_Xref_SUB2].[eex_Effective Date]) AS [Max_Date]
    				FROM BSFIL068 AS [ER_EE_Xref_SUB2]
    				GROUP BY [ER_EE_Xref_SUB2].[eex_Employee ID]) AS [ER_EE_Xref_MAIN3]
    				ON [EE_DEP_Xref_MAIN2].[edx_Employee ID] = [ER_EE_Xref_MAIN3].[eex_Employee ID])
    	
    	-- Get Employer starting from Dependent			
    	LEFT OUTER JOIN BSFIL068 AS [ER_EE_Xref_MAIN4] ON (([ER_EE_Xref_MAIN3].[eex_Employee ID] = [ER_EE_Xref_MAIN4].[eex_Employee ID]) AND ([ER_EE_Xref_MAIN3].[Max_Date] = [ER_EE_Xref_MAIN4].[eex_Effective Date])))
    	
    	-- Get's most recent employee entry (joined on Employee Master), must join again to get employer
    	LEFT OUTER JOIN
    				(SELECT [ER_EE_Xref_SUB].[eex_Employee ID], MAX([ER_EE_Xref_SUB].[eex_Effective Date]) AS [Max_Date]
    				FROM BSFIL068 AS [ER_EE_Xref_SUB]
    				GROUP BY [ER_EE_Xref_SUB].[eex_Employee ID]) AS [ER_EE_Xref_MAIN]
    				ON [EE_Master].[eem;ID] = [ER_EE_Xref_MAIN].[eex_Employee ID])
    	-- Get Employer starting from Employee Master			
    	LEFT OUTER JOIN BSFIL068 AS [ER_EE_Xref_MAIN2] ON (([ER_EE_Xref_MAIN].[eex_Employee ID] = [ER_EE_Xref_MAIN2].[eex_Employee ID]) AND ([ER_EE_Xref_MAIN].[Max_Date] = [ER_EE_Xref_MAIN2].[eex_Effective Date])))
    ... As you can see, not the most maintainable code in the world.

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automating "Append" Query with Parameters
    By Monterey_Manzer in forum Queries
    Replies: 1
    Last Post: 10-04-2012, 12:00 PM
  2. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  3. Replies: 3
    Last Post: 03-20-2012, 10:00 AM
  4. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  5. UPDATE function "too few parameters"
    By eww in forum Programming
    Replies: 5
    Last Post: 05-11-2011, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums