Calling VBA Function from Access SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings! I am querying linked Oracle tables from Access 2000. One of the
tables that I am querying has a new column added to the structure every month
to record end of month data (terrible design, but I didn't build it). I need
to sum all of the columns using SQL for each record when I run my query. I
haven't been able to figure out a way to tell SQL to "sum all of the columns
starting at column three to the last column in the row" in such a way that it
will work without modification the next month when another column is added.

What I would like to do is to create a function in VBA that could look at
the table structure, determine the number of columns, run the appropriate
query to sum all of the columns, and return the result set to the SQL code as
the result of subquery. However, I can't seem to figure out how to call a VBA
function from SQL. Can this be done?

Or does someone have another idea? Ideally, I could do the whole thing
without VBA at all.

Thanks in advance for your help!
 
I think I would start by hunting up the person who designed that system and
nail him/her to the wall, insist that the design be changed, and refuse to
relent until the change is made.

In the event that doesn't work, then if we we knew more about the data
structure, someone here might be able to offer a solution.
 
You can call a VBA function if the SQL is running inside
Access. Just write the VBA function, put it in a standard
code module, and refer to the function by name in your SQL.
If the function name is not recognised, either your
security settings prevent you from running user defined
functions, or else you aren't actually running the SQL
'inside Access'. Post again if you have problems after
you have tried it.

(david)
 
Back
Top