Adding a Field to the Access Database

[johngwms]

I need to extract data directly from the Actinic Access Database to import into my CRM system.

I need to add a new field to some Actinic Database tables to flag when data has already been exported.

If I create this within Access 2007, does anyone have any experience of doing this sort of thing and can comment on the practical consequences of doing so.

For example, is it likely to affect the way Actinic works.

John

[Jan]

I wouldn't recommend modifying the Actinic database tables as doing so can cause snapshot to stop working.

I would suggest working the other way around and keeping a note of what has an hasn't been copied in your local database, be careful with this as well because some of the primary table keys can change - section id on the section table is an example, if you move the section the section id might change, product reference is another, this primary key can be changed by the user.

If that doesn't sound good, then you can add tables to the Actinic database and liink by primary keys, your tables may be lost on snapshot import though (not all snapshot imports, it depends on the type, whether you are including orders etc).

Regards,

[johngwms]

Thanks Jan.

I just need to extract the new order and customer information from the Actinic Database. I will not be touching tables associated with the Actinic Layouts etc.

Does that affect your advise?

John

[Jan]

I wouldn't recommend changing those - I tried adding an id to the order detail table a few years ago and testing showed that it broke snapshot - so I had to take it out again.

Regards,

[johngwms]

OK, I really value this - thank you.

I suppose another way of doing what I need would be to write some code within Actinic Order Processing to export order information to an external table/spreadsheet whenever a new order arrives?

John

[Mike Hughes]

It might be possible to do this the way you want to. Jan would know better than me, but it could be that there are fields already associated with an order that you could use for your purpose.

One that comes to mind is the highlight colour field. You could set this to a certain colour to show the order has been exported into the CRM. This way you have a field you can manipulate and you'll be able to see inside Actinic which orders have been exported.

Mike

[Jan]

Yes, that is definitely the way forward, it's how we control our drop ship system. It gives feedback to the user as well and so is a good system.

Regards,

[cbarling]

Under Settings | Business Setting | Order Processing tick "Enable Export to files" then in the associated options you can define a program that can be run after the export.

You can then export orders at Operations | Report Selection | Export | Export outstanding orders. This will just export orders that haven't been sent before.

Or, look up "command lines" in the help and you can see that you can trigger this externally using the /x switch.

So you should be able to do most if not all of what you want without messing with Actinic's internals.

Chris