Importing to ACCPAC using ODBC Link or other

[flpro]

Hi there,

I need some advise.

I'm a programmer and I have an application that needs to have it's customers synchronized with ACCPAC (Advantage Series 5.2). The ACCPAC database is SQL Server 7.0.

Basically my application will get the customers from ACCPAC and compare them to the customers in my application. Then if I see that ACCPAC is missing customers or some customer information has changed then my application needs to update ACCPAC.

These are the options that I have right now:

1) Send a text file with the new customers/updated customer info so someone can import manually into ACCPAC. How can I get the file layouts? Which tables I'm supposed to update? A second step that I need to do is to send the billing information (monthly/quarterly, etc and the amount to bill) which tables and structure also.

2) Use ODBC Link? This is an interesting option, but I don't see any documentation about how to implement it. Does anyone know about it?

Any suggestions would be greatly appreciated.

Thanks.

 

[jayconverse]

1. You get the file layouts by exporting from the custom master.

2. ODBC is ODBC. But don't use it to do updates, use Accpac views. Record a VBA macro that updates customer information, and you'll get the basic code.

Jay Converse
IT Director
Systemlink, Inc.

 

[flpro]

Thanks Jay, had lost the link to this website.

Basically now the company wants to avoid importing files, they want me to enter customer and recurring billing information programmatically.

1) So basically you think it won't break ACCPAC to enter NEW customers programmatically straight into the ACCPAC tables from another application as long as I know which fields I need to update? and to get the fields that I need to fill out, I should create an export file of the customers from ACCPAC, right?

2) For updates, you don't recommend entering information programmatically straight into the accpac tables, you recommend "ACCPAC Views". What are these ACCPAC views?

3) You mention to record a VBA Macro that updates customer information. I'm lost, the only way that I can see getting code to see how the customer is being updated is if there's a feature in ACCPAC to record a macro. Unfortunately, I don't have access to ACCPAC all the time, I apologize for my ignorance.

Thank you!

 

[jayconverse]

1. Absolutely not! That's the fastest way to break data integrity and ruin the database.

2. Updating existing records can be done in a limited fashion via ODBC if you know what you're doing. Like updating names and addresses and credit limits.

3. Accpac is a COM-enabled program, like Excel or Word. From the main menu, select Macro/Record, do some stuff (like adding a new customer), stop recording, then look at the results. That's how every Accpac programmer in the world has learned what he knows. If you don't have the Accpac security rights to do this, then tell your boss you shouldn't on this project.



Jay Converse
IT Director
Systemlink, Inc.

 

[flpro]

Thanks Jay,

Oh, I got it, you guys control ACCPAC thru COM.

Do you know where I can get any technical papers on this? What methods and properties are exposed, etc.

About the security rights, what I meant is that I only see ACCPAC in their office, I don't have an ACCPAC installation at home or my laptop that I can play with. First time I've looked at ACCPAC, so I'm starting to get my feet wet.

Thanks.

 

[jayconverse]

The only technical papers available are what you get from the Accpac VBA training courses. I strongly suggest you take one or two. Look online for Accpac University.

Jay Converse
IT Director
Systemlink, Inc.