Integration to Sage 2010

Apr 26
2010

Our products MASC , WRAP and Sage Stock Count are now integrated into Sage Line 50 2010. We now support all versions from Sage V9 to Sage V16 (2010). For more information visit our web page. http://www.e-ms.ie/masc.htm

Creating Excel files from MS Access

Apr 24
2010

This routine is used to create an inventory forecast, which displays a 52 week forecast based on Sales orders MRP forecast and scheduled PO’s.

Function OpenWritetoXLS_QCS(tmpFiletoOpen, tmpFirstWeek, tmpLastWeek)
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objWkb As Object
Dim objSht As Object
Dim rst As Recordset, tmpRange, tmpRangeCount, tmpGonePast, tmpPosition, tmpOffset, I, tmpColumn
tmpGonePast = False
    Set rst = CurrentDb.OpenRecordset("Select * from tblTmpXLFile order by Id") ' this is my access table that contains the records I want to insert into excel
    If rst.RecordCount > 0 Then
        rst.MoveFirst
    Else
        Set rst = Nothing
        MsgBox ("Nothing to export to excel")
        Exit Function
    End If
    tmpRange = ""
 
    If fIsAppRunning("Excel") Then
        Set objXL = GetObject(, "Excel.Application")
        boolXL = False
    Else
        Set objXL = CreateObject("Excel.Application")
        boolXL = True
    End If
 
    'now open file
  With objXL
 
    .Visible = True
    Set objWkb = .Workbooks.Open(tmpFiletoOpen)
    On Error Resume Next
    Set objSht = objWkb.Worksheets("SHEETNAME")
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = "SHEETNAME"
    End If
 
    objWkb.Worksheets("SHEETNAME").Activate
 
    objSht.Range("C1").Select
    objXL.ActiveCell.offset(0, 0) = tmpFirstWeek
 
    Err.Clear
 
    On Error GoTo 0
    tmpRangeCount = 1
    With objSht
        Do While Not rst.EOF
            tmpPosition = rst!Cellref ' this notes the line within the Excel model that I want to populate
            'reset to new position
            Select Case tmpPosition
                Case 6
                    .Range("B4").Select
                    tmpOffset = 3
                Case 8
                    .Range("B5").Select
                    tmpOffset = 4
                Case 9
                    .Range("B6").Select
                    tmpOffset = 5
                Case 20
                    .Range("B12").Select
                    tmpOffset = 11
                Case 30
                    .Range("B13").Select
                    tmpOffset = 12
                Case 35
                    .Range("B14").Select
                    tmpOffset = 13
            End Select
 
            'Find out what row we should go to
            tmpColumn = Val(rst!rptLabel)
            tmpColumn = tmpColumn - tmpFirstWeek + 1
 
            If Val(rst!rptLabel) = 0 Then
                objXL.ActiveCell.offset(0, 0) = rst!FIELDNAME
            Else
                objXL.ActiveCell.offset(0, tmpColumn) = rst!FIELDNAME
            End If
            rst.MoveNext
        Loop
    End With
 
  'update Parameters
    Set objSht = objWkb.Worksheets("Parameters")
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = "Parameters"
    End If
 
    objWkb.Worksheets("Parameters").Activate
 
    objSht.Range("A1").Select
    objXL.ActiveCell.offset(0, 0) = "Year"
    objXL.ActiveCell.offset(0, 1) = Forms![frmExport]![txtYear]
    objXL.ActiveCell.offset(1, 0) = "Overdue Week"
    objXL.ActiveCell.offset(1, 1) = Forms![frmExport]![txtOverDue]
    objXL.ActiveCell.offset(2, 0) = "Start of Month"
    objXL.ActiveCell.offset(2, 1) = Forms![frmExport]![txtStartofMonth]
 
    objXL.ActiveCell.offset(3, 0) = "First Week"
    objXL.ActiveCell.offset(3, 1) = Forms![frmExport]![txtFirstWeek]
 
    objXL.ActiveCell.offset(3, 0) = "Last Week"
    objXL.ActiveCell.offset(3, 1) = Forms![frmExport]![txtLastWeek]
 
  End With
 
  objWkb.Close savechanges:=True
 
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rst = Nothing
 
End Function

Access Reports Search and Replace Headers

Apr 23
2010

I had the opportunity to use Rickworlds search and replace utility again today, this product pays for itself immediately , or course if I was designing the app now I would drive the report heading from a table.

Syspro Server Move

Apr 16
2010

We move server for Syspro version 6 recently and had to update 40 users ODBC connections. The easiest way to complete this was to fix the odbc setting on one computer , then export the registry for the ODBC setting. We moved that exported reg file to our server and sent all Syspro users a link to the registry file which updated the PC’s.

This is a sample of the file contents – you would need to change “YOURSERVER”

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Syspro6]
“Driver”=”C:\\WINDOWS\\system32\\tsodbc32.dll”
“Description”=”Live”
“Server”=”YOURSERVER”
“Port”=”7000″
“Timeout”=”100″

We also had an issue with DCOM on the server, when enabling business object. The server was MS 2003 and the only way to get the system to operate was to make to posting user an administrator. This was documented in a tech PDF from syspro as noted below. If you know of any other solution please leave me a comment – thanks

Remote calls made by certain users fail
Cause
Changes introduced in Service Pack 1 (SP1) of Windows 2003 Server has resulted in the
failure of remote calls made by users who are not members of either the Administrators
or Distributed COM Users groups on the server.
Remedy
You need to configure the account permissions for remote access to the server (review
the procedure: Configuring account permissions for remote server access).
Alternatively you can add the user that is being used to run the application, to the DCOM
User group on the server. This group should have all of the required permissions
Configuring account permissions for remote server access
The following steps describe how to configure remote access permissions for users who
are not members of the Administrator or DCOM user groups on the server.
1. Launch the Component Services utility (Control Panel > Administrative Tools >
Component Services).
2. Open the My Computer Properties window.
a. Select the Component Services node.
b. Expand the Computers node.
c. Right-click My Computer.
d. Select Properties from the shortcut menu.
3. Configure the required access permissions.
a. Select the Security tab.
b. Select Edit Limits at the Access Permissions field.
c. Add the account that is being used to run the application via DCOM.
d. Enable the Allow option against the Remote Access option.
e. Select OK.
4. Configure the required launch and activation permissions.
a. Select Edit Limits at the Launch and Activation Permissions field.
b. Add the account that is being used to run the application via DCOM.
c. Enable the Allow option against the Remote Launch and Remote Activation
options.
d. Select OK.
5. Apply your selections.
6. Exit the utility.

Remote Desktop on XP

Apr 14
2010

I spent too much time trying to get Remote Desktop installed onto a PC when it was already there but , for some unknown reason, the icon was missing.

Eventually I found this link and it allowed me to create a shortcut and restore the setting

If you need some help on a project drop leave a comment on the post and I will reply.