代码之家 › 专栏 › 技术社区 › Velocoder

如何从VBA代码中执行PostgreSQL函数?

odbc postgresql ms-access

Velocoder · 技术社区 · 14 年前

如何执行名为 Test1 从VBA代码中存储在PostgreSQL中的?

例如,我们有一个函数定义如下:

CREATE OR REPLACE FUNCTION "public"."Test1" (
)
RETURNS bit AS
$body$
BEGIN
    INSERT INTO test ("name") VALUES ('1');
RETURN 1;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Function TestCall()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Dim strSQl As String

strSQl = "SELECT * FROM Test1();" 

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQl, dbOpenDynaset, dbSeeChanges)
'this doesnt work as well: syntax error'
dbs.Execute strSQl 


If Not (rst.BOF And rst.EOF) Then
    do some work here
End If

End Function

但我现在 Syntax Error near FROM

3 回复 | 直到 13 年前

Kevin Ross 14 年前

它失败是因为您已将dbs设置为当前数据库。执行此语句时,access将查找表Test1(),当找不到时抛出hissy fit。

我不确定如何用postgre做到这一点,但这是我如何用sqlserver做类似的事情,所以我假设它是相同的,但有不同的连接字符串

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=âYour connection string goes here!â
Dbcon.open

Rst.open strsql

HansUp 14 年前

您可以对PostGreSQL函数使用Access“pass-through”查询。

SELECT Test1();

(我不需要FROM子句。)

Avagut 8 年前

尽管这是一篇很老的帖子,但我还是通过Google来到这里,终于找到了一个适合我的函数,它可能会帮助其他人解决同样的问题:在msaccess的一个公共模块中,保存以下内容

Option Compare Database
Public Function ProcessSTP_Fn(PostgresFnString As String)
    Dim qdf As DAO.QueryDef, rst As DAO.Recordset
''Server Settings
   On Error GoTo ErrHandler
    myServerName = "127.0.0.1"
    myDatabaseName = "my_db"
    myUserName = "my_user"
    myPassword = "my_pass"

''Connection String Components
    sqlConnString = ""
    sqlConnString = sqlConnString & "ODBC;Driver={PostgreSQL ANSI};"
    sqlConnString = sqlConnString & "Server=" & myServerName & ";"
    sqlConnString = sqlConnString & "Port=5432;"
    sqlConnString = sqlConnString & "Database=" & myDatabaseName & ";"
    sqlConnString = sqlConnString & "Uid=" & myUserName & ";"
    sqlConnString = sqlConnString & "Pwd=" & myPassword & ";"

''Create QueryDef and run the Postgres Function
    Set qdf = CurrentDb.CreateQueryDef("")
        qdf.Connect = sqlConnString
        qdf.SQL = PostgresFnString ''From the parameters
        qdf.ReturnsRecords = True
        Set rst = qdf.OpenRecordset
            ''Return any results expected
        rst.Close
        Set rst = Nothing
    Set qdf = Nothing
    Debug.Print "Query: " & PostgresFnString & " Passed Sucessfully At " & Now()
   Exit Function
ExitHandler:
   On Error GoTo 0
   ErrorState = True
   Exit Function
ErrHandler:
    Dim MyError As Error
    MsgBox Errors.Count
    For Each MyError In DBEngine.Errors
      With MyError
        MsgBox .Number & " " & .Description
      End With
    Next MyError
    GoTo ExitHandler
End Function

Public Sub TestPostgresFn()
    PostgresFnString = "SELECT * FROM Test1();"
    Call ProcessSTP_Fn(PostgresFnString)
End Sub