• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Database Access Class for VB and ADO 2.5/2.6 I am presenting a very simple class to use for databas

Database Access Class for VB and ADO 2.5/2.6 I am presenting a very simple class to use for databas

Database Access Class for VB and ADO 2.5/2.6 I am presenting a very simple class to use for database access using VB 6.0 and ADO for SQL

API Declarations


' public variables for the server, database, user name and password
Public mServer as String
Public mDb As String
Public mUsr As String
Public mPwd As String



Rate Database Access Class for VB and ADO 2.5/2.6 I am presenting a very simple class to use for databas

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

 


Private Sub Class_Initialize()
' initializing the class for the Server Name, Database name, user name and password for connection string
mDb = strDb
mServer= strServer
mUsr = strUserName
mPwd = strPwd
End Sub

Public Sub Connect()
Set Conn = New Connection
Conn.CursorLocation = adUseClient
' connection string for SQL Server, you will have to change only this connection ‘string for a different database
strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" + mUsr + ";" _
        + "Password=" + mPwd + ";Initial Catalog= " + mDb+ " ; Data Source=" + mServer + ""
Conn.Open strConn

End Function



' takes the query as a string and returns the opened recordset
Public Function GetRecordset (ByVal strQuery As String, rstResult As Recordset) As Boolean
Dim rst As New Recordset
Conn.CommandTimeout = 60
rst.Open strQuery, Conn, adOpenDynamic
If rst.RecordCount <= 0 Then
    GetRecordset = False
Else
    Set rstResult = rst
    GetRecordset = True
End If

End Function

' takes the query as a string and returns the forward only recordset using Command Object
Public Sub GetInfo(strQuery As String, ByRef rst As ADODB.Recordset) 

Dim oCommand As New ADODB.Command
Dim strCmd As String
' specify the connection
oCommand.ActiveConnection = Conn 
' build the command
strCmd = strQuery
oCommand.CommandText = strCmd
oCommand.CommandTimeout = 60
' execute the command
Set rst = oCommand.Execute

End Function

' takes a update or delete query  as string and updates the database
Public Sub UpdateIt(ByVal strQuery As String) 

Dim oCommand As New ADODB.Command
Dim lngRecord As Long
Dim i As Integer
' specify the connection
oCommand.ActiveConnection = Conn 
' prepare the sql command
oCommand.CommandText = strQuery
' execute the command
oCommand.Execute 

End Function
      
Public Sub DisConnect()
Conn.Close ' close the connection
Set Conn = Nothing ' release the resources
End Sub