从Access窗体调用SQL Server函数 [英] Calling SQL Server functions from an Access form

问题描述

我正在寻找一种在Access前端使用SQL Server功能的正确方法。使用它的方法非常简单 - 只需调用函数，使用括号和瞧 - 准备就绪。但我坚持了另一种情况。 Whtt是否有两个单独的功能：一个是代码，另一个是解码数据？问题的背景可以在这里找到：用于在SQL中存储IP地址的数据类型服务器。我想在我的访问应用程序中使用这个示例，但是如何在MS Access窗体中执行两个单独的单向操作？当然，如果需要创建额外的视图或任何额外的图层来实现这一点，除了创建表单之外，我并不是非常固执，只是有点懒惰 - 我很乐意这样做。

解决方案

您所描述的确实可以通过绑定到ODBC链接表的Access窗体来完成。你需要做的是在表单上创建两个控件，绑定到链接表中的字段，

1. >
   
2. 用户可以与之交互的可见的未绑定控件。
   

例如，假设您有一个ODBC名为[dbo_temperatures]的链接表指向一个SQL Server表，它以摄氏温度存储温度值：
$ b

  - （在SQL Server Management Studio中创建表的代码）
 CREATE TABLE dbo.temperatures（
 id INT IDENTITY PRIMARY KEY，
 DATETIME，
 tempC INT）
  

SQL Server中还有两个标量函数可将摄氏度值转换为华氏度值，反之亦然

  CREATE FUNCTION dbo.fnCtoF（@tempC AS INT）RETURNS INT 
 AS 
 BEGIN 
 DECLARE @t AS FLOAT; 
 SET @t = CONVERT（FLOAT，@tempC）; 
 RETURN CONVERT（INT，（@t * 9/5）+ 32）; 
 END 
  

和

  CREATE FUNCTION dbo.fnFtoC（@tempF AS INT）RETURNS INT 
 AS 
 BEGIN 
 DECLARE @t AS FLOAT; 
 SET @t = CONVERT（FLOAT，@tempF）; 
 RETURN CONVERT（INT，（@t  -  32）* 5/9）; 
 END 
  

在窗体上（绑定到链接表[dbo_temperatures]）， / p>

1. 绑定到[tempC]字段的名为txtTempC的隐藏文本框，以及
   
2. 一个名为txtTempF的可见未绑定文本框。
   

表单有两个私有VBA函数来调用SQL Server函数

Private Function getFahrenheit（t As Variant）As Variant
If IsNull（t）Then
getFahrenheit = Null
Else
Dim cdb As DAO.Database，qdf As DAO.QueryDef，rst As DAO.Recordset
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef （）
qdf.Connect = cdb.TableDefs（dbo_temperatures）。连接
qdf.SQL =SELECT dbo.fnCtoF（& t&）AS x
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset（dbOpenSnapshot）
getFahrenheit = rst！ x
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set cdb = Nothing
End If
End Function

和

  Private Function getCelsius（t As Variant）As Variant 
 If IsNull（t）Then 
 getCelsius = Null 
 Else 
 Dim cdb As DAO.Database，qdf As DAO.QueryDef，rst as DAO.Recordset 
 Set cdb = CurrentDb 
 Set qdf = cdb.CreateQueryDef（）
 qdf.Connect = cdb.TableDefs（dbo_temperatures）。Connect 
 qdf.SQL =SELECT dbo.fnFtoC（& t& ）AS x
 qdf.ReturnsRecords = True 
 Set rst = qdf.OpenRecordset（dbOpenSnapshot）
 getCelsius = rst！x 
 rst .Close 
 Set rst = Nothing 
 Set qdf = Nothing 
 Set cdb = Nothing 
 End If 
 End Function 
  

然后，您可以使用Form的On Current事件来填充Fahrenheit文本框

  Private Sub Form_Current（）
 Me.txtTempF.Value = getFahrenheit（Me.txtTempC.Value）
 End Sub 
  

和Fahrenheit文本框的After Update事件更新摄氏温度文本框。

Private Sub txtTempF_AfterUpdate（）
Me.txtTempC.Value = getCelsius（Me。 txtTempF.Value）
End Sub


由于Celsius文本框绑定到[tempC ]字段，当记录保存时，更改将被写回表格。

I'm looking for a correct way to use SQL Server functions in an Access front-end. It's fairly simple to use it one way - just need to call function, use brackets and voila - ready. But I've stuck on the other case. Whtt if there are two separate functions: one to "code" and the second one to "decode" data? Background of the problem can be found here: Datatype for storing ip address in SQL Server. I would like to use this example in my access app, but how to do two separate, one way operations in MS Access form? Of course if there is need to create additional view or any additional layer to achieve this beside creating a form, I'm not very stubborn and just a little lazy - I'll be happy to do that.

解决方案

What you describe can indeed be accomplished with an Access form bound to an ODBC linked table. What you need to do is create two controls on the form

1. a hidden control bound to the field in the linked table, and
2. a visible unbound control that the user can interact with.

For example, say you have an ODBC linked table named [dbo_temperatures] pointing to a SQL Server table that stores temperature values in Celsius:

-- (code to create the table in SQL Server Management Studio)
CREATE TABLE dbo.temperatures (
    id INT IDENTITY PRIMARY KEY, 
    observed DATETIME, 
    tempC INT)

You also have two scalar functions in SQL Server to convert values from Celsius to Fahrenheit and vice-versa

CREATE FUNCTION dbo.fnCtoF(@tempC AS INT) RETURNS INT
AS
BEGIN
    DECLARE @t AS FLOAT;
    SET @t = CONVERT(FLOAT, @tempC);
    RETURN CONVERT(INT, (@t * 9 / 5) + 32);
END

and

CREATE FUNCTION dbo.fnFtoC(@tempF AS INT) RETURNS INT
AS
BEGIN
    DECLARE @t AS FLOAT;
    SET @t = CONVERT(FLOAT, @tempF);
    RETURN CONVERT(INT, (@t - 32) * 5 / 9);
END

On your form (bound to the linked table [dbo_temperatures]) you create

1. a hidden text box named "txtTempC" that is bound to the [tempC] field, and
2. a visible unbound text box named "txtTempF".

The form has two private VBA functions to call the SQL Server functions

Private Function getFahrenheit(t As Variant) As Variant
    If IsNull(t) Then
        getFahrenheit = Null
    Else
        Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
        Set cdb = CurrentDb
        Set qdf = cdb.CreateQueryDef("")
        qdf.Connect = cdb.TableDefs("dbo_temperatures").Connect
        qdf.SQL = "SELECT dbo.fnCtoF(" & t & ") AS x"
        qdf.ReturnsRecords = True
        Set rst = qdf.OpenRecordset(dbOpenSnapshot)
        getFahrenheit = rst!x
        rst.Close
        Set rst = Nothing
        Set qdf = Nothing
        Set cdb = Nothing
    End If
End Function

and

Private Function getCelsius(t As Variant) As Variant
    If IsNull(t) Then
        getCelsius = Null
    Else
        Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
        Set cdb = CurrentDb
        Set qdf = cdb.CreateQueryDef("")
        qdf.Connect = cdb.TableDefs("dbo_temperatures").Connect
        qdf.SQL = "SELECT dbo.fnFtoC(" & t & ") AS x"
        qdf.ReturnsRecords = True
        Set rst = qdf.OpenRecordset(dbOpenSnapshot)
        getCelsius = rst!x
        rst.Close
        Set rst = Nothing
        Set qdf = Nothing
        Set cdb = Nothing
    End If
End Function

Then you can use the On Current event of the Form to populate the Fahrenheit text box

Private Sub Form_Current()
    Me.txtTempF.Value = getFahrenheit(Me.txtTempC.Value)
End Sub

and the After Update event of the Fahrenheit text box to update the Celsius text box.

Private Sub txtTempF_AfterUpdate()
    Me.txtTempC.Value = getCelsius(Me.txtTempF.Value)
End Sub

Because the Celsius text box is bound to the [tempC] field, the change will be written back to the table when the record is saved.

这篇关于从Access窗体调用SQL Server函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！