Need to Export Data From Access into SAGE BusinessWorks Acct. Syst

[Brad]

I have a pressing need to export data from an Access 2007 application into a
Sage BusinessWorks accounting system. (CSV file with three record types). I
would like to see if any other Access user has ever done this and I would
like to compare notes with them.

Thanks!!!

 

[Jeff Boyce]

Brad

Have you checked (?on-line) to see if Sage or someone else offers an ODBC
driver to connect Access & Sage? If one exists, it could save you the work
of exporting in CSV and importing into Sage.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

A quick check on-line revealed one possibility ... it sounds like Sage
Payroll may use .mdb (?!Access?!) tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brad]

Jeff,

Thanks for your help, I really appreciate it.

I am very new to Sage BusinessWorks and fairly new to the world of Access.
I am trying to find an easy way to feed order information into BusinessWorks
from an Access application. This will eliminate dual entry of data.

I have found some Sage documentation after quite a bit of hunting. It looks
like there is a facility to import Orders. I now need to make sure that all
of the data that is needed is available in the Access system. One of the
challenges is that the input into BusinessWorks requires 3 different types of
records, each with different fields, for each order (header, detail, summary).

I am not quite sure how I will be able to create this with Access.

Thanks again!

 

[send2steph]

Brad,
I hope you have it worked out by now. My solution would be to use OutputTo
to put records out to a text file and then use it again to append, doing it
three times for each record type.

Why I'm really writing is I'm hoping you might have some insight in
importing into BW in general. I want to update some fields in the Parts
table, but the fields are not included in the Import file description for
this table. (Namely, the comments fields.) What I would really like to do
is just update the data using SQL - but my MANAGER password is read-only.
Have you found anyway around that in your endeavors? Like a dev or
supervisor password or something. Normally I wouldn't work around a
program's protections - but I'm only talking updating text fields here - not
dates, prices, or anything else.

Thanks,
--Steph--

 

[send2steph]

Brad,
I hope you have it worked out by now. My solution would be to use OutputTo
to put records out to a text file and then use it again to append, doing it
three times for each record type.

Why I'm really writing is I'm hoping you might have some insight in
importing into BW in general. I want to update some fields in the Parts
table, but the fields are not included in the Import file description for
this table. (Namely, the comments fields.) What I would really like to do
is just update the data using SQL - but my MANAGER password is read-only.
Have you found anyway around that in your endeavors? Like a dev or
supervisor password or something. Normally I wouldn't work around a
program's protections - but I'm only talking updating text fields here - not
dates, prices, or anything else.

Thanks,
--Steph--

 

[Brad]

Steph,

Sorry for taking so long to reply to your post. I have posted quite a few
questions and I am not sure that I received an e-mail when you posted your
question.

It sounds like we may have similar issues in working with Business Works.

Currently I am building an interface to feed orders into Business Works. In
order to do this, I also have found it necessary to export Part and Customer
Info out of BW (in order to better edit the in-bound orders in the interface).

I am using a wonderful free scripting tool to do this. It automates the
manual steps for imports and exports. Let me know if you want more info on
this.

Long range, I plan to dig into ODBC access for BW. Currently we are waiting
on a patch from SAGE that needs to be applied before I can go any further
with ODBC. The automated scripting works, but it is not as elegant a
solution as ODBC access would be.

It might be beneficial to continue to compare notes on Business Works,
Access, ODBC, etc as it sounds like we may be facing similar challenges.

Thanks,
Brad

 

[send2steph]

:

It sounds like we may have similar issues in working with Business Works. Agreed.


Let me know if you want more info on <wonderful free scripting tool>. Sure!


Long range, I plan to dig into ODBC access for BW. Currently we are waiting
on a patch from SAGE that needs to be applied before I can go any further
with ODBC.

Are they giving you access to update the tables directly? That would be
great! Right now, I'm still using their forms for input, but I have several
tools built in Access that display information in a more user friendly way.
We also have quite a few fields of data that we track in Access tools and
link that to BW - and then use data from both sources to write reports (using
Access).

It might be beneficial to continue to compare notes on Business Works,
Access, ODBC, etc as it sounds like we may be facing similar challenges.

Yeap, sounds like a plan. I also say active on what's going on over at the
Sage BW forum. I'm send2steph over there as well
http://community.sagesoftware.com/sagevbuc/board?board.id=SageBusinessWorksForum

We're not the only ones trying to make all sorts of work-arounds. I wish
Sage would just start over and re-engineer this thing from the ground up
using AJAX.

 

[Brad]

Steph,

Several months ago I posted a question on one of the Access VBA forums
regarding a need we had to automatically check one segment of our website on
a scheduled basis. One of the responders suggested that I take a look at a
free Windows scripting utility tool called AutoHotKey (AHK). I downloaded
AHK and had it working in just a few minutes. I have used it for a number of
utility functions. It has many features and appears to be bug free. The
best thing is there is no charge (Open Source). AHK has a great web site
which has links to a forum that is also very good. I have posted several
questions and in each case received good advice quite quickly.

One of the ways in which I am currently using AHK is to automate Business
Works imports and exports. It is my understanding that there is no built-in
“batch†facility available for Business Works imports and exports. Instead
of asking our users to step through several Business Works screens for each
import/export, I wanted to provide a simpler solution that would involve
pushing a single button. I have been able to do this with AHK scripts.

Admittedly, this is not as elegant as using ODBC. Next week, a patch from
SAGE is going to be applied to our system to allow ODBC access to our
Business Works tables. It is my understanding, however, that this will only
be for “read only†access. If this is true, we will continue to use AHK
scripts for importing data into Business Works and gradually move towards
using ODBC for data extracts from Business Works.


Here is the link to AutoHotKey
http://www.autohotkey.com/

AutoHotKey can do many things that are difficult or impossible with VBA. We
have used it for several things besides the Business Works Import/Exports. I
have worked with computer software for many years and it takes a lot to
impress me. I am quite impressed with AHK.

Brad