View Code Snippet

Author: Muhammad Musa Ali
Date Submitted: 11/15/2001
Category: Database
Compatibility: VB 6
This code has been accessed 35129 times.

Task: 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 Server 7.0/2000. By changing the Connection string, this can be used for any database. Each VB developer needs this sort of thing while doing database related work.Add this class to your project and name it say ‘Cdatabse’. Provide the Server name, Database name, user name and password as StrServer= “ MyServer” StrDB = “MyDatabaseName” StrUserName = “ TestUser” StrPassword = “ MyPassword Now, wherever you need to do database access, just instantiate this class as Dim Cdata as new Cdatabse The initialize event of the class will take the server name, database name, user name and password. Dim rst as ADODB.Recordset ‘ recordset to be used Call Cdata.connect ‘ In order to get a dynamic recordset, build your query and pass it to the GetRecordset function as Say strQuery = “ Select * from table1” Cdata. GetRecordset strQuery, rst Now you can use this recordset as you want. In order to get a forward only recordset, use the GetInfo method as Cdata. GetInfo strQuery, rst For updation or deletions, use Update method by passing your query as Cdata.Update strQuery And use the Disconnect method in the end.

Declarations

Code

'The class is as under: 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