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
– FunThomas, Commented Oct 23, 2017 at 12:49

Vityata · Accepted Answer · 2017-10-23 13:13:00Z

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, 2017 at 13:13

answered Oct 23, 2017 at 12:58

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jvbs Over a year ago

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?

Vityata Over a year ago

@jvbarsou - you can do anything, the printing is just an example.

jvbs Over a year ago

how the code would look like turning it more flexible? could you explain?

Vityata Over a year ago

@jvbarsou - take a look here for an INSERT INTO example - msdn.microsoft.com/en-us/library/office/ff834799.aspx

jvbs Over a year ago

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?

|

Parfait · Accepted Answer · 2017-10-23 13:42:30Z

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, 2017 at 13:42

Parfait

108k19 gold badges103 silver badges138 bronze badges

2 Comments

jvbs Over a year ago

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

Parfait Over a year ago

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, *.

Collectives™ on Stack Overflow

Create an ADODB connection inside a function VBA-ACCESS

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related