Link to home
Start Free TrialLog in
Avatar of jumpy262000
jumpy262000

asked on

Assistance in linking a VB6 form to MS SQL Server

I'm a newbie working with Visual Basic 6 using MS SQL Server as the data source. The question that I have is how do I link a VB6 form to a MS SQL Server database?
 
Avatar of jumpy262000
jumpy262000

ASKER

I have an older VB6 application that my ex-boss created before he left the company. He created a module and use this code:

Public objCnn As New ADODB.Connection
Public objCmd As New ADODB.Command
Public objRst As New ADODB.Recordset
Public objPrm As New ADODB.Parameter
Public Const SQLOLEDB = "Provider=SQLOLEDB.1;Password=jobprod2005;Persist Security Info=True;User ID=logi0001_admin;Initial Catalog=Logistics;Data Source=VCSQL1" 'CMH1-D1-W-SQL03"

Is this all that I have to do is hardcode the following:  password, user, database, and source?
Add a module to your project and add the module code below. It requires a reference to MSADO. This function returns the number of results from your query or -1 if an error occured.

You can hopefully get an idea of how to use MSADO with my example or just use my module and example to piece somehting together.

' ####################### Module Code #######################
' Requires Reference to 'Microsoft ActiveX Data Objects 2.5 Library'
Option Explicit

Public Function RemoteCTQuery(ByVal SQLServer As String, ByVal mDataBase As String, ByVal mID As String, ByVal mPassWord As String, ByVal mQuery As String, Optional ByRef ErrStr As String, Optional ByRef Results As Variant) As Long ' Returns -1 if error
    On Error GoTo ERR_TRAP
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim mRes() As Variant
    Dim i As Long
    Dim r As Long
    Dim x As Long
    Dim y As Long
    Dim mStr As String
    Dim mBl As Boolean
    Dim mFieldCnt As Long
    RemoteCTQuery = -1
    If SQLServer = "" Then Exit Function
    If mDataBase = "" Then Exit Function
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.ConnectionString = "Provider=SQLOLEDB.1;" _
                & "Password=" & mPassWord & ";" _
                & "Persist Security Info=True;" _
                & "User ID=" & mID & ";" _
                & "Initial Catalog=" & mDataBase & ";" _
                & "Data Source=" & SQLServer
               
    conn.CursorLocation = adUseClient  'to get right value from server
    conn.ConnectionTimeout = 10
    conn.Open
    rs.Open mQuery, conn
    RemoteCTQuery = rs.RecordCount
    ReDim mRes((rs.RecordCount * rs.Fields.Count) + rs.Fields.Count - 1)
    mFieldCnt = rs.Fields.Count
    If mFieldCnt <> 0 Then
        rs.GetRows
        rs.MoveFirst
        For y = 0 To rs.RecordCount
            For x = 0 To mFieldCnt - 1
                If y = 0 Then
                    ' Record 0 is actually the column headers (label)
                    mRes((x * (rs.RecordCount + 1)) + y) = rs.Fields(x).Name
                Else
                    If VarType(rs.Fields(x).Value) = vbNull Then
                        mRes((x * (rs.RecordCount + 1)) + y) = "NULL"
                    ElseIf VarType(rs.Fields(x).Value) = vbBoolean Then
                        mRes((x * (rs.RecordCount + 1)) + y) = rs.Fields(x).Value
                    Else
                        mRes((x * (rs.RecordCount + 1)) + y) = rs.Fields(x).Value
                    End If
                End If
            Next x
            If y > 0 Then rs.MoveNext
        Next y
    End If
    If rs.State <> 0 Then rs.Close
    If conn.State <> 0 Then conn.Close
    Set rs = Nothing
    Set conn = Nothing
    Results = mRes
    Exit Function
ERR_TRAP:
    ErrStr = Err.Description
End Function


' ####################### USAGE EXAMPLE #######################
Private Sub Command1_Click()
    Dim lRet As Long
    Dim sQuery As String
    Dim vRes() As Variant
    Dim mVar As Variant
    Dim i As Long
    Dim mFieldCnt As Long
    Dim mCurField As String
    sQuery = "SELECT * FROM Sometable" ' Example!
    lRet = SQLQuery("VCSQL1", "Logistics", "logi0001_admin", "jobprod2005", sQuery, serr, vRes, mFieldCnt)
    If lRet = -1 Then
        MsgBox serr
    ElseIf lRet > 0 Then
        ' vRes contains an array of your results.
        ' Process your results
        For y = 0 To lRet
            For x = 0 To mFieldCnt - 1
                mVar = mRes((x * (lRet + 1)) + y)
                If y = 0 Then
                    ' mVar = A Field Name
                    mCurField = mVar
                    ' ########
                    ' Add your code here
                    ' ########
                Else
                    ' mVar = A result
                    Debug.Print mCurField & ": " & CStr(mVar)
                    ' ########
                    ' Add your code here
                    ' ########
                End If
            Next x
        Next y
    End If
End Sub
ASKER CERTIFIED SOLUTION
Avatar of LostIt6
LostIt6

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial