473,320 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Running an Oracle procedure through Access

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
1 5163
MMcCarthy
14,534 Expert Mod 8TB
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

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

Similar topics

13
by: kristoff plasun | last post by:
I have a problem with a C++ DCOM application that prints Crystal Reports with data from Oracle. The SQL query is relatively complex but when the report is printed from the Crystal Reports...
3
by: none | last post by:
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK, which has one or more PURCHASES, which have one or more PURCHASE_ITEMS. When a purchase item is INSERTED, I'd like the...
1
by: Jason Leiser | last post by:
Is there a way to call an Oracle Procedure using the MS OLD DB Provider for Oracle object in a SQL Server 2000 DTS package? If it can't be done this way, is there another way to retrieve data from...
0
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The...
2
by: André Nobre | last post by:
I don't know if this is the right place to make this question, so, if isn't, let me know... I have an oracle package with some procedures, and i need to access one procedure using vb.net. The...
1
by: Chad | last post by:
Hi, I am a SQL Server programmer using Oracle for the first time. In our .NET client apps which use a SQL Server back end, we would use Stored Procedure exclusively for all database access for...
0
by: riyap | last post by:
i understood the oracle procedure but only question is after executing the access db query, how do i assign those values(each field values) to the parameters iam passing into oracle procedure i.e...
3
by: John | last post by:
Hi. I have a number of batch jobs that are ran nightly on our Windows 2000 based Oracle 8.1.7 (soon to be 9i) server. I have these designed just right, so the Windows Scheduled Tasks runs them...
23
by: Gloops | last post by:
Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.