Different flavors of vba?

[mohobrien]

I can run this sql from another db in the 'access vba enviroment' and it runs fine. There is a function 'GetOwners' used in the query. If I try within the 'arcgis vba enviroment', the function isn't recognized.
Any thoughts?

This is the query

 Public Sub MakeSearchBook()

 Dim Mydb As DAO.Database
Set Mydb = OpenDatabase("G:\Common\Access_Databases\New Folder\DB_Tables\MetallicMineralsTables.mdb")
Mydb.Execute "SELECT DISTINCTROW ALLDISP.[Disposition Number] AS Disp_num, ALLDISP.[Current Status], GetOwners([ALLDISP].[Disposition Number]) AS OWNERS, ALLDISP.Location, ALLDISP.[Area (Hectares)], ALLDISP.[NTS Map Reference], ALLDISP.[Effective Date], ALLDISP.[Grouping Certificate], ALLDISP.[Mining District], ALLDISP.[Total Available Expenditures], ALLDISP.[Assessment work awaiting approval by Geology Branch (Y/N)], ALLDISP.[Date Protected to] AS [In Good Standing to], ALLDISP.[Applied Assessment work for year ending], ALLDISP.Incurred, ALLDISP.[Not Incurred], ALLDISP.[Relief from Assessment Work], ALLDISP.[Non-Refundable Cash Deposit] " & _
"INTO [DBASE 5.0;DATABASE=C:\].[Test1.dbf] " & _
"FROM ALLDISP " & _
"WHERE (((ALLDISP.[Current Status]) Like 'ACT*')) " & _
"ORDER BY ALLDISP.[Disposition Number]"

 End Sub

And this is the function

Public Function GetOwners(Dispos As String) As String
    ' MAKE THE OWNER string
 Dim qdfGetOwners As DAO.QueryDef
 Dim rstGetOwners As DAO.RecordSet
 Dim strSQL As String
 Dim strOWNERS As String
 Dim db As DAO.Database
  Set db = OpenDatabase("G:\Common\Access_Databases\New Folder\DB_Tables\MetallicMineralsTables.mdb")
  Set qdfGetOwners = db.CreateQueryDef("")
 
 strSQL = "SELECT tblHolders.DispositionNumber, tblHolders.Holder, tblHolders.Percentage " & _
          "FROM tblHolders " & _
          "WHERE (((tblHolders.DispositionNumber)='" & Dispos & "')) " & _
          "ORDER BY tblHolders.Percentage DESC"
    
    With qdfGetOwners
            .SQL = strSQL
            Set rstGetOwners = .OpenRecordset()
    End With
    With rstGetOwners
            Do While Not .EOF
                If .OpenRecordset.Fields(0).Value = Dispos Then
                strOWNERS = strOWNERS & .Fields(1).Value & " " & .Fields(2).Value & "%" & "  "
                End If
            .MoveNext
            Loop
            strOWNERS = RTrim(strOWNERS)
    End With

    rstGetOwners.Close
    GetOwners = strOWNERS
End Function

 

[krc777]

I did find this. (http://gis.esri.com/library/userconf/proc01/professional/papers/pap417/p417.htm)

In the DDE architecture, the ArcView application and the Visual Basic application exist as two separate processes on the client system; DDE provides the means for passing data back and forth seamlessly between the two processes. The DDE communication was established as a two-way link between the two applications. Avenue scripts within the ArcView application were capable of calling various forms and functions from the Visual Basic (VB) portion of the application, and, likewise, the various VB forms and functions could call Avenue scripts whenever necessary.

While the DDE communication generally worked very well, there were, needless to say, several hurdles that had to be overcome before the system became stable enough to use on a consistent basis. The major difficulty was in making sure that the DDE connection was not only initiated at the proper time, but that it was also broken at the proper time. On both the Visual Basic and ArcView sides of the DDE link, there were several problems encountered in which the DDE link would either remain in a connected state even after it was no longer needed, which resulted in timeout errors, or the link would be broken prematurely, which resulted in a function or script continuing to process without having all of the information it needed to complete successfully.

 

[mohobrien]

Thanks for the response. Perhaps that refers to ArcView3.x generation. I would have thought 9.2 generation would have solved the problems. I guess I can always start an instance of Access from ArcView and run the sql that way.

 

[The_Doc_Man]

When a function isn't recognized, there is something blocking its visibility.

For access, to make a public function you must place the function in a general module and declare it public. Then it is visible anywhere inside of Access.

Don't know about ArcView but you need to find out the specifics of VBA for ArcView and global or public declaratons. Look up the help files for VBA in that product to see if there is something oddball about such declarations. Something extra required for the declaration, for example.

 

[mohobrien]

Problem solved. I hope!
http://forums.esri.com/Thread.asp?c=93&f=992&t=237561