Here is a sample VBA script you can use to work with tables in MS Dynamics GP and MS SQL.  The script shows a select statment and and insert statement for MS SQL. Make sure to add the Reference for the MS ActiveX Data Objects 2.8 Library to the project under Tools-Reference in Excel or the script will not compile.

The sample script locates the gl account index from the GL00100 gl account master file then combines that with payroll data to insert the record into the UPR40500 GL Payroll Posting Accounts table.

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
Let strSQL = “select * from GL00100 WHERE(ACTNUMBR_1 = ’000′) AND (ACTNUMBR_2 = ’2940′) AND (ACTNUMBR_3 = ’00′)”
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText

‘step through the record set and perform required actions on the records., in this case ‘there is only one record in the recordset
‘reference the fields using the column name fro the SQL table
Do While Not rs.EOF
‘retrieve the GL account index from the first row of the dataset
intID = rs!ACTINDX
‘show the account index number
MsgBox intID
‘move to the next record in the recordset
rs.MoveNext
Loop

‘close the open record set
rs.Close:   Set rs = Nothing
‘ declare the variables for next operation
Dim strDept As String
Dim strPosition As String
Dim strPayCd As String
Dim strMainAcct As String
Dim strPayType As Integer
Dim strAcctIndx As Integer

‘strAcctIndx holds the account index retrieved from the record set
‘set the department, position, pay code, pay type 1 = gross pay
strAcctIndx = intID
strDept = “ACCT”
strPosition = “ADA”
strPayCd = “HOUR”
strPayType = 1
‘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

‘close the connection to the database
cn.Close:   Set cn = Nothing
End Sub