✔ Programação GLOBAL®: MS Access

MS Access – Exec Proc in ORACLE

| Blog Office VBA | Blog Excel | Blog Access |

A questão é a seguinte: Ao utilizarmos qualquer versão do MS Access (2007/2003/XP/2000/97) como interface de aplicação [front-end], acessando certas funcionalidades no banco de dados Oracle [back-end], como executo ou efetuo chamadas a uma stored procedure no Oracle (RDBMS) a partir do MS Access?

Considere: application_program_interface (member_id, provider_id, service_date)

Procedimento: Para chamar a stored procedure no Oracle, é necessário criar algum código VBA.

Antes do código VBA, precisamos de uma conexão ODBC no Windows para o seu banco de dados Oracle, usando o driver {Microsoft ODBC for Oracle}.

Para criar a conexão no Windows vá até o Painel de Controle | Data Sources ODBC e crie um novo Data Source usando o driver {Microsoft ODBC for Oracle}.

Configure a sua conexão ODBC:

Neste exemplo configuraremos o Data Source com o nome de AAAA, o usuário com o nome de BBBB, e um servidor Oracle chamado CCCC. Você precisará configurar a sua conexão ODBC com as suas próprias informações para estes parâmetros.

No exemplo abaixo chame sua stored procedure do Oracle através da application_program_interface em VBA.

No MS Access crie um novo Módulo e cole o código abaixo:

Function CallSProc() As Boolean

Dim db As Database Dim LSProc As QueryDef

On Error GoTo Err_Execute

Set db = CurrentDb()

Set LSProc = db.CreateQueryDef("")

' Utilize {Microsoft ODBC for Oracle} para a conexão ODBC LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC" LSProc.SQL = "BEGIN application_program_interface; END;" LSProc.ReturnsRecords = False LSProc.ODBCTimeout = 0

LSProc.Execute

Set LSProc = Nothing

CallSProc = True

Exit Function

Err_Execute: MsgBox "A execução da Stored Procedure no Oracle falhou." CallSProc = False

End Function

Ei! Note que você precisará customizar o seu código adequando a linha abaixo:

LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

Isto é:

AAAA é o nome ODBC do "Data Source" que você configurou.

BBBB é o nome do usuário que se logará no Oracle.

CCCC é o nome do seu servidor Oracle.

DDDD é a senha que utilizará para logar-se no Oracle.

Pode acontecer que ao tentar executar a stored procedure, receba uma mensagem similar a "not defined" error on the "Dim db as Database" declaration, neste caso precisará seguir os passos abaixo:

Na janela VBE do MS Access escolha a opção de Referências.

Quando a janela aparecer, role para baixo até achar a opção "Microsoft DAO 3.6 Object Library", clicando a caixa e depois o botão OK.

Agora sua aplicação MS Access reconhecerá o objeto database e a mensagem de erro desaparecerá.

Dica importante: Ao passo que é possível executar Stored Procedures do Oracle através do MS Access, não é possível recuperar informações históricas de status vindas do Oracle. Recomendo que crie uma tabela para auditoria posteriormente. Grave nela registros com informações das ações executadas através da sua interface MS Access, propiciando posteriores auditorias em seus procedimentos. Através desta solução, poderá consultar a tabela do MS Access e visualizar o status da sua stored procedure.

Stored procedure with parameters

O segundo exemplo executa uma stored procedure chamada application_program_interface com parâmetros, considere:

application_program_interface (member_id, provider_id, service_date)

Onde

member_id é um valor numérico

provider_id é um valor numérico

service_date é um valor de data

No MS Access crie um novo Módulo e cole o código abaixo:

Function CallSProc() As Boolean

Dim db As Database Dim LSProc As QueryDef Dim LSQL As String

On Error GoTo Err_Execute

Set db = CurrentDb()

Set LSProc = db.CreateQueryDef("")

' Código SQL para executar a stored procedure (com parâmetros) LSQL = "BEGIN application_program_interface (" & member_id & ", " LSQL = LSQL & provider_id & ", " LSQL = LSQL & "to_date('" & Format(service_date, "mm/dd/yyyy") & "','mm/dd/yyyy'))" LSQL = LSQL & "; END;"

' Utilize {Microsoft ODBC for Oracle} para a conexão ODBC LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC" LSProc.SQL = LSQL LSProc.ReturnsRecords = False LSProc.ODBCTimeout = 0

LSProc.Execute

Set LSProc = Nothing

CallSProc = True

Exit Function

Err_Execute: MsgBox "A execução da Stored Procedure no Oracle falhou." CallSProc = False

End Function

Ei! Não se esqueça de customizar o seu código na seguinte linha:

LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

Isto é:

AAAA é o nome ODBC do "Data Source" que você configurou. BBBB é o nome do usuário que se logará no Oracle. CCCC é o nome do seu servidor Oracle. DDDD é a senha que utilizará para logar-se no Oracle.