Two Way Integration with ACCPAC 5.2 1

[NeilN]

One of our clients wants us to integrate a custom application (SQL Server back end) with ACCPAC Advantage 5.2 (also a SQL Server back end).

Basically we need to send customer info and invoices to ACCPAC and retrieve cash receipts info from ACCPAC.

Doing straight SQL inserts is probably a bad idea - can xAPI with VB.NET do what we require? If so, is there any good documentation on this and does the ACCPAC client need to be installed on the development machine? If not, can anyone suggest a way to do this? Thanks.

 

[jayconverse]

SQL inserts are a terrible idea, don't go there.

The xAPI, or better yet, the COMAPI performs marvelously.

Yes, you need the Accpac client installed to use the xxxAPI functions.

To see what you need to do on the Accpac side, record a macro that adds a customer, then wrap it with SQL queries.

Jay Converse
IT Director
Systemlink, Inc.

 

[zemp]

I agree with Jay, use the COMAPI. It basically uses the same views, and therfore business logic, that ACCPAC does.

Minimum requirements if you are using them from outside of ACCPAC (VB.Net) are the SM installed locally and an available IAP licence.

zemp

 

[NeilN]

Thanks very much for this! I've read other threads on this site and have some related questions:

1) Is it correct to say COMAPI only works within ACCPAC? If so, is it possible to schedule a macro to run at a certain time?

2) If I can't schedule a macro then I have to use something like VB.NET and xAPI instead of COMAPI, correct?

3) I've installed SM on the dev machine and verified there's an available IAP license. I can't seem to figure out how to direct ACCPAC to use an existing company database stored on the client's SQL Server box. Apologies if this is a newbie question but I haven't been able to get a hold of the installer.

 

[zemp]

First off I apologize for threads that I posted earlier where I said that the COMAPI is only used in a macro and the xAPI outside a macro. I was misinformed... just plain wrong. Here is the story.

Both can be used in either situation. The COMAPI is basically a wrapper around the xAPI (the COMAPI uses the xAPI) giving the programmer access to more features (methods and events) such as Gonext and Goprevious to move within a recordset. It's is basically a more user friendly xAPI, and thankfully so. xAPI can be used from ACCPAC 4.2 , COMAPI only from 5.0.

ACCPAC macro's use the COMPAI by default. You can however write a macro using the xAPI but I don't recommend it.

When outside of ACCPAC you need add a reference to the COMAPI object library and then declare and open a Session object. A macro can use the existing session object opened by ACCPAC. FRom VB or VB.Net you need to open that Session object yourself (hence the licence).

Here is some code to open a COMAPI session from VB.

Dim Session As New AccpacCOMAPI.AccpacSession
Dim DBLink As AccpacCOMAPI.AccpacDBLink

Session.Init "", "AS", "AS1000", "52A" 
Session.Open "ADMIN", "ADMIN", "SAMLTD", Date, 0, ""

The first three parameters for Session.Open method are Username, Password and Database ID. These need to be passed either hard coded or in variables. The DBLink object is needed when you are trying to open a view. As below,

Dim ARCUSTOMER As AccpacCOMAPI.AccpacView
   
DBLink.OpenView "AR0024", ARCUSTOMER

The syntax for VB.Net is slightly different. I am not 100% sure of it.

I am not sure about scheduling a macro.




zemp

 

[NeilN]

Hi zemp. I can't seem to set up the DBlink properly - get an automation error on Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

If it helps, here's my code:

Sub ExportClients()
Dim connStewardship As New ADODB.Connection
Dim rsClients As New Recordset
Dim accSession As New AccpacCOMAPI.AccpacSession


On Error GoTo ACCPACErrorHandler

accSession.Init "", "AS", "AS1000", "52A"
accSession.Open "ADMIN", "TEST", "TSTDAT", Date, 0, ""

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

Dim ARCUSTOMER1 As AccpacCOMAPI.AccpacView
Dim ARCUSTOMER1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0024", ARCUSTOMER1
Set ARCUSTOMER1Fields = ARCUSTOMER1.Fields

Dim ARCUSTSTAT2 As AccpacCOMAPI.AccpacView
Dim ARCUSTSTAT2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0022", ARCUSTSTAT2
Set ARCUSTSTAT2Fields = ARCUSTSTAT2.Fields

Dim ARCUSTCMT3 As AccpacCOMAPI.AccpacView
Dim ARCUSTCMT3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0021", ARCUSTCMT3
Set ARCUSTCMT3Fields = ARCUSTCMT3.Fields

connStewardship.Open ConnectionString:="DSN=Fred", userid:="wilma", Password:="barney"
Set rsClients.ActiveConnection = connStewardship
rsClients.CursorLocation = adUseClient

rsClients.Source = "select company_id, company_name, address1, address2, address3, city, province, postal_code, country, phone_number, fax_number from companies"
rsClients.Open

If rsClients.RecordCount > 0 Then
rsClients.MoveFirst

Do While Not rsClients.EOF
ARCUSTOMER1Fields("IDCUST").Value = "FRED" + CStr(rsClients.Fields.Item(0).Value)

ARCUSTSTAT2.Init
ARCUSTSTAT2Fields("IDCUST").PutWithoutVerification ("FRED" + CStr(rsClients.Fields.Item(0).Value)) ' Customer Number
ARCUSTSTAT2Fields("CNTYR").PutWithoutVerification ("2004") ' Year
ARCUSTSTAT2Fields("CNTPERD").Value = "06" ' Period

ARCUSTCMT3.Init
ARCUSTCMT3Fields("IDCUST").PutWithoutVerification ("FRED" + CStr(rsClients.Fields.Item(0).Value)) ' Customer Number
ARCUSTCMT3.Browse "(IDCUST = ""FRED" + CStr(rsClients.Fields.Item(0).Value) + """)", 1
ARCUSTOMER1Fields("NAMECUST").Value = rsClients.Fields.Item(1).Value ' Customer Name

ARCUSTOMER1Fields("IDGRP").Value = "CSR" ' Group Code
ARCUSTOMER1Fields("TEXTSNAM").Value = Left(rsClients.Fields.Item(1).Value, 15) ' Short Name

ARCUSTOMER1Fields("CODETAXGRP").Value = "GPST" ' Tax Group
ARCUSTOMER1Fields("TEXTSTRE1").PutWithoutVerification (rsClients.Fields.Item(2).Value) ' Address Line 1
ARCUSTOMER1Fields("TEXTSTRE2").PutWithoutVerification (rsClients.Fields.Item(3).Value) ' Address Line 2
ARCUSTOMER1Fields("TEXTSTRE3").PutWithoutVerification (rsClients.Fields.Item(4).Value) ' Address Line 3
ARCUSTOMER1Fields("NAMECITY").PutWithoutVerification (rsClients.Fields.Item(5).Value) ' City
ARCUSTOMER1Fields("CODESTTE").PutWithoutVerification (rsClients.Fields.Item(6).Value) ' State/Prov.
ARCUSTOMER1Fields("CODEPSTL").PutWithoutVerification (rsClients.Fields.Item(7).Value) ' Zip/Postal Code
ARCUSTOMER1Fields("CODECTRY").PutWithoutVerification (rsClients.Fields.Item(8).Value) ' Country
ARCUSTOMER1Fields("TEXTPHON1").PutWithoutVerification (rsClients.Fields.Item(9).Value) ' Phone Number
ARCUSTOMER1Fields("TEXTPHON2").PutWithoutVerification (rsClients.Fields.Item(10).Value) ' Fax Number
'ARCUSTOMER1Fields("EMAIL2").PutWithoutVerification (rsClients.Fields.Item(11).Value) ' E-mail

ARCUSTOMER1.Insert

rsClients.MoveNext
Loop
End If

rsClients.Close
connStewardship.Close

Set ARCUSTCMT3 = Nothing
Set ARCUSTSTAT2 = Nothing
Set ARCUSTOMER1 = Nothing

Exit Sub

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long

If Errors Is Nothing Then
MsgBox Err.Description
MsgBox "VBA Macros cannot run where Accpac is deployed as a Web Server"
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

 

[zemp]

You must open the DBLink object with the .openDBLink method of the session object. In your code the .openDBLink method is not properly qualified. Try,

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = [b]accSession.[/b]OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

zemp

 

[NeilN]

Thanks! One step closer!

 

[kwagz]

Hey Guys

I am quite new to tek-tips. I have a question and I dont know how to post it. Please help

kwagz

 

[zemp]

kwagz, just go to the forum page and scroll to the bottom until you see the 'Start a new thread' section. enter your question and click 'submit'.

zemp