VB如何连接SQL SERVER？

Answer 1

打开“控制面板”→“管理工具”→“数据源 (ODBC)”后，出现窗体。单击“系统DSN”选项卡后，点击“添加”按钮，出现窗体。选择其中的“SQL Server”后，单击“完成”，出现窗体。单击“下一步”，。选择“SQL Server验证”，一般ID为“sa”，密码为空（实际要根据具体情况输入）。单击“下一步”。更改默认数据库为所建立的数据库。单击“下一步”后完成设置。（2）VB中使用ADO数据库控件，实现与数据库的连接使用ADO数据库控件需要添加组件，控件工具箱中就出现需要的控件。将控件放在需要的窗体中，。鼠标右键调出ADODC属性,进行设置。在未建立数据源的情况下，也可进行“新建”或者使用第三个选择“使用连接字符串”。数据源设立完毕，就需要设立身份验证和记录源。以上设置也可在ADODC1控件的属性中进行设置(3)代码实现Public conn As ADODB.ConnectionPublic rs As ADODB.RecordsetPublic addFlag As Boolean '声明部分Public Function OpenCn(ByVal Cip As String, ByVal users As String, ByVal pw As String) As Boolean '连接模块 填写数据库等信息Dim mag As StringOn Error GoTo strerrmagSet conn = New ADODB.Connectionconn.ConnectionTimeout = 25conn.Provider = "sqloledb"conn.Properties("data source").Value = Cip '服务器的名字conn.Properties("initial catalog").Value = "pubs" '库名'conn.Properties("integrated security").Value = "SSPI" '登陆类型conn.Properties("user id").Value = users 'SQL库名conn.Properties("password").Value = pw '密码'sql = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=;Initial Catalog=pubs;Data Source=127.0.0.1" '如果不用这个模块也行可以，这一句便是常用的引擎。'conn.ConnectionString = sql conn.OpenOpenCn = TrueIf conn.State = 1 Then addFlag = TrueExit Functionstrerrmag: mag = "Data can't connect" Call MsgBox(mag, vbOKOnly, "Error:Data connect") addFlag = False Exit Function '连接错误消息End Function'关闭数据库，释放连接Public Sub cloCn()On Error Resume NextIf conn.State <> adStateClosed Then conn.CloseSet conn = NothingEnd SubPublic Function openRs(ByVal strsql As String) As Boolean '连接数据库记录集Dim mag As StringDim rpy As BooleanOn Error GoTo strerrmag Set rs = New ADODB.Recordset If addFlag = False Then rpy = True With rs .ActiveConnection = conn .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open strsql End With addFlag = True openRs = True 'End '将记录集给rs Exit Functionstrerrmag: mag = "data not connect" Call MsgBox(mag, vbOKOnly, "error:connect") openRs = False End 'Exit Function '连接错误消息End FunctionPublic Sub cloRs()On Error Resume NextIf rs.State <> adStateClosed Then rs.CloneSet rs = Nothing '释放记录集End Sub