Is my thought process correct?

[kIreland]

Help!...
I am totally green to using AccPac, not to mention VBA.
Why should this deter me?
Anyway.. My boss - who expects miracles...
Wants me to pass information from our e-commerce site - into AccPac
Advantage.

From waht I have been reading - and piecing together via forums...
In order for me to do this I must connect to the AccPac DB via Accpac COMAPI
via AccPac's Product "ODBC Link".


Am I right in thinking that this is the correct path to what I need to do:

website >> ODBC LINK >> ACCPAC COMAPI >> ACCPAC DB


My intention is to build a Component that will pass the needed info from the
website to the Accpac DB whenever an order is placed.

Now I have messed around writting DLL's a while back...
I am completely rusty on how to go about setting up an environment where I can
do the development.

Can anyone give me pointers, words of advice.. pointers to other resources
for AccPac?

Anything to help clear up my confusion...

 

[zemp]

I would look into using the AccPac xAPI (a4wcomm.dll). This is a set of tools that allow you to access the AccPac DB via Visual Basic. You can easily insert data directly into the AccPac DB with the xAPI and the xAPI will ensure that your AccPac companies business logic and data integrity is maintained.

Baically you will want to create a dll or app which can take the data fron your website and pass it to the xAPI. If you use a dll then you wesite will need to push the data to the dll. if you use an app then app can pull the data from your website. It all depend on who initiates the transfer.

I would assume that your website has data stored in some database. Therefore the dll or app will work directly with that database. That being the case I would use an app to get the data from the websites database and bypass the website all together.

That would leave me with something like this.

WebSite DB <--> VB app --> xAPI --> AccPac DB

I would use ADO objects to make the two way connection. The connection between the app and Website DB can be direct or use ODBC. To break that connection down further,

WebSite DB <--> ADO <--> VB app

I hope that this information has been helpful. Thanks and Good Luck!

zemp

 

[kIreland]

Hi zemp,
Thank you for the reply and help.
I need to transfer the data automatically.
If I build a stand alone app to do this - I must then manually execute something to trigger the transfer.
I need the data to be inserted automatically at the time the order is placed.
I assume the only way to do this is via dll and pass the data live at the time of the order.

Also - Accpac has a product ODBC Link - I assume I need this in order to do what I want to do? Am I right or wrong in that assumption?


Thannks and Have A Great Day.

 

[zemp]

If you want to pass the data when an order is placed then you will want your website to pass the data to a dll. Usually when a 'submit' or similar button is clicked. How will depend on what your website is written in or with and the tools it uses.

You may need an ODBC link from the website to the dll but not from the dll to AccPac. The xAPI will take care of this. Thanks and Good Luck!

zemp

 

[DjangMan]

I would *strongly* recommend using the OLE interface through the COM layer - ie a VBA macro to put the data into ACCPAC. Using the ACCPAC views you are assured that your data is being posted correctly to the database and that all of the necessary referential integrity checks are being made. For those who don't know, ACCPAC's user interface, macros and import/export functionality all go through the views. The view maintains the business logic.

Now when you want to read data from ACCPAC - use ODBC. The views will be way too slow. Since you're just reading the data you can select the exact tables and fields that you need. All ACCPAC databases can use an ODBC link. You only need ACCPAC's ODBC when you have an older Pervasive database or ACCPAC Plus. Pervasive 2000, MSSQL, ORACLE, etc all have ODBC drivers.

As for how your website will pass along the order to ACCPAC is a matter of opinion, preference and the tools you're using. Is your site using ASP, PHP or something else? How are you handling the shopping cart? Would it make more sense for the client to purchase a proven solution such as ePOS, Icinity or eTransact? It might seem like a big chunk of cash but think about all the time you'll have to spend analysin, documenting, planning, coding, testing, fixing, testing, fixing, testing, etc.

DjangMan

 

[zemp]

The basis of the xAPI is also to use the views. Only this allows quick access to the data as well as inserts and updates without Accpac.

However, I like DjangMan's suggestion of seeing if there is a tool or program that can already do what you need done. Before you and your company invest a lot of time and money in the project.

One more thing with the xAPI. You will need an available IAP user for it to connect with the database. Thanks and Good Luck!

zemp

 

[kIreland]

From what I have read the ONLY way to write to the AccPac db is Via xapi - esle corruption is inevitable.


My website is built in Javascript, ASP, with some XML/XSLT thrown in for good measure.
What do you mean &quot;How are you handling the shopping cart?&quot;

Finding a suitable tool to do what I need - is a possibilty... Any ideas on where to look? I currently do not have support from AccPac - so I am on my own here...

Again thank you both for your help..

 

[zemp]

DjangMan wrote, &quot;I would *strongly* recommend using the OLE interface through the COM layer - ie a VBA macro to put the data into ACCPAC.&quot; Therefore I would assume that you can also use this suggested method. But to be honest I don't know this method, I have just used the xAPI.

It seems that either will put data into accpac properly, ensuring business logic and data integrity, so the choice will be up to you.

&quot;Shopping Cart&quot; is an internet term to describe what you are ordering from a website. All the products for one order are in your &quot;Shopping cart&quot; so to speak before you place your order. Similar to a grocery store check out line.

If you are already familiar with XML then you can pass your data in an XML document to the ADO in a DLL and maybe even to a macro. Thanks and Good Luck!

zemp

 

[DjangMan]

Sorry - the COM layer will use the XAPI ultimately. I wouldn't say that corruption is inevitable but if you stick to the view method your a** is covered when you decide to implement version 5.1 and all of your text fields double in size or ACCPAC decides to add a third table to the Order Header view.

The part where I can't give you help in detail is processing the list of things that the customer has ordered. I suppose an ASP page would create a new ACCPAC Object (e.g. Session = ACCPACXAPI.Session), create a new order header, query the user's list of goods in the shopping cart and create detail lines for the order, post the order and then off to a confirmation page with the order number that ACCPAC generated. There. I said it in one long sentence so it can be done. ;-p.

What you might do is start recording a macro in ACCPAC and enter an order into the system with data you might be getting from the website. Once you've posted the order stop recording the macro and you have all the VBA you need to import that order. I think you should be able to take that logic and most of that code, un hardcode where necessary and move it over to an ASP page as I've described above...

Good luck!

DjangMan

 

[kIreland]

DjangMan

Are you saying that I can do all of this from the ASP without writting a DLL? hmm... how would I get ot the xapi?

My concern is not what I need to get from my shopping cart/DB - I've written it all - I know it inside and out.

My concern is what Accpac requires to maintain referential integrity, etc.
I assume once i get in there and learn about these views - it will become much clearer on what I need to insert and in what order.

You said one thing that concerns me...
The generation of order numbers. I already have an order number - can I use my order number - or must I have AccPac generate a number for me?

 

[DjangMan]

Hi kIreland,

You can access the xapi through the COM layer that ACCPAC provides. Record a VBA macro in ACCPAC and the code that is generated should be pretty close to code you'll use in your ASP page. Your ASP setup needs sufficient rights to be able to access ACCPAC in this way so there's a potential area where security is an issue.

I'm going to sound like a broken record but record a macro to learn what fields are populated and in what order, which methods are called and when you have to update a table vs. post a table.

You can pass your own order number to ACCPAC. ACCPAC will report an error back to you if it already exists when you try to save the order (but I'm sure you're generating unique order #'s anyway).

If you want to let OE generate it's own numbers you can populate the ORDNUMBER field with &quot;*** NEW ***&quot; and when you insert the document the view will know to generate the next number in sequence. You could then store your web # in the PO field or anywhere in the Order Header. You have some flexibility here.

DjangMan

 

[kIreland]

DjangMan,
Thank you again for your help.
broken records are ok - sometimes you need to hear it over and over again - to get the hint.. LOL.

Problem is right now my installation of Accpac is toast.
I cannot record a macro.
I cannot do anything in AccPac - unless I access it via the web browser. And of course I cannot record Macros via the browser. So I am flying in a holding pattern for right now.

We are getting a new machine especially for AccPac - and then the Systems guys will install AccPac... And then it is in my hands.
Right now I am just asking questions to get a general idea of what I need to do - when I can do something.

 

[zemp]

kIreland, the xAPI should be included with Accpac. Search for the a4wcomm.dll (usually in the Accpac\runtime directory) if it there then you have the xAPI. Thanks and Good Luck!

zemp

 

[DjangMan]

Ah - I think you mentioned your machine already.

Good luck and let us know of your progress.

DjangMan

 

[kIreland]

zemp & DjangMan,
Thank you for your help.
I am sure you will be hearing from me again - as I begin to delve into the api and views.

Thanks again for your assistance.
Kim