Calling SQL stored procedure from form control cmdButton

[PsyberFox]

Hi there,

I've got an SQL stored procedures which is working from SQL scheduled jobs -
so there are no syntax errors, etc. I am trying to run the sp from a form
control button within Access, but I get a syntax error on the
"case...datepart" command. This is the SQL sp:

insert into Prod_Knitting_Data_1 ([Date], [Month], [Year], MachNo,
MachModel, Shift, ShiftPeriod,
[Time], Style, [Size], Quantity, [8hrTarget], Target, Operator,
Technician, [TimeStamp])

select C.[Date], month([Date]), year([Date]), C.MachNo, KM.Model, C.Shift,
case when datepart(dw,C.[Date]) in (1,7) then 12 else 8 end as ShiftPeriod,
8 as [Time], C.Style, C.[Size], C.Quantity,
KP.Pairs as [8hrTarget],
case when Shift in ('E','F') then KP.Pairs / 8 * 12 else KP.Pairs / 8 * 8
end as Target,
KO.Operator, KT.Technician,
C.[TimeStamp]

from Prod_Knitting_Capture C left join
X03_Knit_Machines KM on KM.MachNo = C.MachNo left join
X03_Knit_MachinesProd KP on KP.Ref = substring(KM.Model,1,4) + C.Style left
join
X03_Knit_Operators KO on KO.[MachNo/Shift] = (C.MachNo + C.Shift) left join
--AND C.[Date] >= KO.StartDate left join
X03_Knit_Technicians KT on KT.MachNo = C.MachNo

Can someone please help...
Thank you!

 

[Danny Lesandrini]

If you're using an ADP, you can just execute it with the CurrnetProject.Connection.Execute method, but if you're in an MDB file,
then Access queries won't understand SQL Syntax, like DATEPART().

In an MDB, you might want to put the call into a SQL PassThrough Query. That will allow you to use the SQL Syntax. If you need
help figuring out the PassThrough, post back.

 

[PsyberFox]

Morning and thank you for your reply...

I am not 100% sure how to do the pass-through query, so help will be
appreciated.

Thank you kindly,
W

If you're using an ADP, you can just execute it with the CurrnetProject.Connection.Execute method, but if you're in an MDB file,
then Access queries won't understand SQL Syntax, like DATEPART().

In an MDB, you might want to put the call into a SQL PassThrough Query. That will allow you to use the SQL Syntax. If you need
help figuring out the PassThrough, post back.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi there,

I've got an SQL stored procedures which is working from SQL scheduled jobs -
so there are no syntax errors, etc. I am trying to run the sp from a form
control button within Access, but I get a syntax error on the
"case...datepart" command. This is the SQL sp:

insert into Prod_Knitting_Data_1 ([Date], [Month], [Year], MachNo,
MachModel, Shift, ShiftPeriod,
[Time], Style, [Size], Quantity, [8hrTarget], Target, Operator,
Technician, [TimeStamp])

select C.[Date], month([Date]), year([Date]), C.MachNo, KM.Model, C.Shift,
case when datepart(dw,C.[Date]) in (1,7) then 12 else 8 end as ShiftPeriod,
8 as [Time], C.Style, C.[Size], C.Quantity,
KP.Pairs as [8hrTarget],
case when Shift in ('E','F') then KP.Pairs / 8 * 12 else KP.Pairs / 8 * 8
end as Target,
KO.Operator, KT.Technician,
C.[TimeStamp]

from Prod_Knitting_Capture C left join
X03_Knit_Machines KM on KM.MachNo = C.MachNo left join
X03_Knit_MachinesProd KP on KP.Ref = substring(KM.Model,1,4) + C.Style left
join
X03_Knit_Operators KO on KO.[MachNo/Shift] = (C.MachNo + C.Shift) left join
--AND C.[Date] >= KO.StartDate left join
X03_Knit_Technicians KT on KT.MachNo = C.MachNo

Can someone please help...
Thank you!

 

[Danny J. Lesandrini]

Pass Through Queries are cool ... you're gonna like this.

Create a new query without any tables and select SQL From the View menu.
Select Pass-Through from the Query | SQL Specific menu
Select Properties from the View menu
Find the ODBC Connect property, click in it and click the Elipisis to the right
Select your ODBC connection and save the property.

Now paste this into your query ...

SELECT Name, Type FROM SysObjects
WHERE Type = 'U'
ORDER BY Name

This will give you a list of tables from the SQL Server SysObjects table. It
will work on any SQL Server database, but probably isn't really too useful
for any production work you have, but will demonstrate the SQL connection.

Speaking of connection, I usually change the DNS reference to a more generic
connection string so users don't need to have my named DSN configured.
This approach is easy to maintian because you can set the connection prop
of queries when you relink tables.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Morning and thank you for your reply...

I am not 100% sure how to do the pass-through query, so help will be
appreciated.

Thank you kindly,
W

If you're using an ADP, you can just execute it with the CurrnetProject.Connection.Execute method, but if you're in an MDB
file,
then Access queries won't understand SQL Syntax, like DATEPART().

In an MDB, you might want to put the call into a SQL PassThrough Query. That will allow you to use the SQL Syntax. If you
need
help figuring out the PassThrough, post back.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi there,

I've got an SQL stored procedures which is working from SQL scheduled jobs -
so there are no syntax errors, etc. I am trying to run the sp from a form
control button within Access, but I get a syntax error on the
"case...datepart" command. This is the SQL sp:

insert into Prod_Knitting_Data_1 ([Date], [Month], [Year], MachNo,
MachModel, Shift, ShiftPeriod,
[Time], Style, [Size], Quantity, [8hrTarget], Target, Operator,
Technician, [TimeStamp])

select C.[Date], month([Date]), year([Date]), C.MachNo, KM.Model, C.Shift,
case when datepart(dw,C.[Date]) in (1,7) then 12 else 8 end as ShiftPeriod,
8 as [Time], C.Style, C.[Size], C.Quantity,
KP.Pairs as [8hrTarget],
case when Shift in ('E','F') then KP.Pairs / 8 * 12 else KP.Pairs / 8 * 8
end as Target,
KO.Operator, KT.Technician,
C.[TimeStamp]

from Prod_Knitting_Capture C left join
X03_Knit_Machines KM on KM.MachNo = C.MachNo left join
X03_Knit_MachinesProd KP on KP.Ref = substring(KM.Model,1,4) + C.Style left
join
X03_Knit_Operators KO on KO.[MachNo/Shift] = (C.MachNo + C.Shift) left join
--AND C.[Date] >= KO.StartDate left join
X03_Knit_Technicians KT on KT.MachNo = C.MachNo

Can someone please help...
Thank you!

 

[PsyberFox]

Hi!

Blud-e marvelous... thank you for your help... and you were right, I love
it...

Ciao

Pass Through Queries are cool ... you're gonna like this.

Create a new query without any tables and select SQL From the View menu.
Select Pass-Through from the Query | SQL Specific menu
Select Properties from the View menu
Find the ODBC Connect property, click in it and click the Elipisis to the right
Select your ODBC connection and save the property.

Now paste this into your query ...

SELECT Name, Type FROM SysObjects
WHERE Type = 'U'
ORDER BY Name

This will give you a list of tables from the SQL Server SysObjects table. It
will work on any SQL Server database, but probably isn't really too useful
for any production work you have, but will demonstrate the SQL connection.

Speaking of connection, I usually change the DNS reference to a more generic
connection string so users don't need to have my named DSN configured.
This approach is easy to maintian because you can set the connection prop
of queries when you relink tables.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Morning and thank you for your reply...

I am not 100% sure how to do the pass-through query, so help will be
appreciated.

Thank you kindly,
W

If you're using an ADP, you can just execute it with the CurrnetProject.Connection.Execute method, but if you're in an MDB
file,
then Access queries won't understand SQL Syntax, like DATEPART().

In an MDB, you might want to put the call into a SQL PassThrough Query. That will allow you to use the SQL Syntax. If you
need
help figuring out the PassThrough, post back.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Hi there,

I've got an SQL stored procedures which is working from SQL scheduled jobs -
so there are no syntax errors, etc. I am trying to run the sp from a form
control button within Access, but I get a syntax error on the
"case...datepart" command. This is the SQL sp:

insert into Prod_Knitting_Data_1 ([Date], [Month], [Year], MachNo,
MachModel, Shift, ShiftPeriod,
[Time], Style, [Size], Quantity, [8hrTarget], Target, Operator,
Technician, [TimeStamp])

select C.[Date], month([Date]), year([Date]), C.MachNo, KM.Model, C.Shift,
case when datepart(dw,C.[Date]) in (1,7) then 12 else 8 end as ShiftPeriod,
8 as [Time], C.Style, C.[Size], C.Quantity,
KP.Pairs as [8hrTarget],
case when Shift in ('E','F') then KP.Pairs / 8 * 12 else KP.Pairs / 8 * 8
end as Target,
KO.Operator, KT.Technician,
C.[TimeStamp]

from Prod_Knitting_Capture C left join
X03_Knit_Machines KM on KM.MachNo = C.MachNo left join
X03_Knit_MachinesProd KP on KP.Ref = substring(KM.Model,1,4) + C.Style left
join
X03_Knit_Operators KO on KO.[MachNo/Shift] = (C.MachNo + C.Shift) left join
--AND C.[Date] >= KO.StartDate left join
X03_Knit_Technicians KT on KT.MachNo = C.MachNo

Can someone please help...
Thank you!