Erp Forum: The Sage Forum Site

[ben_wood]

How can you use a macro to extract data out of MMS version 2.2?

[freda]

Yes anyone can help That would be cool

Thanks

[edbeeny]

A good place to start would be

http://support.microsoft.com/kb/257819

[edbeeny]

Try this

Sage Line 500 to extract distinct product and description out of stockm table
Go into Excel F11 and paste this code into the Module
Change the required to fit your network


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Sub get_data()

'Change Server to the server Ip Address
'Change uid to the user Name
'Change pwd to the Password


cn.Open "Driver={SQL Server};" & "Server=127.0.0.1;" & "Database=demo;" & "Uid=User_Name;" & "Pwd=Password;"

sql = "Select distinct product, long_description " _
& "from scheme.stockm"

rs.Open sql, cn

Do Until rs.EOF
Range("A1").CopyFromRecordset rs
Loop

Range("A1").Select

rs.Close
cn.Close

End Sub

[edbeeny]

Sorry I forgot to add you must add Microsoft Activex Data object 2.5 Library

In VBA editor go Tools, References and check the Data object

[ben21]

Nice bit of code..

Have you got an example to insert into SQL?

I take it you could develop these simular to Codis's Products..
but make them open source!!

[fred_ward]

Could this be adapted for Sage 200?

[MikeCTerry]

This was tested with v5

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Sub get_data()

'Change Server to the server Ip Address
'Change uid to the user Name
'Change pwd to the Password


cn.Open "Driver={SQL Server};" & "Server=127.0.0.1;" & "Database=demo;" & "Uid=User_Name;" & "Pwd=Password;"

Sql = "Select Code, Name from StockItem"

rs.Open Sql, cn

Do Until rs.EOF
Range("A1").CopyFromRecordset rs
Loop

Range("A1").Select

rs.Close
cn.Close

End Sub

[or-8]

Assuming that MMS v2.2 runs on a SQL server.....

Why would you want to use a macro to extract the data?
Can you not just link to the relevant tables using ODBC and extract it that way?

Gordon
OR-8

[freda]

I find problems with ODBC that for complex reports it locks the tables.

With VBA you can be much clever and use dirty reads, and use recordsets within excel to place the data where ever you want to on the Excel sheet.

I would always use VBA over ODBC.