Running a PL/SQL in Access

[Joe]

How do I run a PL/SQL program in MS ACCESS?

 

[Van T. Dinh]

Check Access Help on Pass-Through Queries.

 

[Joe]

I don't think I am being clear. I only have an Access
database, not a MS SQL,so I can't use the pass-through.
The query I have is really a stored proc designed for MS
SQL, I want to run it in Access. How can I do this?

 

[Van T. Dinh]

Huh?

Let's clarify:

1. Do you use an Access MDB file or an Access Project (ADP) file?

2. You mentioned the "PL/SQL program" and I guess you meant a PL/SQL stored
procedure. Where does this SP reside?

3. Why do you want to use an SP designed for MS-SQL (Server?) but you are
not using MS-SQL Server as the BE?

 

[Douglas J. Steele]

Van: I believe PL/SQL is Oracle's version of T/SQL in SQL Server.

Joe: Pass-through queries aren't limited to SQL Server. Just set the
connection string appropriately to connect to Oracle.

 

[Van T. Dinh]

Thanks, Doug.

I knew PL/SQL is a database server SQL but couldn't remember which one.

Joe's last sentence is rather confusing so I wanted to clarify ...

 

[Guest]

Yes, PL/SQL is for Oracle, Transact SQL is for SQL Server. You can make a
passthrough table in Access off of an Oracle table. You can also import the way
you would with ADO, only you use OO4O with Oracle. But thats for pure code. You
can link and Oracle table with push button methods.
(e-mail address removed)

 

[Albert D. Kallal]

Well, it is also possible that he has some pl/sql that he wants to run in
ms-access.

In re-reading the original question, it not at all clear that he is trying
to run a stored procedure in Oracle, but simply asking how the same thing is
done in ms-access!

In other words, perhaps the OP is not trying to connect to oracle, or sql
server or anything else.

This simply may be a case that he has some sql code that needs to be run in
ms-access.

So, the question really reads:

I have some pl/sql code. How can one run pl/sql code in a ms-access JET file
share database? (or, how does one run or write sql code procedures in
ms-access?)

My answer:

Simply post the sql you have. You can place the sql into a code module, and
I sure the code can easily be converted to run with ms-access VBA.

 

[Joe]

Sorry for the confusion, let me re-explain my situation.
I have an Access Database (.mda) that was an export of MS
Project. There is a table that MS project created that is
not usable unless you run a SP that MS created on there
knowledge base for download. It is designed to run
against a MS SQL database, but I have a access database.
So I want to run this SP against my Access database. The
SP right now is just a text file, and does not reside in
any database. Also, I was not able to copy it in an
access module without getting many errors.

 

[Douglas J. Steele]

As Albert suggested, post the details of what the SP contains, and someone
should be able to help you translate it into something Access can use.

 

[Joe]

Here is the script I am trying to run in MS ACCESS. This
was provided by MS knowledge base and is designed for MS
SQL database.

--
-- Time-phased data script for assignment contours
--
-- To use this script, perform the following edits and
execute using Microsoft Query
-- Analyzer, ISQL, OSQL or another query tool:
-- 1. Set @field_id to the assignment timephased data
field (see MSP_CONVERSIONS table
-- where STRING_TYPE_ID = 107).
-- 2. Set @proj_id to the ID of the project (see
MSP_PROJECTS table).
-- 3. Set @ref_uid to the value of ASSN_UID that you want
to query.
-- Do NOT change the value of @period.

declare @ref_uid as int, @field_id as int, @proj_id as
int, @period as int
select @field_id = 5
select @proj_id = 5
select @ref_uid = 13
select @period = 0 -- Do not edit!

create table #td_data (
Assn_UID int,
Task_Name varchar(255),
Res_Name varchar(255),
Contour_Type_ID int,
Period_Start datetime,
Period_Unit varchar(255),
Value float )

while (@period < 7)
begin
insert into #td_data
select td.td_ref_uid,
t.task_name,
r.res_name,
td.td_field_id,
case td.td_units
when 0 then dateadd(minute,
@period, td.td_start)
when 1 then dateadd(hour, @period,
td.td_start)
when 2 then dateadd(day, @period,
td.td_start)
when 3 then dateadd(week, @period,
td.td_start)
when 5 then dateadd(month,
@period, td.td_start)
when 8 then dateadd(year, @period,
td.td_start)
end,
td.td_units,
case @period
when 0 then td.td_value1
when 1 then td.td_value2
when 2 then td.td_value3
when 3 then td.td_value4
when 4 then td.td_value5
when 5 then td.td_value6
when 6 then td.td_value7
end
from msp_timephased_data td,
msp_assignments a,
msp_tasks t,
msp_resources r
where td.td_category = 3
and td.proj_id = @proj_id
and td.proj_id = a.proj_id
and td.proj_id = t.proj_id
and td.proj_id = r.proj_id
and td.td_field_id = @field_id
and td.td_ref_uid = a.assn_uid
and a.task_uid = t.task_uid
and a.res_uid = r.res_uid
and td.td_ref_uid = @ref_uid

select @period = @period + 1
end

delete from #td_data where Value = 0 or Value is null

select Assn_UID,
Task_Name,
Res_Name,
c.CONV_STRING 'Contour_Type',
Period_Start,
'Period_Unit' = case Period_Unit
when 0 then 'minute'
when 1 then 'hour'
when 2 then 'day'
when 3 then 'week'
when 5 then 'month'
when 8 then 'year'
end,
'Value' = case Contour_Type_ID
when 1 then ((Value/1000)/60)
when 2 then ((Value/1000)/60)
when 3 then ((Value/1000)/60)
when 4 then ((Value/1000)/60)
when 5 then (Value/100)
when 6 then (Value/100)
when 7 then ((Value/1000)/60)
when 8 then (Value/100)
when 9 then ((Value/1000)/60)
when 10 then (Value/100)
when 11 then Value
end,
'Value Unit' = case Contour_Type_ID
when 1 then 'hours'
when 2 then 'hours'
when 3 then 'hours'
when 4 then 'hours'
when 5 then 'dollars'
when 6 then 'dollars'
when 7 then 'hours'
when 8 then 'dollars'
when 9 then 'hours'
when 10 then 'dollars'
when 11 then 'percent'
end
from #td_data t_d, MSP_CONVERSIONS c
where c.STRING_TYPE_ID = 107
and t_d.Contour_Type_ID = c.CONV_VALUE
order by Task_Name, Res_Name, Period_Start

drop table #td_data

 

[Albert D. Kallal]

Gee, I didn't think the script was going to be so long.

Is this just a one-time thing you need to run?

If yes, then I would fire up the msde engine, and runt he script on that.

You should be aware that for the last 3 versions of ms-access, you can use a
true 100% sql server based engine in place of JET. This desktop version of
sql comes free with office. That engine could also run your script as is.

Unfortunately, that is a rather long script for a newsgroup post, and to
convert into code for ms-access is going to be a bit work for sure. One has
to decipher the p/sql and then "convert" it into ms-access.

I guess the hope here was that the script was not too long (so much for that
hope!, it certainly is too long for a newsgroup post!). It is not clear
how/what other existing code was intended to call this code routine. In
other words, you might well convert this script to ms-access, but likely,
the other parts of the application and code also needs to be converted to
use that new script.

In other words, if you don't have a good understanding of the application,
and how the stored procedure is to be called, then JUST converting this
routine would NOT get you a working application. If it is in fact a stand
alone script (which it does not seem to be), then just run the code on sql
server to "create" the table,a and then export that table to ms-access using
the DTS on sql server.

Are you fluent in sql, and VB?

To convert that code to run in ms-access, you need to know sql, and VB and
pl/sql.

So, you would get something like:

Public Sub MySqlProc

dim ref_uid as integer
dim field_id as integer
dim proj_id as integer
dim period as int

dim strSql as string

field_id = 5
proj_id = 5
ref_uid = 13
period = 0 '-- Do not edit!

strSql = "create table #td_data (Assn_UID int, Task_Name varchar(255)," &
_
"Res_Name varchar(255),Contour_Type_ID int," & _
"Period_Start datetime,Period_Unit varchar(255), Value float )"

' and so on.

You then execute the sql like:

currentdb.Execute strSql

Really, you need good knowledge of both pl/sql, sql, and VB. Also, knowing
the ms-access object model is also required here. In other words, you need a
developer who is fluent in ms-access to make this work for you. Also, that
example seems to be using a temp/table, or so called cursor (and we don't
have those in ms-access!). So, you could most certainly use a temp table,
but your design must take into account the multi-user aspects when using
temp tables.

As mentioned, you can run that code in the desktop version of sql that comes
with ms-access, and then transfer it to a JET file share. The above example
air code is just the first few lines of the pl/sql that was converted into
VBA/sql. So, you certainly can run sql code in ms-access, but you have use
what is called in-line VB code. It is certainly a MUCH better language then
the pl script, but you will need good knowledge of both here to really do
this kind of conversion, and there is no such automatic way to doing this.