Upload Sage 50 data to SQL Server – Sync with ODBC

Follow my video to allow you to sync up your Sage 50 database without having to pay for any connectors or get an SQL linked server working etc.

This uses Access linked tables and ODBC to create a copy of the sage database on SQL so you can query it for reports.

I created this as I was looking for ways to pull data and report on it from the Sage 50 database with no luck, everywhere I googled it mentioned linked servers but I could not get this to work at all.  This way works great just takes a little bit of setting up.

Files to accompany YouTube video below! – let me know how you get on!

Access VB Module:

Option Compare Database

Public Sub uploadSql1()

Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef

sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=sagesql;UID=sa;PWD=password"
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."

Set db = CurrentDb()

For Each tbldef In db.TableDefs
Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
Next tbldef

SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
DoCmd.Quit

Exit Sub

DoCmd.Quit

End Sub

Public Function runUpload()

Call uploadSql1

End Function

Open Access and manually run the Macro for the first time and make sure Macros are enabled on the PC running this.

droptables.sql:

DROP TABLE [dbo].[MSysAccessStorage]

DROP Table MSysACEs

Drop table MSysComplexColumns

Drop table MSysNameMap

Drop table MSysNavPaneGroupCategories

Drop table MSysNavPaneGroups

Drop table MSysNavPaneGroupToObjects

Drop Table MSysNavPaneObjectIDs

Drop Table MSysObjects

Drop Table MSysQueries

Drop Table MSysRelationships

Drop Table MSysResources

Drop table dbo.AUDIT_HEADER

Drop table dbo.CURRENCY

Drop table dbo.SALES_LEDGER

Drop table dbo.GRN_ITEM

Drop table dbo.INVOICE

Drop table dbo.INVOICE_ITEM

Drop table dbo.NOMINAL_LEDGER

Drop table dbo.PURCHASE_ORDER

Drop table dbo.POP_ITEM

Drop table dbo.STOCK

Drop table dbo.STOCK_ALLOCATION

Drop table dbo."Name AutoCorrect Save Failures"

Drop table dbo.PURCHASE_LEDGER

runsqldrop.bat:

sqlcmd -S computername\instance -d Sage -U sa -P password -I -i c:\SAGE\dropandcreate.sql

runmacro.bat:

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\SAGE\linktolocal.accdb" /x Upload

ExportSageToSQL.bat:

call c:\SAGE\runsqldrop.bat

call c:\SAGE\runmacro.bat

exit

11 thoughts on “Upload Sage 50 data to SQL Server – Sync with ODBC

  1. Hi Andy

    Even as something of a novice I’ve been able to get this working following your tutorial, so good job!

    My next challenge is that I have over 40 Sage companies to deal with, so I was trying to build a refresh all function. My first attempt involved creating a new batch file that executes the ExportSageToSQL.bat files for the first two companies I have linked, however only the first one refreshes. I’m guessing this is because the second ExportSageToSQL.bat file attempts to execute when the first one is still operating Access?

    Any thoughts on how to make it execute the batch files in sequence?

    Thanks
    Chris

    1. Hi Chris,

      Glad it helped you out!

      You could create an export to save batch for each company, then make another one as refreshall.bat that has call c:\sage\exportcompany1.bat etc so it runs them in order one at a time,

      Does that help?

      Cheers,

      Andy

  2. It’s actualⅼy a nice and useful piece of info. I am glad that you just shared this
    useful information with us. Pⅼease stay us informed like this.
    Thаnk yoᥙ for sharing.

  3. Good information… what are the limitations with the Sage 50 ODBC driver – its not bi-directional is it, does it automatically fetch new stuff in the Sage db?

    1. Yes it will only allow for retrieval of data from Sage, it fetches on a schedule – you can make this as frequent or infrequent you want, cheers

  4. Good day,
    Please advise if you have a script that can assist us upload Sage Payroll and HR data to SQL Server? The Sage Payroll and HR runs on a pervasive database. Thank you very much in advance

  5. Hi Andy,

    Thank you for sharing, something that would be useful for me.
    However, what if I don’t see the Sage line 50 vXX driver in ODBC32? I can’t find how to.
    If you can help it would be more than appreciated!
    Thanks

  6. Really grateful I stumble on your page. Saved me a lot of hours figuring out how to import Sage data to Power BI. Many thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *