Database SQL Server Connection using Class Module

[Luthius]

Guys I'm a newbie with OOP (Object-oriented programming)

I need your assistance to develop a connection inside a class so I can get/access this connection on a module.
Here below the function the connects to the SQL Server that is working fine.
What I need is make reference to this function outside the class so I can use SQL strings to retrieve the data from the server.

class Name:clsDbCon

[COLOR=#0000cd]Public Function [/COLOR]conectToSQLServer(strServerName, strDatabaseName [COLOR=#0000cd]As String[/COLOR])

[COLOR=#0000cd]Dim[/COLOR] conn [COLOR=#0000cd]As[/COLOR] ADODB.Connection

[COLOR=#0000cd]Set[/COLOR] conn = [COLOR=#0000cd]New [/COLOR]ADODB.Connection
    [COLOR=#0000cd]With [/COLOR]conn
[COLOR=#006400]     ' DSN-less connection using the ODBC driver[/COLOR]
    .Open "Driver={SQL Server};" & _
    "Server=" & strServerName & ";UID=USER;" & _
    "PWD=PASSWORD;" & _
    "Database=" & strDatabase
    .Close
[COLOR=#0000cd]     End With[/COLOR]
[COLOR=#0000cd]Set[/COLOR] conn = [COLOR=#0000cd]Nothing

End Function[/COLOR]

 

[Luthius]

I forgot to explain that the User and the Password are included on the code already

Any idea guys how do use this code outside the class?

 

[Kyle123]

Why do you want to do this in a class?

 

[Luthius]

As I can have several calls to this function on different databases the intention is reduce the code because will exist objects created to access and perform changes by SQL statements (Will be developed on classes as well).

In this case its a dns-less connection inside a class. I just want to refine it so I can refer it outside the class.

 

[Kyle123]

You haven't really given enough information to go on, but maybe something like this:
Class DbQuery

Private p_DatabaseName As String
Private p_ServerName As String
Private p_Connection As Connection

Public Property Let ServerName(ByRef rhs As String)
    p_ServerName = rhs
End Property

Public Property Let DatabaseName(ByRef rhs As String)
    p_DatabaseName = rhs
End Property

Public Function ExecuteQuery(ByRef sql As String) As ADODB.Recordset
    
    If Not isDbOpen Then OpenConnection
    Set ExecuteQuery = p_Connection.Execute(sql)
    
End Function

Private Sub OpenConnection()

    p_Connection.Open "Driver={SQL Server};" & _
    "Server=" & p_ServerName & ";UID=USER;PWD=PASSWORD;" & _
    "Database=" & p_DatabaseName
    
End Sub

Private Function isDbOpen() As Boolean

    isDbOpen = p_Connection.State = adStateOpen
    
End Function

Private Sub Class_Initialize()

    Set p_Connection = New Connection
    
End Sub

Private Sub Class_Terminate()

    If isDbOpen Then p_Connection.Close
    
End Sub

Module:

    Dim db As DBQuery
    Dim rs As Recordset
    
    Set db = New DBQuery
    
    db.DatabaseName = "database"
    db.ServerName = "server"
    
    Set rs = db.ExecuteQuery("select top 1 * from someTable")
    
    rs.Close

 

[Luthius]

Thank very much. Its working perfectly.
I really appreciate.