UDF in Make Table Query

[Guest]

I have a make table query that calls a (public) user defined function
(Percentage).
When I try to execute the make table from outside of Access97, eg from Excel
I get an error 3085: Undefined function 'Percentage' in expression.
This is what Excel calls:

Dim myDatabase As Database
Dim tblDelTbl As TableDef
Dim qryMakeTbl As QueryDef
Dim mkTable(1, 1) As String
Set myDatabase = OpenDatabase(myDB)
'updates each table listed in the mkTable array
For i = 0 To UBound(mkTable)

Set tblDelTbl = myDatabase.TableDefs(mkTable(i, 0))
myDatabase.TableDefs.Delete (mkTable(i, 0)) 'delete the tables first

Set qryMakeTbl = myDatabase.QueryDefs(mkTable(i, 1))
qryMakeTbl.Execute 'remake the tables
Next i

 

[Marshall Barton]

I have a make table query that calls a (public) user defined function
(Percentage).
When I try to execute the make table from outside of Access97, eg from Excel
I get an error 3085: Undefined function 'Percentage' in expression.
This is what Excel calls:

Dim myDatabase As Database
Dim tblDelTbl As TableDef
Dim qryMakeTbl As QueryDef
Dim mkTable(1, 1) As String
Set myDatabase = OpenDatabase(myDB)
'updates each table listed in the mkTable array
For i = 0 To UBound(mkTable)

Set tblDelTbl = myDatabase.TableDefs(mkTable(i, 0))
myDatabase.TableDefs.Delete (mkTable(i, 0)) 'delete the tables first

Set qryMakeTbl = myDatabase.QueryDefs(mkTable(i, 1))
qryMakeTbl.Execute 'remake the tables
Next i

The problem is that you are not using Access, which is the
program that integrates SQL with VBA so it can execute
functions. You are only using DAO to connect to Jet, which
manages your table's data and executes SQL statements.

I know this is confusing, but try to think of Access as a
user interface development tool that also provides an easy
way to get to the VB programming environment and the Jet
database engine.