Create an ADODB connection inside a function VBA-ACCESS

Question

I am trying to simplify my code, having a module which contains all DB connection functions in one Access, so this is what I've already done:

Module "DB"

Public Function connect() As String
    Dim cn As ADODB.connection    

    cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=system;Data Source=localhost; User Id= root;Password= root;"
    connect = cn

End Function

Public Function sql(cn As String, query As String) As String

    Dim rs As ADODB.Recordset
    cn.Open

    Set rs = cn.Execute(query)

    cn.Close

    sql = rs

End Function

Event when I click in a button

Private Sub btn_run_Click()

conexao = connect()
result = sql(conexao, "SELECT TOP 1* FROM  MIS.MP_BASE_ACOES")

End Sub

Here is what my Access as an error: Translating to en -> "Compilation error: The type defined by the user wasn't defined"

What am I doing wrong? Is that the correct way to define a connection function?

PS: There's no error in ConnectionString, I just changed some content because it is confidential.

Edit1: Following FunThomas, I really have forgotten to mark all the references like ActiveX from my project, but it still not working, now with this error: "Uncompatible argument ByRef"

I assume that your compile error comes from a missing reference to `Microsoft Active X DataObjects`, so the type `ADODB.connection` is not known. Anyhow, your code horribly mixes data type `connection` with `string` and will for sure not work — FunThomas, Oct 23 '17 at 12:49

Vityata · Answer 1 · 2017-10-23T13:13:00.660

1

In general, you code has the following errors:

Wrong usage of functions (Public Function connect() As String)
The SQL function is not called
The object cn is of type string, and thus it does not have the Execute procedure.

Try this and try to assign the TestMe to a button. The idea to give the ConnectionString as a separate Function is a good one:

Option Explicit

Public Function ConnectionString() As String

    ConnectionString = "Provider=SQLOLEDB; Data Source=1111111111; Database=ABC; User ID=NotSA; Password=NotTheSaPwd"

End Function

Public Sub TestMe()

    Dim rs  As ADODB.Recordset
    Dim cn  As ADODB.Connection

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.Open (ConnectionString)
    rs.Open "SELECT * FROM TABLE", cn

    Do While Not rs.EOF
        Debug.Print rs!ColumnName
        rs.MoveNext
    Loop

End Sub

edited Oct 23 '17 at 13:13

answered Oct 23 '17 at 12:58

Vityata

42,633
8
55
100

I assume that I do not work much with functions in VBA so sorry for the wrong usage of the language. Can this code be used in a module? I see that you're using this code to print something, but to turn it more flexible, can it return the array from the query or just to another sql task? like an insert, update? – jvbs Oct 23 '17 at 13:18
1

@jvbarsou - you can do anything, the printing is just an example. – Vityata Oct 23 '17 at 13:19
how the code would look like turning it more flexible? could you explain? – jvbs Oct 23 '17 at 13:19
@jvbarsou - take a look here for an INSERT INTO example - https://msdn.microsoft.com/en-us/library/office/ff834799.aspx – Vityata Oct 23 '17 at 13:21
got it @Vityata, but lets suppose I'm using the TestMe to a insert clause, does not need to return anything, but and if i want to do something different than a while loop? can the TestMe return me the object rs if its set so i can work with it out of the testme scope? – jvbs Oct 23 '17 at 13:43
@jvbarsou - what you need is here - https://stackoverflow.com/questions/2463212/how-to-return-a-recordset-from-a-function – Vityata Oct 23 '17 at 13:57
@jvbarsou, you need to learn about functions and about parameter passing ... i think that is what you meant by `module` – jsotola Oct 24 '17 at 04:42

score 0 · Answer 2 · answered Oct 23 '17 at 13:42

0

Currently you are passing a string when your function requires a connection object. Simply change parameter types accordingly:

Public Function sql(cn As ADODB.connection, query As String) As String

    Dim rs As ADODB.Recordset
    cn.Open

    Set rs = cn.Execute(query)

    cn.Close

    sql = rs

End Function

answered Oct 23 '17 at 13:42

Parfait

104,375
17
94
125

thanks for the answer! Do I have to change anything else in my code, incuding `connect()` function? Because running it, it returns synthax error: `Private Sub btn_run_Click() result = sql(cn as ADODB.Connection,"SELECT TOP 1* FROM MIS.MP_BASE_ACOES") End Sub` – jvbs Oct 23 '17 at 13:52
I thought your SQL query had a syntax error but thought you had a typo in above post. Put a space between `TOP 1` and asterisk, `*`. – Parfait Oct 23 '17 at 15:21

Create an ADODB connection inside a function VBA-ACCESS

2 Answers2