✔ Programação GLOBAL®: MS Access

MS Access – Exec Proc in SYBASE

O Código abaixo deve ser colado em um novo módulo do MS Access. A partir deste a função ReturnData() pode ser executada.

É verdade que uma query também precisará ser criada para a execução do código abaixo. Esta é apenas uma técnica de como obter resultados de uma Stored Procedure no Sybase que retorna dados.

'============================================================================================== ' Microsoft® Office Access developing by A&A - In Any Place. ' Copyright© A&A - In Any Place. All Rights Reserved. '==============================================================================================

Public Colmn0 As Variant Public Colmn1 As Variant Public Colmn2 As Variant Public Colmn3 As Variant Public Colmn4 As Variant Public Colmn5 As Variant Public Colmn6 As Variant Public Colmn7 As Variant Public Colmn8 As Variant Public Colmn9 As Variant Public Colmn10 As Variant Public Colmn11 As Variant

Option Compare Database

Function ReturnData() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess@gmail.com

' Retorna o conteúdo de PROC do Sybase.

Dim db As Database Dim l As Long Dim Ss As Recordset Dim StrConection As String Dim i As Integer Dim flag As Boolean Dim nTx1, nTx2, nTx3, nTx4, nTx5, nTx6 As String

Let nTx1 = [Form_frm_sys_SybaseImport].CxStoredProcedure.Value Let nTx2 = [Form_frm_sys_SybaseImport].CxDSN.Value Let nTx3 = [Form_frm_sys_SybaseImport].CxServer.Value Let nTx4 = [Form_frm_sys_SybaseImport].CxDatabase.Value Let nTx5 = [Form_frm_sys_SybaseImport].CxUser_ID.Value Let nTx6 = [Form_frm_sys_SybaseImport].CxPassword.Value

' String de conexão. Let StrConection = "DSN=" & nTx2 & ";" & _ "SRVR=" & nTx3 & ";" & _ "DB=" & nTx4 & ";" & _ "UID=" & nTx5 & ";" & _ "PWD=" & nTx6

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, StrConection) Set rs = db.OpenRecordset("EXEC " & nTx1, dbOpenSnapshot, dbSQLPassThrough)

Let flag = True Do Until rs.EOF ' Nome dos campos. If flag Then Let j = 1

While j <= 11 'Debug.Print rs(j).Name Let j = j + 1 Wend Let flag = False End If

'a = SysCmd(acSysCmdSetStatus, Now() & " |" & rs(0).Value & rs(1).Value & rs(2).Value & rs(3).Value & rs(4).Value)

' & rs(5).Value & rs(6).Value & rs(7).Value & rs(8).Value & rs(9).Value & rs(10).Value & rs(11).Value)

'Debug.Print i, rs(0).Value, rs(1).Value, rs(2).Value, rs(3).Value, rs(4).Value, rs(5).Value, rs(6).Value, rs(7).Value, rs(8).Value, rs(9).Value, rs(10).Value, rs(11).Value

[Form_frm_sys_SybaseImport].Repaint [Form_frm_sys_SybaseImport].Refresh

Let Colmn0 = Trim(rs(0).Value) Let Colmn1 = Trim(rs(1).Value) Let Colmn2 = Trim(rs(2).Value) Let Colmn3 = Trim(rs(3).Value) Let Colmn4 = Trim(rs(4).Value) Let Colmn5 = Trim(rs(5).Value) Let Colmn6 = Trim(rs(6).Value) Let Colmn7 = Trim(rs(7).Value) Let Colmn8 = Trim(rs(8).Value) Let Colmn9 = Trim(rs(9).Value) Let Colmn10 = Trim(rs(10).Value) Let Colmn11 = Trim(rs(11).Value)

' Atualiza a tabela de Recursos Humanos. DoCmd.SetWarnings (False) DoCmd.OpenQuery "qry_sys_SetPeople", acViewNormal, acAdd DoCmd.SetWarnings (True)

rs.MoveNext

Let i = i + 1 Loop

rs.Close

Set db = Nothing Let Colmn0 = Null Let Colmn1 = Null Let Colmn2 = Null Let Colmn3 = Null Let Colmn4 = Null Let Colmn5 = Null Let Colmn6 = Null Let Colmn7 = Null Let Colmn8 = Null Let Colmn9 = Null Let Colmn10 = Null

MsgBox "Atualização terminada", vbInformation, "Colaboradores importados" End Function

Function ReturnField0() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField0 = Colmn0 End Function

Function ReturnField1() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField1 = Colmn1 End Function

Function ReturnField2() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField2 = Colmn2 End Function

Function ReturnField3() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField3 = Colmn3 End Function

Function ReturnField4() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField4 = Colmn4 End Function

Function ReturnField5() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField5 = Colmn5 End Function

Function ReturnField6() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField6 = Colmn6 End Function

Function ReturnField7() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField7 = Colmn7 End Function

Function ReturnField8() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField8 = Colmn8 End Function

Function ReturnField9() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField9 = Colmn9 End Function

Function ReturnField10() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField10 = Colmn10 End Function

Function ReturnField11() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField11 = Colmn11 End Function

Query SQL Code:

INSERT INTO tbl_data_SetPeople ( Registro, CPF, Nome, funcao, nivel_profis, Disciplina, data_admissao, data_demissao, lotacao, razao_soci, local_trabalho )

SELECT ReturnField1() AS RE, ReturnField2() AS nCFP, ReturnField3() AS nNome, ReturnField4() AS nFuncao, ReturnField5() AS nNivelProfissional, ReturnField6() AS nDisciplina, ReturnField7() AS nDtAdmissao, ReturnField8() AS nDtDemissao, ReturnField9() AS nLotacao, ReturnField10() AS nRazaoSocial, ReturnField11() AS nLocalTrabalho