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

Leave a Reply

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