By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

Running an Oracle procedure through Access

P: 1
Is this correct? We are trying to run a stored procedure with parameters and have a File DSN setup but it shows an error on the Execute statement.

Dim db As DAO.Database
Dim LSProc As DAO.QueryDef
Set db = CurrentDb()
Set LSProc = db.CreateQueryDef("")
'SQL to call stored procedure (with parameters)
'Use {Microsoft ODBC for Oracle} ODBC connection
LSProc.Connect = "ODBC;DSN= RAADBO_DEV1;UID=test;PWD=test1;SERVER=fmebs_dev1"
LSProc.SQL = "exec mfp_alloc_p1('2006')"
LSProc.ReturnsRecords = False
LSProc.ODBCTimeout = 0
LSProc.Execute
Set LSProc = Nothing
Jan 30 '08 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
You need to set up a connection and I would suggest using ADO for a connection to a non-Access database. Something like the following ...

Expand|Select|Wrap|Line Numbers
  1. Dim cnn As ADODB.Connection
  2. Dim db_name As String
  3. Dim UserName As String
  4. Dim Password As String
  5.  
  6.     Set cnn = New ADODB.Connection
  7.  
  8.     db_name = "KRIJGS"
  9.     UserName = "bertuser"
  10.     Password = "abcxyz123"
  11.  
  12.     'Making an ODBC connection according to ADO
  13.     cnn.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
  14.     & Password + ";"
  15.  
  16.     cnn.SQL = "exec mfp_alloc_p1('2006')"
  17.     cnn.Execute
  18.  
  19.     Set cnn = Nothing
  20.  
I don't use stored procedures much so someone may wish to play around with the syntax on this.
Feb 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.