VB6.0 如何连接SQL 数据库

1、打开代码窗口，添加引用：Imports System.Data.SqlClient。

2、输入以下代码：

“Public conn1  As SqlConnection = New SqlConnection _

("server=192.168.1.79Initial Catalog= studentUser ID= panqePWD=shentai768@")”，vb就已经成功连接sql数据库了。

3、代码详解：声明关键字Public（因为是全局变量，所以用Public 来声明）。

4、连接参数。

5、如果SQL 数据库就在本机，则用以下代码连接：

("server=.Integrated Security=FalseInitial Catalog= studentUser ID= panqePWD=shentai768@")。

6：如果代码太长，影响可读性，可以用空格加"_"后，回车换行。

－、用DAO控件连接数据库1.与Access2000数据库连接Private Sub Command1_Click()'也可直接在控件属性中设置以下各项但在控件属性中不能写入密码'只有在数据数没有密码的情况下可以省略Data1.RefreshData1.Connect = "Access 2000"Data1.DatabaseName = App. Path + "/chncmadb.mdb"'数据库没有密码此句可省Data1.Connect = "pwd=123456"'Data1.RecordSource = "耕地资源管理单元属性数据表2004" Data1.RecordSource = "select * from耕地资源管理单元属性数据表2004"Data1.Refresh’move后才能正确显示记录个数End Sub2.与没有密码的DBF文件数据库连接Private Sub Command2_Click()Data1.Connect = "dBASE III"Data1.DatabaseName = App. Path' Data1.RecordSource ="DBF"Data1.RecordSource = "select * from dbf"Data1.Refresh’move后才能正确显示记录个数End Sub3.与没有密码的Excel文件数据库连接Private Sub Command3_Click()Data1.Connect = "Excel 8.0"Data1.DatabaseName = App.Path &"/EXcel.xls"Data1.RecordSource = "select * from [EXcel.xls]"Data1.Refresh’move后才能正确显示记录个End Sub 二、用DAO代码连接数据库'在使用DAO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[Microsoft DAO 3.6 Object Library]选项，或其它版本1.DAO代码与Access数据库连接Private Sub Command1_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开'如果无密码最后一个参数可以不要Set Db= OpenDatabase(App.Path &"/chncmadb.mdb", False, False, "pwd=123456")'不需要move来更新记录个数 'Set Rs = Db.OpenRecordset("耕地资源管理单元属性数据表2004") '需要move来更新记录个数Set Rs = Db.OpenRecordset("select * from [耕地资源管理单元属性数据表2004]")If Rs.RecordCount >0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub 2.DAO代码与没有密码的DBF文件数据库连接Private Sub Command2_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开Set Db = OpenDatabase(App.Path, False, False, "dbase III") '不需要move来更新记录个数'Set Rs = Db.OpenRecordset("DBF")’需要move来更新记录个数Set Rs = Db.OpenRecordset("select * from [DBF]") If Rs.RecordCount >0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd sub 3. 'DAO代码与没有密码的Excel文件数据库连接Private Sub Command3_Click()Dim Db As DatabaseDim Rs As Recordset'以共享、读写方式打开'如果无密码最后一个参数可以不要Set Db = OpenDatabase(App.Path &"/EXcel.xls", False, False, "Excel 8.0")'不需要move来更新记录个数 ' Set Rs = Db.OpenRecordset("EXcel.xls") '表格中的工作目录sheet '需要move来更新记录个数Set Rs = Db.OpenRecordset("select * from [EXcel.xls]") '表格中的工作目录sheet'If Rs.RecordCount >0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub 三、用ADO控件连接数据库'也可直接在控件属性中设置以下各项1.ADO控件与Access2000数据库连接Private Sub Command1_Click() '连接有密码的Access数据库 'Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &App.Path &"/chncmadb1.mdbJet OLEDB:DataBase PASSWORD=123456" '连接没有密码的Access数据库 Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &App.Path &"/chncmadb.mdbPersist Security Info=False" 'Adodc1.RecordSource = "[耕地资源管理单元属性数据表2004]" Adodc1.RecordSource = "select * from [耕地资源管理单元属性数据表2004]" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub 2.'ADO控件与DBF表连接Private Sub Command2_Click() 'Adodc1.ConnectionString = "Provider=MSDASQL.1Persist Security Info=FalseData Source=dBASE FilesDBQ=" &App.Path &"SourceType=DBF" 'Adodc1.ConnectionString = "Provider=MSDASQL.1Persist Security Info=FalseExtended Properties=DSN=Visual FoxPro TablesUID=SourceDB=”&app.path &”SourceType=DBFExclusive=NoBackgroundFetch=YesCollate=MachineNull=YesDeleted=Yes" 'Adodc1.ConnectionString = "Provider=MSDASQL.1Persist Security Info=FalseExtended Properties=DSN=dBASE FilesDBQ=”&app.path &”DefaultDir=”&app.path &”DriverId=533MaxBufferSize=2048PageTimeout=5" '能使表名长度不受限制 Adodc1.ConnectionString = "Provider=MSDASQL.1Driver=Microsoft Visual Foxpro DriverSourceDB=" &App.Path &"SourceType=DBFLocale Identifier=2052" 'Adodc1.RecordSource = "[DBF1]" Adodc1.RecordSource = "select * from DBF1" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub 3.'ADO控件与Excel表连接Private Sub Command3_Click() '下面一句测试未能通过 'Adodc1.ConnectionString = "Data Provider=MSDASQL.1driver=Microsoft Excel Driver *.xls)DBQ=" &App.Path &"/EXcel.xls" 'Adodc1.ConnectionString="Provider=MSDASQL.1Persist Security Info=FalseExtended Properties=DSN=Excel FilesDBQ=" &App.Path &"/EXcel.xlsDefaultDir=”&app.path &”DriverId=790MaxBufferSize=2048PageTimeout=5" Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0Persist Security Info=FalseData Source=" &App.Path &"/EXcel.xlsExtended Properties='Excel 8.0HDR=Yes'" 'Adodc1.RecordSource = "[EXcel.xls]" Adodc1.RecordSource = "select * from [EXcel.xls]" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub 4.'ADO控件与Oracle数据库连接Private Sub Command4_Click() 'Adodc1.ConnectionString = "Provider=MSDAORA.1Password=chncmadbUser ID=chncmadbData Source=towebserverPersist Security Info=True"Adodc1.ConnectionString="Provider=OraOLEDB.Oracle.1Password=chncmadbPersist Security Info=TrueUser ID=chncmadbData Source=towebserver" 'Adodc1.RecordSource = "T320481TR012004" '表名不能加方括号 Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub 5.'ADO控件与SQLserver数据库连接'未测试Private Sub Command5_Click() Adodc1.ConnectionString = "Provider=SQLOLEDB.1Password=111Persist Security Info=TrueUser ID=111Initial Catalog=111Data Source=111" 'Adodc1.RecordSource = "T320481TR012004" Adodc1.RecordSource = "select * from T320481TR012004" Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub 四、用ADO代码连接数据库'在使用ADO对象前应选定Visual Basic菜单下的[工程]中的引用了菜单中的[Microsoft.ActiveX Data Object 2.5 Library]选项，或其它版本1.'ADO代码与Access2000数据库连接Private Sub Command1_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0Data Source=" &App.Path &"/chncmadb1.mdbJet OLEDB:DataBase PASSWORD=123456"AdoRs.Open "select * from [耕地资源管理单元属性数据表2004]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 2.'ADO代码与DBF表连接Private Sub Command2_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open "Provider=MSDASQL.1Driver=Microsoft Visual Foxpro DriverSourceDB=" &App.Path &"SourceType=DBFLocale Identifier=2052"AdoRs.Open "select * from [DBF1]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub3.'ADO代码与Excel表连接Private Sub Command3_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open"Provider=Microsoft.Jet.OLEDB.4.0Persist Security Info=FalseData Source=" &App.Path &"/EXcel.xlsExtended Properties='Excel 8.0HDR=Yes'"AdoRs.Open "select * from [EXcel.xls]", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 4.'ADO代码与Oracle数据库连接Private Sub Command4_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open "Provider=OraOLEDB.Oracle.1Password=chncmadbPersist Security Info=TrueUser ID=chncmadbData Source=towebserver"AdoRs.Open "select * from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub 5.'ADO代码与SQLserver数据库连接'未测试Private Sub Command5_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient '.open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open "Provider=SQLOLEDB.1Password=111Persist Security Info=TrueUser ID=111Initial Catalog=111Data Source=111"AdoRs.Open "select * from T320481TR012004", AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub

有多种连接方式，可以用数据控件连接，也可以用组件连接。下面是用ADO组件来连接的例子（假定ACCESS数据库文件和程序是放在同一个文件夹的）：

Private Sub Form_Click() '点击窗体运行

Dim conn As Object, rs As Object

Set conn = CreateObject("Adodb.Connection")

conn.Open "provider=Microsoft.Jet.OLEDB.4.0 Data Source=" & App.Path & "\a.mdb"

Set rs = CreateObject("Adodb.Recordset")

rs.Open "select * from b", conn, 1, 1

Do Until rs.EOF

    '这里是循环列出表b中的每条记录，熟悉的人就知道怎么做了

    rs.MoveNext

Loop

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

End Sub

---