Can I link to a view?

[downwitchyobadself]

I'm trying to query the set of company files presented in the open-company dropdown. According to the documentation, it is stored in an Accpac view--AS0020 to be precise--composed from a DLL.

Is there any way to directly query the view? Or do I have to invoke the VB methods and fill the list that way?

Thanks in advance for your help.

 

[Jay Converse]

This view does not point to a table, so you have to use VB methods to traverse it.

 

[DjangMan]

I think the session has a Organizations listing. I'm looking at some Delphi code I've written and I'm referencing a property of the session...

 

[Jay Converse]

Quite true, but you still can't "query" it like ADO.

 

[downwitchyobadself]

Is it a single-view call? Basic .Fetch or .Read I assume? (Can't really record the macro on this one to get the syntax.)

 

[Jay Converse]

Here's some old Xapi I found, I think DjangMan gave it to me years ago.

-------------- The call ---------------

Private Sub UserForm_Initialize()
Dim clsComps As New ACCPACCompanyList '---------GET ORGIDs
Dim sCurComp As String
Dim i As Integer
myCompID = clsComps.FetchCompanyIDs
myCompDesc = clsComps.FetchDescriptions
Dim x
For Each x In myCompDesc
If VBA.Trim(x) <> "" Then Me.DropDownCompany.AddItem x
If Trim(Session.Company) = Trim(myCompID(i)) Then sCurComp = x
i = i + 1
Next

Me.DropDownCompany.text = sCurComp

End Sub

------------- The class ----------------
' gets a list of available company ids
Public Function FetchCompanyIDs() As String()
Dim CompanyDef As ORGRecord
Dim rotoHandle As Long
Dim rotocount As Long
Dim ObjectID As String * 9
Dim test
Dim rc
Dim tmpStr() As String
ReDim tmpStr(NumOfOrgs)
test = orgOpen(0, rotoHandle)
CompanyDef.wSizeOrg = Len(CompanyDef)
CompanyDef.sOrgId = VBA.String(SIZEOF_ORGID, " ")
rc = orgGetGE(rotoHandle, CompanyDef)
Dim recCnt As Integer
Dim cnt As Integer
recCnt = 1
While rc = 0
If CompanyDef.wOrgType = ORG_TYPE_COMPANY Then
tmpStr(cnt) = VBA.Trim(CompanyDef.sOrgId)
cnt = cnt + 1
End If
rc = orgGetNext(rotoHandle, CompanyDef)
Wend
orgClose (rotoHandle)
FetchCompanyIDs = tmpStr
End Function
' gets the matching company descriptions for the company ids
Public Function FetchDescriptions() As String()
Dim CompanyDef As ORGRecord
Dim rotoHandle As Long
Dim rotocount As Long
Dim ObjectID As String * 9
Dim test
Dim rc
Dim cnt As Integer
Dim tmpStr() As String
ReDim tmpStr(NumOfOrgs)
test = orgOpen(0, rotoHandle)
CompanyDef.wSizeOrg = Len(CompanyDef)
CompanyDef.sOrgId = VBA.String(SIZEOF_ORGID, " ")
rc = orgGetGE(rotoHandle, CompanyDef)
Dim recCnt As Integer
recCnt = 1
While rc = 0
If CompanyDef.wOrgType = ORG_TYPE_COMPANY Then
tmpStr(cnt) = VBA.Trim(CompanyDef.sDesc)
cnt = cnt + 1
End If
rc = orgGetNext(rotoHandle, CompanyDef)
Wend
orgClose (rotoHandle)
FetchDescriptions = tmpStr
End Function
' finds on the number of companies available
Private Function NumOfOrgs() As Integer
Dim CompanyDef As ORGRecord
Dim rotoHandle As Long
Dim rotocount As Long
Dim ObjectID As String * 9
Dim test
Dim rc
Dim cnt As Integer
test = orgOpen(0, rotoHandle)
CompanyDef.wSizeOrg = Len(CompanyDef)
CompanyDef.sOrgId = VBA.String(SIZEOF_ORGID, " ")
rc = orgGetGE(rotoHandle, CompanyDef)
Dim recCnt As Integer
recCnt = 1
While rc = 0
If CompanyDef.wOrgType = ORG_TYPE_COMPANY Then
cnt = cnt + 1
End If
rc = orgGetNext(rotoHandle, CompanyDef)
Wend
orgClose (rotoHandle)
NumOfOrgs = cnt
End Function


--------------- The API definitions ------------------
Type ORGRecord
wSizeOrg As Integer
sOrgId As String * SIZEOF_ORGID
sDesc As String * SIZEOF_ORGDESC
wOrgType As Integer
sSystemOrgID As String * SIZEOF_ORGID
wDriverID As Integer
sDatabase As String * SIZEOF_DB
wSecLevel As Integer
sSignonID As String * SIZEOF_USERID
sSignonPW As String * SIZEOF_PASSWORD
fSecEnabled As Integer
wStatus As Integer
End Type


'Org type (database catagory)
Global Const ORG_TYPE_SYSTEM = 0
Global Const ORG_TYPE_COMPANY = 1

'Security levels
Global Const ORG_SECLVL_NONE = 0
Global Const ORG_SECLVL_DB = 1
Global Const ORG_SECLVL_USER = 2

'Status
Global Const ORG_ACTIVE = 0
Global Const ORG_INACTIVE = 1

'Errors
Global Const ORG_ERROR_SIZE = 1001

Declare Function orgOpen Lib "a4wapi.dll" (reserved As Long, ByRef lHandle As Long) As Long
Declare Function orgClose Lib "a4wapi.dll" (ByVal lHandle As Long) As Long
Declare Function orgGet Lib "a4wapi.dll" (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long
Declare Function orgGetGE Lib "a4wapi.dll" (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long
Declare Function orgGetNext Lib "a4wapi.dll" (ByVal lHandle As Long, ByRef cmpList As ORGRecord) As Long

 

[Jay Converse]

Or a simpler way with the COMPAPI:

Dim a4wLinkReadSys As AccpacDBLink
Set a4wLinkReadSys = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READONLY)

a4wLinkReadSys.OpenView "AS0020", AsOrg
AsOrg.Browse "" & AsOrg.Session.CompanyID, True

do while AsOrg.Fetch
----- Populate the dropdown here
loop

 

[downwitchyobadself]

Thanks, that helps a lot. The specific properties, in case anyone else needs them:

With AsOrg
Do While .Fetch
Debug.Print .Fields("ORGID").Value
Debug.Print .Fields("DESC").Value
Debug.Print .Fields("SYSORGID").Value
Debug.Print .Fields("DRIVERID").Value
Debug.Print .Fields("DATABASE").Value
Debug.Print .Fields("SECLEVEL").Value
Debug.Print .Fields("SIGNONID").Value
Debug.Print .Fields("SIGNONPW").Value
Debug.Print .Fields("SECENABLED").Value
Debug.Print .Fields("STATUS").Value
Loop
End With

 

[Jay Converse]

And when anybody reading this thread in the future asks, no, you _cannot_ get the database password with this method, the field is in the view, but it doesn't output.

 

[downwitchyobadself]

I left out one (useful) property:

Debug.Print .Fields("ORGTYPE").Value

1=Company 0=System

 

[DjangMan]

Tuba - thanks for the credit but that wasn't me.

 

[shulkie]

It wasn't me either, wonder who it could have been?