Running a T/SQL in MS Access

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I am trying to run the following T/SQL in MS Access. Can
this be done? I know the syntaxs are not proper for MS
ACCESS, how would I go about converting this? Alsi, I am
not sure how to use temp tables in MS ACCESS.

Here is the Script:

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
 
Dear Joe:

You can run TSQL against any Access MSDE database. But then MSDE is a
version of SQL Server whose limitations do not prevent any of this
being done. You cannot run this against a Jet database. So, if you
have significant amounts of existing TSQL and SQL Server data, it
would seem to be a seamless way to build a Data Project and just work
directly with what you already have. This compatibility includes
temporary tables.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Ditto what Tom said, or... create a Pass Through query that calls the Stored
Proc that has the T/SQL, or re-create VBA to handle all of the logic against
a recordset for a regular MDB.
 
You should be asking yourself how to write
this thing more efficiently in t-sql.For example,
how do I get rid of the While loop without
opening up a cursor:).

Tools for Sql Server 2000 @
www.rac4sql.net
 
I am not sure I understand. I don't have the Stored Proc
in any database, it is a text file. All I have is a MS
Access database that I want to apply this SP. Microsoft
wrote this SP specifically for those who have the database
in MS SQL. I have the exact same database that this SP
was designed for but not in MS SQL, just in MS ACCESS. I
have no other conenctions or database. Hope this
clarifies
 
"Joe" wrote: in message [snipe]
Microsoft wrote this SP specifically for those who have the database
in MS SQL.
.
Click to expand...

So much for being a master of ones own product :~)
 
Reread your sentence: Microsoft wrote this SP specifically for those who
have the database in MS SQL.

If you are implying that Microsoft made something for MS SQL Server, then
you CAN NOT USE IT, because you do NOT have SQL Server, and Access doesn't
allow T/SQL or have Stored Procs. You will need to recreate the entire
functionality with Access Queries, or a VBA function.
 
[MVP] S.Clark" said:
If you are implying that Microsoft made something for MS SQL Server, then
you CAN NOT USE IT, because you do NOT have SQL Server, and Access doesn't
allow T/SQL or have Stored Procs. You will need to recreate the entire
functionality with Access Queries, or a VBA function.
Click to expand...

Yes, I understand that.
... Microsoft wrote this SP specifically for those who
have the database in MS SQL.
Click to expand...

Preciously.

Now do you get my point? :~)

Happy Holidays,
www.rac4sql.net
 
Back
Top