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?
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
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
mRes((x * (rs.RecordCount + 1)) + y) = "NULL"
ElseIf VarType(rs.Fields(x).Value
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;Passw
Is this all that I have to do is hardcode the following: password, user, database, and source?