Question

vb和sql数据库连接的代码

Answer 1

提前申明，转自别人的博客，供参考！希望可以帮到你，助人为乐！

第一步，在ModConString模块中定义一系列变量

'定义一个属性过程反映连接字符串
Public Property Get conString() As Variant
conString =
"data source=.;initial catalog=Sims_four;user
End
Property
'定义一个提供者反映数据库类型
Public Property Get conProvide() As
Variant
conProvide = "sqloledb"
End Property

第二步，建立一个类OpenRs.cls

Option Explicit
Private cnSIMS As ADODB.Connection
'声明一个连接

Private rsSims As ADODB.Recordset '声明一个连接记录集

Public rs1 As ADODB.Recordset '声明一个连接到所需表的记录集
Public
rs2 As ADODB.Recordset

Public rst As ADODB.Recordset
Private Data As
Object
'---------------------------------------------------------
'
在类初始化时建立连接
'---------------------------------------------------------
Private
Sub Class_Initialize()
On Error GoTo DbnotOpen
Set cnSIMS = New
ADODB.Connection
With cnSIMS
.Provider = conProvide

.ConnectionString = conString
.ConnectionTimeout = 10
.Open

Set rst = New ADODB.Recordset
End With
Exit
Sub
DbnotOpen:
If Err = -2147467259 Then
Set cnSIMS =
Nothing
MsgBox "连接后台数据库失败！" & vbCrLf & vbCrLf &
"请检查配置是否完好,数据库Sims_four是否存在?", vbOKOnly + vbInformation, "学生信息管理系统"

End
End If
End
Sub
'---------------------------------------------------------
'data是一个指向ADO记录集的对象的引用
'用于设置class的数据源内容
'---------------------------------------------------------
Private
Sub Class_GetDataMember(DataMember As String, Data As Object)
Set Data =
rsSims
End Sub

'-------------------------------------------------------
'建立一个可以打开记录集的函数，需要是必须把表名传给函数
'-------------------------------------------------------
Public
Function rsDK(Table As String)
Set rsSims = New ADODB.Recordset

With rsSims
.Source = Table
.ActiveConnection = cnSIMS

.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open , ,
, , adCmdTable
End With
End
Function
'-------------------------------------------------------
'建立一个可以打开记录集(动态游标)的函数
'需要把SQL语句传递给函数
'-------------------------------------------------------
Public
Function rsDK1(sql As String)
Set rs1 = New ADODB.Recordset
With
rs1
.Source = sql
.ActiveConnection = cnSIMS
.CursorType =
adOpenKeyset
.LockType = adLockOptimistic
.Open , , , ,
adCmdText
End With
End
Function
'-------------------------------------------------------
'建立一个可以打开记录集(静态游标)的函数
'需要把SQL语句传递给函数
'-------------------------------------------------------
Public
Function rsDK2(sql As String)
Set rs2 = New ADODB.Recordset
With
rs2
.Source = sql
.ActiveConnection = cnSIMS
.CursorType =
adOpenStatic
.LockType = adLockOptimistic
.Open , , , ,
adCmdText
End With
End
Function
'-------------------------------------------------------
'类终止时,释放一切可能占据的资源
'-------------------------------------------------------
Private
Sub Class_Terminate()
Set cnSIMS = Nothing
Set rs1 =
Nothing
Set rs2 = Nothing
Set rsSims = Nothing
Set rst =
Nothing
End Sub

第三步，在窗体中使用这个类

Option Explicit
Dim myModiStudent As OpenRs '声明连接数据库的类
Private Sub
Form_Load()
Set myModiStudent = New OpenRs
myModiStudent.rsDK1 "SELECT *
FROM student_info" ’打开表，红字是表名
If Not myModiStudent.rs1.EOF Then
Set
StudentGrid.DataSource = myModiStudent.rs1 ’这里用到了MSHFlexGrid 控件
End
If
End Sub

Answer 2

直接用添加ODBC数据源，先添加ODBC数据源（添加拉到最下面就可以看到SQL)然后用ADO和datagrid部件将VB和数据库里面的数据库和对应表连接起来，就可以看到数据了。
adodc选中右键属性设置，点生成连接数据库。