Functions within queries

  • Thread starter Thread starter Clay
  • Start date Start date
C

Clay

I'm not sure if this is the correct place for this
question, but I know people here know access, so heres the
question.

I am trying to automate an update process through VB.
This NT service simply runs 10 consecutive queries that
reside within an access database. The queries are run by
operators through the database front end as well.

Functions written within a module in the database reside
in some of the queries. As you can imagine, the queries
run perfectly when run from the database, however, when
running from my application, it doesn't recognize the
function name. While I did expect this somewhat, I
haven't been able to find a workaround. Changing the
queries is out of the question, so I am left with this
problem to solve.

The query is below. Just to help put things in
perspective, we are running DAO 3.6, access 97, Visual
Basic 5, and NT 4. Thank you.

INSERT INTO Warehouse ( Docket, Customer, GameName,
OrderQty, ESS, ESSQty, ImpressionLength, FinishingPlant,
TktsPerFt, FtRequired, StockType, DieCut, Laminated,
TargetYield, ActualYield, YieldVariance, BalanceMethod,
LastUpdated )
SELECT WarehouseData.Docket, WarehouseData.CustomerName AS
Customer, WarehouseData.GameName, WarehouseData.OrderQty,
WarehouseData.ESS, WarehouseData.ESSQty,
WarehouseData.ImpressionLength,
WarehouseData.FinishingPlant, WarehouseData.TktsPerFt,
WarehouseData.FtRequired, WarehouseData.StockType,
WarehouseData.DieCut, WarehouseData.Laminated,
RoundIt([WarehouseData]![TargetYield],4) AS TargetYield, 0
AS ActualYield, 0 AS YieldVariance,
WarehouseData.BalanceMethod, WarehouseData.LastUpdated
FROM WarehouseData LEFT JOIN Warehouse ON
WarehouseData.Docket = Warehouse.Docket
WHERE (((Warehouse.Docket) Is Null));


The function is:
Function RoundIt(ByVal dblNumber As Double, ByVal
intDecimals As Integer) As Double ' changed fom public to
global
On Error GoTo RoundError

Dim lngFactor As Long

Select Case intDecimals
Case 0
RoundIt = Int(dblNumber + 0.5)
Case Is > 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber * lngFactor) +
0.5)) / lngFactor
Case Is < 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber / lngFactor) + 0.5))
* lngFactor
End Select

RoundExit:
Exit Function

RoundError:
RoundIt = 0
Resume RoundExit

End Function
 
Unfortunately, if you're running a query from outside of Access, you're
communicating with the database strictly through the Jet Engine, which
doesn't know anything about user-defined functions.

The only workaround is to use Automation, which requires that Access exist
on the client machine.
 
Doug,

Will this problem be solved with Access 2003 and the
VB.Net developer extensions?

Ken McLean
-----Original Message-----
Unfortunately, if you're running a query from outside of Access, you're
communicating with the database strictly through the Jet Engine, which
doesn't know anything about user-defined functions.

The only workaround is to use Automation, which requires that Access exist
on the client machine.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clay said:
I'm not sure if this is the correct place for this
question, but I know people here know access, so heres the
question.

I am trying to automate an update process through VB.
This NT service simply runs 10 consecutive queries that
reside within an access database. The queries are run by
operators through the database front end as well.

Functions written within a module in the database reside
in some of the queries. As you can imagine, the queries
run perfectly when run from the database, however, when
running from my application, it doesn't recognize the
function name. While I did expect this somewhat, I
haven't been able to find a workaround. Changing the
queries is out of the question, so I am left with this
problem to solve.

The query is below. Just to help put things in
perspective, we are running DAO 3.6, access 97, Visual
Basic 5, and NT 4. Thank you.

INSERT INTO Warehouse ( Docket, Customer, GameName,
OrderQty, ESS, ESSQty, ImpressionLength, FinishingPlant,
TktsPerFt, FtRequired, StockType, DieCut, Laminated,
TargetYield, ActualYield, YieldVariance, BalanceMethod,
LastUpdated )
SELECT WarehouseData.Docket, WarehouseData.CustomerName AS
Customer, WarehouseData.GameName, WarehouseData.OrderQty,
WarehouseData.ESS, WarehouseData.ESSQty,
WarehouseData.ImpressionLength,
WarehouseData.FinishingPlant, WarehouseData.TktsPerFt,
WarehouseData.FtRequired, WarehouseData.StockType,
WarehouseData.DieCut, WarehouseData.Laminated,
RoundIt([WarehouseData]![TargetYield],4) AS TargetYield, 0
AS ActualYield, 0 AS YieldVariance,
WarehouseData.BalanceMethod, WarehouseData.LastUpdated
FROM WarehouseData LEFT JOIN Warehouse ON
WarehouseData.Docket = Warehouse.Docket
WHERE (((Warehouse.Docket) Is Null));


The function is:
Function RoundIt(ByVal dblNumber As Double, ByVal
intDecimals As Integer) As Double ' changed fom public to
global
On Error GoTo RoundError

Dim lngFactor As Long

Select Case intDecimals
Case 0
RoundIt = Int(dblNumber + 0.5)
Case Is > 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber * lngFactor) +
0.5)) / lngFactor
Case Is < 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber / lngFactor) + 0.5))
* lngFactor
End Select

RoundExit:
Exit Function

RoundError:
RoundIt = 0
Resume RoundExit

End Function
Click to expand...


.
Click to expand...
 
Not as far as I'm aware.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ken McLean said:
Doug,

Will this problem be solved with Access 2003 and the
VB.Net developer extensions?

Ken McLean
-----Original Message-----
Unfortunately, if you're running a query from outside of Access, you're
communicating with the database strictly through the Jet Engine, which
doesn't know anything about user-defined functions.

The only workaround is to use Automation, which requires that Access exist
on the client machine.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clay said:
I'm not sure if this is the correct place for this
question, but I know people here know access, so heres the
question.

I am trying to automate an update process through VB.
This NT service simply runs 10 consecutive queries that
reside within an access database. The queries are run by
operators through the database front end as well.

Functions written within a module in the database reside
in some of the queries. As you can imagine, the queries
run perfectly when run from the database, however, when
running from my application, it doesn't recognize the
function name. While I did expect this somewhat, I
haven't been able to find a workaround. Changing the
queries is out of the question, so I am left with this
problem to solve.

The query is below. Just to help put things in
perspective, we are running DAO 3.6, access 97, Visual
Basic 5, and NT 4. Thank you.

INSERT INTO Warehouse ( Docket, Customer, GameName,
OrderQty, ESS, ESSQty, ImpressionLength, FinishingPlant,
TktsPerFt, FtRequired, StockType, DieCut, Laminated,
TargetYield, ActualYield, YieldVariance, BalanceMethod,
LastUpdated )
SELECT WarehouseData.Docket, WarehouseData.CustomerName AS
Customer, WarehouseData.GameName, WarehouseData.OrderQty,
WarehouseData.ESS, WarehouseData.ESSQty,
WarehouseData.ImpressionLength,
WarehouseData.FinishingPlant, WarehouseData.TktsPerFt,
WarehouseData.FtRequired, WarehouseData.StockType,
WarehouseData.DieCut, WarehouseData.Laminated,
RoundIt([WarehouseData]![TargetYield],4) AS TargetYield, 0
AS ActualYield, 0 AS YieldVariance,
WarehouseData.BalanceMethod, WarehouseData.LastUpdated
FROM WarehouseData LEFT JOIN Warehouse ON
WarehouseData.Docket = Warehouse.Docket
WHERE (((Warehouse.Docket) Is Null));


The function is:
Function RoundIt(ByVal dblNumber As Double, ByVal
intDecimals As Integer) As Double ' changed fom public to
global
On Error GoTo RoundError

Dim lngFactor As Long

Select Case intDecimals
Case 0
RoundIt = Int(dblNumber + 0.5)
Case Is > 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber * lngFactor) +
0.5)) / lngFactor
Case Is < 0
lngFactor = 10 ^ Abs(intDecimals)
RoundIt = (Int((dblNumber / lngFactor) + 0.5))
* lngFactor
End Select

RoundExit:
Exit Function

RoundError:
RoundIt = 0
Resume RoundExit

End Function
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top