Access table valued function equivalent

[TimInAus]

Hi Guys,

I'm hoping someone can put me in the right direction. I have a C# app that uses access as the DB. I have some vba code that will create a table and populate it with some data. I now need a way to call the function and select from the new table externally.

I'm still relativly new to programming in vba but as far as I know you can't call a vba functional from outside the database. What I'm hoping for is some way I can select all from the function and have the function return a table, the same as table valued functions in MSSQL. e.g. SELECT * FROM myFunction()

Is this possible in assess? what would I need to do to my function to have it populate and return a table? I'm not trying to be spoon feed here, i'm just a little lost for direction. If someone can point me the right way i'm happy to do more resarch myself.

Thanks

rgds
Tim

 

[gemma-the-husky]

doesnt c# have intirinsic functions, or a suitable library to do what you want?

 

[TimInAus]

not a fuzzy search, that is the function that I need to select from

 

[Banana]

I don't think you can use VBA functions outside of Access, since it's the Access that does the magic. Excel developers has had that issue where they can't use say, Nz() function which is in Access but not available via Jet in other contexts. Here's another thread asking similar question with the answer

A possible workaround is to have the C# app automate the Access and run query within that automated instance but that means the C# app will now require Access or at least a runtime engine distributed and that may make it even more heavy.

 

[TimInAus]

Thanks guys,

I realise I can't access the vba from outside the database. But I can access stored queries. So if I can call the function from the stored query and have it return the results to the stored query then I'm in business.

I just need to know if I can have a stored query along the lines of the following SELECT * FROM myFunction(). The function would be called a table valued function in MSSQL server, I'm looking for the equivalent in Access.

cheers guys
Tim

 

[Banana]

Well, let's say you save a query in Access and name it "Query1". To use it then you would do this:

SELECT * FROM Query1;

Even so, I'm not confident that will enable you to run Query1 if it contains VBA. See how it goes.

 

[TimInAus]

Hi Banana,

This is acually for the fuzzy search function that you helped me out with not too long ago. The vba code;
1. creates a temp database
2. creates a table in the database
3. links the table created in step 2 to the main database
4. runs a query and inserts the results into the table created in step 2

I need a way to trigger the vba, and then select all from the temp table. I thought if there was an Access equivalent of the MSSQL table value function then the problem would be solved. I might be able to use a different approach if I'm not able to trigger the vba from outside the database or through a stored query.

Is it possible to do steps 1, 2 & 3 through OleDb? If so I might have a solution.

Thanks again for your help.

 

[Banana]

Hi, I guess I could have had worded my previous reply.

To be explicit - there's no support for table-valued function in Jet - that is pretty much MSSQL specific (come to think of it, I'm not sure if any other RDBMS supports this idea either... maybe Oracle?)

To run VBA, you have to automate Access to execute the query within Access context - you certainly can have Access/VBA insert the results into a temporary table and return the name of table to C# and C# can then link to that temp table. But that's practical as long as this is for say, server-side processing. If you had to distribute this C# app as a client app, you're looking at distributing Access runtime also to make this possible.

I gave you an example of how to use a stored query which I already expressed my doubts that even doing so would enable you to run VBA just because it's a stored query but at least you know what the syntax so we can be definitively certain whether the idea of hiding VBA calls in a stored query is possible.

The only missing piece in the puzzle, though, is that obviously for VBA to work in a Jet query, Access had to somewhat bind into Jet's Expression Service library or something and thus make the functions available to the Jet for calling. If Access could do that, then surely it could be done in C# or any other languages. Take this KB article for example - it seems to imply that in C++, one can in fact call a VBA-driven query, but doesn't say how and my cursory google has not turned anything useful that would help provide a clear example of how to tell Jet that such functions are available somewhere.

 

[TimInAus]

Thanks Banana. I don't think the query idea will work, I've tried something similar and access spits it when you try to save.

I've found a way to create databases/tables and link them through c# using ADOX and Jet. I'll move the vba code to c# as the reason I had it in Access was so I could manipulate the databases. This should be a lot cleaner approach anyway. thanks again Banana.

http://www.dotnet247.com/247reference/msgs/11/59426.aspx
http://www.devx.com/tips/Tip/20462