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].lblStatus.Caption = "|" & Trim(Str(i)) & "| " & Now() & " |" & rs(0).Value & " |" & rs(1).Value & " |" & rs(2).Value & " |" & Trim(rs(3).Value) & " |" & rs(4).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
FROM tbl_sys_Add
WITH OWNERACCESS OPTION;
ANDRÉ BERNARDES
Santos - SP - Brasil
MSN:
bernardess@gmail.com
SKYPE: inanyplace
TWITTER: bernardess
My Profile:
http://al-bernardes.sites.uol.com.br/
In LinkedIn:
http://www.linkedin.com/in/andrebernardes
In Plaxo:
http://andrebernardes.myplaxo.com/
In Jobster:
http://www.jobster.com/people/andrebernardes
My experiences:
http://andrelbernardes.sites.uol.com.br/MIS.html
http://andrelbernardes.sites.uol.com.br/XLS.html
Acesse meus Feeds:
http://www.google.com/reader/shared/17429357110218427555
Acesse o portfólio:
http://www.slideshare.net/bernardes/informaes-ao-invs-de-dados-presentation
