Sampe VBA Excel Macro with loop to update MS Dynamics GP table.

Here is a full example of the VBA sample code with a loop.  This VBA code loops through an Excel spreadsheet and inserts records in the MS Dynamics GP Payroll Posting Accounts table UPR40500. In this case we only insert accounts for Gross Pay.

The VBA sample code also create a record set named (rs) from the GL00100 table that will provide the GL account index for the full account.  The full GL account is matched to the record in the GL00100 table to get the account index.

Comment lines begin with an apostrophe (‘).

Public Sub GetAccounts()

‘Define ADO connection object and recordset object
Dim cn As ADODB.Connection, rs As ADODB.Recordset

‘set cn (connection) to hold database connection string
‘set rs (recordset) to hold the results for the retrieved record set ‘from SQL
Set cn = New ADODB.Connection
cn.Open “Provider=’SQLOLEDB’;Data Source=’dh-PC’;Initial Catalog=’TWO’;” & _
“Integrated Security=’SSPI’”
Set rs = New ADODB.Recordset

‘strSQL will hold the select SQL statemment to run
‘rs.open opens the record set with strSQL, and connection cn
Dim strSQL As String

‘ declare other variables
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer
Dim strFullAcct As String
Dim rsFullAcct As String

‘Read cells from payroll accounts sheet starting in row 2
Dim i As Integer
i = 2
strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Let strSQL = “select * from GL00100″
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
‘Use a MsgBox to display a messge and field contents for debuging, ‘remove the apostrophe in from the MsgBox statement below to see ‘how it works.

‘MsgBox strSQL

‘Process all the rows in the spreadsheet
Do Until IsEmpty(Cells(i, 1))

‘step through the record set and perform required actions on the records
‘reference the fields using the column name from the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
strFullAcct = strDept & strMainAcct & strPosition
rsFullAcct = Left(rs!ACTNUMBR_1, 3) & Left(rs!ACTNUMBR_2, 4) & Left(rs!ACTNUMBR_3, 2)
If strFullAcct = rsFullAcct Then intID = rs!ACTINDX
‘move to the next record in the recordset
rs.MoveNext
Loop

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID

‘adjust department and position code strings to proper length
strDept = Right(strDept, 2)
strPosition = Right(strPosition, 2)

‘create the sql string to insert the record into the Payroll posting accounts table
strSQL = “INSERT INTO UPR40500 (DEPRTMNT,JOBTITLE,PAYROLCD,UPRACTYP,ACTINDX) VALUES”
strSQL = strSQL & “(” & strDept & “,” & strPosition & “,’” & strPayCd & “‘,” & strPayType & “,” & strAcctIndx & “)”
‘MsgBox strSQL
cn.Execute strSQL

i = i + 1
strDept = “”
strPosition = “”
strPayCd = “”
strMainAcct = “”
strPayType = 0
strAcctIndx = 0
strFullAcct = “”
rsFullAcct = “”

strDept = ActiveWorkbook.Sheets(“PRA”).Cells(i, 1).Value
strPosition = ActiveWorkbook.Sheets(“PRA”).Cells(i, 2).Value
strPayCd = ActiveWorkbook.Sheets(“PRA”).Cells(i, 3).Value
strMainAcct = ActiveWorkbook.Sheets(“PRA”).Cells(i, 4).Value
strPayType = ActiveWorkbook.Sheets(“PRA”).Cells(i, 5).Value

Loop

MsgBox “Import Completed”

‘close the open record set
rs.Close:   Set rs = Nothing
‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub

MS Dynamics GP SOP sales order packing list prints wrong ship to address information.

Instead of spending hours trying to solve this problem here is what you need to do.

Run the following script to delete the order from the SOP work files.

delete SOP10100 where SOPNUMBE = ’44735′
delete SOP10101 where SOPNUMBE = ’44735′
delete SOP10102 where SOPNUMBE = ’44735′
delete SOP10103 where SOPNUMBE = ’44735′
delete SOP10104 where SOPNUMBE = ’44735′
delete SOP10105 where SOPNUMBE = ’44735′
delete SOP10106 where SOPNUMBE = ’44735′
delete SOP10107 where SOPNUMBE = ’44735′

Run Check Links on the Sales Work File.  Click on the MS Dynamics GP button, then Maintenance – Check Links. Click the drop down for Series Sales, then insert the Sales Work file and OK.

Re-enter the sales order.

MS Dynamics GP sql script to update the Direct Deposit master.

Employees may be inactive in the employee master table but show active records in the direct deposit tables. Use this script to identify employees that are inactive in the employee master table UPR00100, and that may have active direct deposit records in the direct deposit master DD00100 table, and the direct deposit account master DD00200 table.

SELECT dbo.DD00100.EMPLOYID, dbo.DD00100.INACTIVE, dbo.DD00200.EMPLOYID, dbo.DD00200.INACTIVE, dbo.UPR00100.EMPLOYID, dbo.UPR00100.INACTIVE FROM
dbo.DD00200 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00200.EMPLOYID
inner join dbo.DD00100 on dbo.DD00100.EMPLOYID =
dbo.UPR00100.EMPLOYID

Script to set Inactive the records in the DD00100 and DD00200 that should be inactive.

update dbo.DD00100 set dbo.DD00100.INACTIVE = ’1′ where dbo.DD00100.EMPLOYID in (SELECT dbo.DD00100.EMPLOYID FROM dbo.DD00100 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00100.EMPLOYID
where dbo.UPR00100.INACTIVE = ’1′)

update dbo.DD00200 set dbo.DD00200.INACTIVE = ’1′ where dbo.DD00200.EMPLOYID in (SELECT dbo.DD00200.EMPLOYID FROM dbo.DD00200 inner join dbo.UPR00100 on dbo.UPR00100.EMPLOYID = dbo.DD00200.EMPLOYID
where dbo.UPR00100.INACTIVE = ’1′)

In the Select subquery after the Update only one column reference is allowed.

 

 

MS Dyamics GP Safe Pay – positive pay banking

MS Dynamics GP Safe Pay module provides the capability you need to transmit a positive pay file to your bank.  What is Safe Pay or Positive Pay?

Positive Pay is a fraud detection program provided by your bank.  Your accounting software will provide a file to the bank that includes check number, account number, issue date, and amount.  This list is compared electronically to the check when it is presented to the bank for payment.  These fields must match exactly or the check will not be paid and and an exception notification will be sent to you and you would instruct the bank to either pay or return the check.

In MS Dynamics GP accounts payable checks, payroll checks, and EFT transactions can be included in Safe Pay transactions that are uploaded to the bank.  Payroll Direct Deposit transactions are not included in Safe Pay transactions since payments submitted via ACH files are not subject to the check clearing mechanisms at the bank.