NOT ABLE TO READ OR WRITE AP AGING TO A FILE 1

[sakmer123]

Hi,

I have to write a program in either VB6 or C# (preferably) to extract data for AP and AR Aging from ACCPAC 5.3.
The steps that I have taken are:

1) Run a macro in ACCPAC
2) Initialize a session using COMAPI
3) Open a View AP0043
4) Process (APAGING1.Process)

Essentially other than opening a session and stripping the code of all references to the code processing and writing to a Crystal report, all the steps are as they exist in the default macro.

My questions are:
1) Can I store the values returned by the DLL in a file (text file) or ARRAY or read it and store it in a database?As I do not wish to write it to a crystal report.
2) When I write APAGING1.Read or APAGING1.GoTop or any other recordset function it gives me an error?
3) Is this view that I am using AP0043 in any way different from the other views that ACCPAC has. I am an absolute newbie at this.
4) I used this code as a proof of concept in the code just to see if I am doing the necessary steps and this ran properly.
*****************************
Dim CSRATETYPS1 As AccpacCOMAPI.AccpacView
mDBLinkSysRW.OpenView "CS0004", CSRATETYPS1
CSRATETYPS1.Fields("RATETYPE").Value = "SP"
Dim Status As Boolean
Status = CSRATETYPS1.Read
If Status = False Then
MsgBox "The SP record does not exist."
Else
MsgBox "No Problems"
Exit Sub
End If

************************************

I have attached the code that I have written, any help will be highly appreciated.

Regards

'************************************************
Sub MainSub()

On Error GoTo ACCPACErrorHandler

Dim Session As AccpacCOMAPI.AccpacSession
Set Session = New AccpacCOMAPI.AccpacSession

Session.Init "", "AP", "AP0043", "53A"
Session.EnforceAppVersion = False
Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0, ""

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = Session.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = Session.OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)


Dim APAGING1 As AccpacCOMAPI.AccpacView
Dim APAGING1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0043", APAGING1
Set APAGING1Fields = APAGING1.Fields

temp = APAGING1.Exists
APAGING1.Init


APAGING1Fields("CMNDCODE").PutWithoutVerification ("51") ' Command Code
APAGING1Fields("AGEINVDTSW").PutWithoutVerification ("0") ' Due Date / Invoice Date
APAGING1Fields("AGEPERIOD2").PutWithoutVerification ("30") ' First Period
APAGING1Fields("AGEPERIOD3").PutWithoutVerification ("60") ' Second Period
APAGING1Fields("AGEPERIOD4").PutWithoutVerification ("90") ' Third Period
APAGING1Fields("SWDETAIL").PutWithoutVerification ("2") ' Detail / Summary Report
APAGING1Fields("SWOPTMETER").PutWithoutVerification ("1") ' Display Meter
APAGING1.Process

'TRYING TO READ VALUE FROM THE FIELD - GIVES ME THE LAST RECORD OF IDVEND
' IF I TRY TO WRITE ANYTHING HERE APAGING1.GOTOP / PREVIOUS ETC. IT GIVES ME AN ERROR SESSION NOT INITIALIZED

MsgBox (APAGING1Fields.FieldByName("IDVEND").Value)

'THE CODE SNIPPET BELOW IS JUST A TEST AND HAS NOTHING TO DO WITH THE CODE ABOVE OR BELOW

'***********************************************************

Dim CSRATETYPS1 As AccpacCOMAPI.AccpacView
mDBLinkSysRW.OpenView "CS0004", CSRATETYPS1
CSRATETYPS1.Fields("RATETYPE").Value = "SP"
Dim Status As Boolean
Status = CSRATETYPS1.Read
If Status = False Then
MsgBox "The SP record does not exist."
Else
MsgBox "No Problems"
Exit Sub
End If

'***********************************


Set Session = Nothing

Exit Sub

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count

If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next

End If

End Sub

'****************************************************

 

[jayconverse]

You can't do that at all, it's a virtual view. Run the aging report, and work from the CSV file that's created.

Jay Converse
IT Director
Systemlink, Inc.

 

[sakmer123]

Hi Jay,

Thanks for the info. I have a couple of more questions with regards to your reply.

1) Is there anything to differentiate the virtual views from the normal views? Any links/help file would be appreciated that explains this.
2) You have written about the csv file, I tried looking for a csv file that ACCPAC generates, I could not find any. The way that I create this report in ACCPAC is -
AP Transaction Reports --> Aged payables. I am not aware of any other way of creating this particular report. Or do you mean that I have to export the Crystal Report in csv format?

Thanks again for the quick response and looking forward to a reply.

Regards

 

[jayconverse]

1. The way you know is if there's a table with the same name as the view. ARCUS and GLAMF are views that have tables.

2. Record a macro that runs the aging report, then look at the value of ARAGING1.Fields("FILENAME")

Jay Converse
IT Director
Systemlink, Inc.

 

[sakmer123]

Hi Jay,

Thanks for the response, I know this can be dumb, but I cannot see the csv file. I inserted this code
MsgBox (APAGING1.Fields("FILENAME").Value)
to view the path and name of the file (.csv) but this makes a file (.tmp) under "c:\documents and settings\....\tfc84.tmp"

When I navigate to this folder I cannot see this file(tfc84.tmp)

I know this is not right but I tried writing to this file, doing this, creates this file 111.csv and writes the LAST IDVEND value (only one record).

Open "c:\temp\111.csv" For Output Access Write As #1
Print #1, "Cust ID: " & APAGING1.Fields("IDVEND").Value & vbCrLf
Close #1

Thanks once again for the response and bearing with me.

Regards

 

[jayconverse]

You need to stop the process before it prints, because the file gets deleted. From here:

ARAGING1.Init
ARAGING1Fields("CMNDCODE").PutWithoutVerification ("51")
ARAGING1Fields("AGEINVDTSW").PutWithoutVerification ("0")
ARAGING1Fields("AGEPERIOD2").PutWithoutVerification ("30")
ARAGING1Fields("AGEPERIOD3").PutWithoutVerification ("60")
ARAGING1Fields("AGEPERIOD4").PutWithoutVerification ("90")
ARAGING1Fields("SWDETAIL").PutWithoutVerification ("2")
ARAGING1Fields("SWOPTMETER").PutWithoutVerification ("1")

ARAGING1.Process

' Now you can get the file name, and you don't need the rest that follows

rpt.SetParam "FILENAME", ARAGING1.Fields("FILENAME")
rpt.NumOfCopies = 1
rpt.Destination = PD_PREVIEW
rpt.PrintDir = ""
rpt.PrintReport

' This final process is what deletes the file
ARAGING1Fields("CMNDCODE").PutWithoutVerification ("13")
ARAGING1.Process

Jay Converse
IT Director
Systemlink, Inc.

 

[sakmer123]

Hi Jay,

Thanks Jay, you are the best, your responses were not only first rate but also exceptionally quick.

The issue was resolved, actually the *.tmp file was hidden. I converted this tmp file into a csv and have cleaned the file according to my needs. Now I have to figure a way out to automatically pick this file, convert to csv and populate a dataset. But ofcourse that has nothing to do with the initial query that I had.

Thanks for everything

Regards

Merchant

 

[ettienne]

The file name can be read from the FILENAME field in the aging view, so you have no problem there.
If you have some ADO or DAO skills you can attach the CSV file to an Access database and query it that way, you can actually bypass Access and query the file directly using ADO, there are many options, it just depends what you want to do.