Question MS Access 2010 and Sage Accounting

[humph]

Has anyone had any experience with moving data from Access into Sage - I am not even sure if it is possible.

High level overview of what I am trying to do is I have a package which prepares customer invoices which is one segment of a customers business. The customer uses sage and would like if possible to import the issued invoice to sage. The other option is to export the raw data into sage and use sage to issue the invoices.

Simply seeking clarification at his stage to understand if it is possible.

Thanks

Humph

 

[Lightwave]

Haven't done it but it will be possible.

Firstly you need to talk to your client and find out who is in charge of their sage. Someone on their side will probably be doing the import and might very probably know exactly how to do it.

From a short bit of research looks like you will export your Access to csv format and then either go through a UI import routine or possibly run a built in function. Exactly how you do it will depend on what kind of ledger you are importing into. Sales Ledger records for instance will almost certainly want a specific FKID primary key relating to the account names of the Debtors which the company should be able to give you this may mean you have to do a bit of matching between the names of debtors and the accounts to create an amalgamated csv ready for import.

See here

See this line

You might have to do the matching process to their accounts in conjunction with the person in the company in charge of SAGE - he / she can give you the list of accounts for instance.

 

[CJ_London]

As Lightwave says, You can only import specific types of records and transactions via a .csv file - at least that applies to the versions of Sage I know.

In Sage, go to Help and find Import>Data which provides a complete guide to the structure of the .csv files required and guidance for loading to Sage.

You can use ODBC to create a linked table to Sage but the tables are not updateable this way - but useful for Access to interogate Sage for customerID, productID, prices etc.

One thing to be aware of re invoices is you cannot import the invoice in detail, only as a single line - the equivalent of using the Sage functionality of Customers>Invoice rather than the invoicing routine provided by Sage so the client will only be able to review a full invoice within your application - but it is fine for credit control.

I have done this a few times where the Sage invoicing routine does not meet the needs of the client- typically for something where the invoice needs to specify dimensions and calculate a rate based on area or volume.

If you are developing off site from the client and do not have Sage yourself, I would go into the client office with a db, make the odbc connection, link the tables required then export the data as a .csv file and relink to the .csv files for working off site. Once the application is complete and for the install, relink to Sage and overwrite the table names.

So far as I am aware Sage does not provide any automation to upload data, so if your client has a new customer this will need to be entered or loaded to Sage before loading the invoice.

The import routine is trouble free providing you follow the rules - all invoices will be rejected if one customer does not exist within Sage for example, length of text fields, presentation of numbers and dates also need to be as specified.

 

[Simon_MT]

You do not need to to insert records into individual Sage Debtors records. I post into currency accounts within debtors and do individual Debtor balances with Access. The Rational is that have the Invoices with Access why not process the cash?

Simon