Option Explicit
Private Function Selectsql(SQL As String) As ADODB.Recordset '返回ADODB.Recordset对象
Dim ConnStr As String
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set Conn = New ADODB.Connection
'On Error GoTo MyErr:
ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=登录数据库用户名(默认为sa);Password=登录数据库密码;Initial Catalog=数据库名;Data Source=服务器名(默认为:MERRYCHINA)" '这是连接SQL数据库的语句
Conn.Open ConnStr
rs.CursorLocation = adUseClient
rs.Open Trim$(SQL), Conn, adOpenDynamic, adLockOptimistic
Set Selectsql = rs
'Exit Function
'MyErr:
'Set rs = Nothing
'Set Conn = Nothing '释放相关的系统资源
'MsgBox Err.Description, vbInformation, "系统提示" '显示出错信息
End Function
Private Sub Form_Load()
Dim SQL As String
Dim rs As ADODB.Recordset
Dim X As Long
On Error GoTo Err_box
SQL = " select * from 用户表"
Set rs = Selectsql(SQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
For X = 1 To rs.RecordCount
Combo1.AddItem rs.Fields("用户名").Value
rs.MoveNext
Next X
Combo1.ListIndex = 0
End If
rs.Close
Exit Sub
Err_box:
End Sub
Private Sub Command1_Click()
Dim SQL As String
Dim rs As ADODB.Recordset
If Text1.Text = "" Then
MsgBox "请输入口令!", 16
Text1.SetFocus
Exit Sub
End If
If Combo1.Text = "" Then
MsgBox "请选择登录用户!", 16
Combo1.SetFocus
Exit Sub
End If
SQL = "SELECT * FROM 用户表 WHERE 用户名='" & Combo1.Text & "' AND 密码='" & Text1.Text & "' "
Set rs = Selectsql(SQL)
If rs.RecordCount > 0 Then
Form1.Show '想要打开的主窗体
MsgBox "恭喜兄弟,登录成功!", 64, "提示"
Unload Me
Else
MsgBox "口令不对,请重新输入!", 16, "提示"
Text1.SetFocus
End If
End Sub
'**********************************************************************
'说明:1) 在工程中引用Microsoft ActiveX Data Objects 2.8 Library ,其它版本也行如:2.0
' 2) 在窗体中加Texe1.text(文本框控件),Combo1.text(组合框控件),Command1(命令按钮)各一个
' 3) 在SQL Server2000中创建数据库，新建表"用户表",表中包含"ID,姓名,密码"等字段,然后将以上代码复制,OK搞定
4) 以上方式无需加载ADO控件,方便!
Ⅱ VB如何连接SQL数据库
一个用VB连接SQL数据库连接的模块2007年01月22日 星期一 下午 05:29下面是一个连接数据库的模块，大家可以使用它来连接SQL数据库，使用起来比ADO控件还简单：
代码如下：
Public rsNew As ADODB.Recordset
Public CnNew As ADODB.Connection
Public addFlag As Boolean
'连接数据库
Public Function OpenCn() As Boolean
Dim Mag As String
On Error GoTo strErrMag
Set CnNew = New ADODB.Connection
CnNew.ConnectionTimeout = 25
CnNew.Provider = "sqloledb"
CnNew.Properties("data source").value = "" 'SQL服务器的名
CnNew.Properties("initial catalog").value = "pubs"'库名
CnNew.Properties("integrated security").value = "SSPI"'登陆类型
'con.Properties("user id").value = "sa"
'con.Properties("password").value = "wwww"
CnNew.Open
OpenCn = True
addFlag = True
Exit Function
strErrMag:
Mag = "数据库末连接"
Call MsgBox(Mag, vbOKCancel, "错误:数据库连接")
addFlag = False
End
End Function
Public Sub Clocn()
'闭关数据库
On Error Resume Next
If CnNew.State <> adStateClosed Then CnNew.Close
Set CnNew = Nothing
End Sub
Public Function OpenRs(ByVal strSql As String) As Boolean
'连接数据库记录集
Dim Mag As String
Dim rpy As Boolean
On Error GoTo strErrMag
Set rsNew = New ADODB.Recordset
If addFlag = False Then rpy = OpenCn
With rsNew
.ActiveConnection = CnNew
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSql
End With
addFlag = True
OpenRs = True
Exit Function
strErrMag:
Mag = "数据库数据集末连接"
Call MsgBox(Mag, vbOKOnly, "错误:数据库连接")
OpenRs = False
End
End Function
Public Sub cloRs()
'闭关数据集
On Error Resume Next
If rsNew.State <> adStateClosed Then rsNew.Close
Set rsNew = Nothing
End Sub
只要在你的标准模块中加入该代码，你就可以使用其中的函数来连接数据源了。
Ⅲ vb如何连接sql数据库
1、打开代码窗口，添加引用：Imports System.Data.SqlClient。
Ⅳ vb怎么连接sql
在窗体上添加adodc控件
鼠标右键-adodc属性-在通用选项卡里，点生成，选for
SQL
Server驱动程序-下一步-连接选项卡里
1.输入服务器名称，2.使用Windows
NT
集成安全设置。3.选择一个数据库
点测试连接，看看是否成功。按确定
点记录源选项卡，命令类型选8，命令文本（SQL）写：select
*
from
table1
确定，就连接到SQL数据库的表table1了。
Private
Sub
Command1_Click()
If
Not
Adodc1.Recordset.EOF
Then
Text1.Text
=
Adodc1.Recordset.Fields("line1")
End
If
End
Sub
Ⅳ VB怎么连接MSSql数据库
要连接SQL数据库有两种方法用ADODC或ADO。
方法一:
（ADODC)
首先须引用Projects-Components-Microsoft
ADO
Data
Controls
6.0
(OLEDB)。
之后在表单设置ADODC控件。然后Form_Load()输入以下代码：
Private
Sub
Form_Load()
ADODC1.ConnectionString
=
"Provider=SQLOLEDB;Password=密码;Persist
Security
Info=False;User
ID=用户名;Initial
Catalog=数据库名;Data
Source=服务器名"
ADODC1.CommandType
=
adCmdText
ADODC1.RecordSource
=
"select
*
from
表格名称"
ADODC1.Refresh
End
Sub
方法二:
(ADO)
首先须引用Projects-References-Microsoft
Active
X
Data
Objects
2.0
Library。
注意以下两段须写在程式最上面（Gerenal一般）中。
Dim
cn
As
New
ADODB.Connection
Dim
rs
As
New
ADODB.Recordset
Dim
SQL
As
String
然后在Form_Load()输入以下代码：
Private
Sub
Form_Load()
cn.ConnectionString
=
"Provider=SQLOLEDB;Password=密码;Persist
Security
Info=False;User
ID=用户名;Initial
Catalog=数据库名;Data
Source=服务器名"
cn.Open
If
rs.State
=
adStateOpen
Then
rs.Close
rs.CursorLocation
=
adUseClient
SQL
=
"select
*
from
表格名"
rs.Open
Trim$(SQL),
cn,
adOpenKeyset,
adLockPessimistic
End
Sub
Ⅵ 如何用vb链接sql的数据库
这段代码写在模块中,可以连接一个数据库
Public
DBConnct
As
New
ADODB.Connection
'连接数据库
Public
Function
Connt()
As
Boolean
'数据库连接函数
Dim
splstr
As
String
'App.Path
&
"\db\123.mdb为数据库路径
splstr
=
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
& App.Path
&
"\db\123.mdb"
& ";Persist
Security
Info=False"
DBConnct.Open
splstr
'连接数据库
Connt
=
True
End
Function
Ⅶ vb怎么连入SQL数据库，请详细一些，真心不明白
够详细了吧
Public conbook As ADODB.Connection '声明全局的连接对象变量
Public Sub Main() '入口程序
Dim bln As Boolean
bln = opensqlserver
If bln = True Then
On Error GoTo connerr
End If
Dim s As String
Dim fn As String '文件名
'如果数据库booksystem已经存在，先删除
conbook.Execute ("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'booksystem') EXEC sp_detach_db 'booksystem', 'true'")
'查找数据库文件是否存在，如果存在则根据文件附加数据库
If Dir(App.Path & "\database\*.mdf") <> "" And Dir(App.Path & "\database\*.ldf") <> "" Then
'存在时根据文件附加数据库
s = "exec sp_attach_db @dbname='booksystem', @filename1= '" & App.Path & "\database\booksystem_data.mdf', @filename2='" & App.Path & "\database\booksystem_log.ldf'"
conbook.Execute (s) '附加数据库
Else
'数据库文件不存在，则先查找备份文件是否存在，如果存在，根据备份还原数据库
fn = Dir(App.Path & "\database\*.dat")
If fn <> "" Then
s = "restore database booksystem from disk='" & App.Path & "\database\" & fn & "' with recovery,move 'booksystem_data' to '" & _
App.Path & "\database\booksystem_data.mdf' ,move 'booksystem_log' to '" & App.Path & "\database\booksystem_log.ldf'"
conbook.Execute s '恢复数据库到工程路径
Else
'备份文件不存在，则先根据sql语句创建数据库
Call createdb '调用过程，创建数据库
End If
End If
'附加成功后将当前数据库设置为student
conbook.Execute ("use booksystem")
frmStar.Show
'MDIMain.Show
Exit Sub
connerr:
MsgBox Err.Description
End Sub
'自定义函数，打开和SQL数据库的连接
Public Function opensqlserver() As Boolean
On Error GoTo conerr
Set conbook = New ADODB.Connection
With conbook
.Provider = "sqloledb"
.ConnectionTimeout = 30
.CommandTimeout = 30
.ConnectionString = "data source=.;user id=sa;password=;database=master" '连接字符串
.Open
End With
opensqlserver = True
Exit Function
conerr:
MsgBox "数据库连接失败！" & Err.Description
opensqlserver = False
End Function
Public Function rsopen(rs As ADODB.Recordset, sql As String) As Boolean
On Error GoTo rserr
'分配内存
Set rs = New ADODB.Recordset
'设置属性
rs.CursorLocation = adUseClient '客户端游标
rs.Open sql, conbook, adOpenStatic, adLockOptimistic, adCmdText
rsopen = True
Exit Function
rserr:
MsgBox "记录集打开失败！" & Err.Description
rsopen = False
End Function
Private Sub createdb() '自定义过程，创建数据库
On Error GoTo dberr
Dim s As String
Dim sql As String
Dim fn As String
'在工程路径下查找sql文件
fn = Dir(App.Path & "\database\*.sql")
If fn <> "" Then '如果存在，打开文件，执行sql语句
fn = App.Path & "\database\" & fn '获得文件路径名
Open fn For Input As #1
While EOF(1) = False
Line Input #1, s
sql = sql & s & vbCrLf ' 从文件中读取sql命令
Wend
conbook.Execute sql '执行sql创建数据库和表
Close #1 '关闭文件
Else
'不存在，则执行指定的sql语句创建数据库
s = "create database booksystem on(name='booksystem_data',filename='" & App.Path & "\database\booksystem_data.mdf',size=2,filegrowth=1)" & _
"log on(name='booksystem_log',filename='" & App.Path & "\database\booksystem_log.ldf',size=2,filegrowth=1)"
Open App.Path & "\database\booksystem.sql" For Output As #2 '打开文件，存储sql语句
Print #2, s '向文件中写入数据
Close #2 '关闭文件
MsgBox "创建数据库的sql语句已经写入文件", , "写入数据"
conbook.Execute s '执行sql语句创建数据库
End If
Exit Sub
dberr:
MsgBox "创建数据库失败！" & Err.Description
End
End Sub
Public Sub over()
On Error GoTo erra
'分离数据库，结束程序的运行
If conbook.State = 1 Then
conbook.Execute ("backup database booksystem to disk='" & App.Path & "\database\backupbooksystem.dat'") '备份数据库
conbook.Execute ("use master")
conbook.Execute ("EXEC sp_detach_db 'booksystem', 'true'") '分离数据库
conbook.Close '关闭连接
Set conbook = Nothing '释放内存
End If
End '结束程序
Exit Sub
erra:
End Sub
Ⅷ VB怎么连接 SQL 数据库
一个简单的方法:
首先,建立程序公共模块输入以下代码:
Public conn As New ADODB.Connection
'全局变量 rst 指针
Public rst As New ADODB.Recordset
'全局调用函数 打开数据库
Public Function openconn()
conn.ConnectionString = "连接信息"
'此连接信息最简单的生成办法:
'在任意窗口下建立一新ADODC控件
'选择控件属性,通用页,使用连接字符串,生成(U)
'利用该向导生成出字符串,然后将该字符串复制到"连接信息"中即可
conn.Open
End Function
'全局调用函数 关闭数据库
Public Function closeconn()
If conn.State = 1 Then
conn.Close
Set conn = Nothing
End If
End Function
然后在窗体文件中可以使用的函数:
打开数据库:openconn
注意:此函数为自定义函数,无参数,定义在公共模块中!
建立数据表连接:Set rst = conn.Execute("select * from 数据表名")
如做登陆页可以这样判断用户名密码:
Text1.Text = RTrim(rst.Fields("id")) And Text2.Text = RTrim(rst.Fields("password"))
注:其中Text1.Text为输入用户名TextBOX Text2.Text为输入密码TextBOX
数据表中 id 为保存用户名 password 为保存密码!此方法为非加密方法!
关闭数据库函数:
closeconn
Set rst = Nothing
注:此两行,必须在打开数据库的情况下使用!并且每打开一次数据库后必须先使用此两句将数据库关闭后才可打开另一数据库表文件!!!!!否则程序报错终止!
添加新数据函数
rst.AddNew
rst("字段名1") = Text1.Text
rst("字段名2") = Text2.Text
rst.Update
删除数据函数
rst.Delete
注:使用此函数前,必须保证数据库指针函数rst指向需删除数据,移动rst指针可以使用函数:rst.MoveNext或rst.MoveLast
更新数据函数
rst.Open "update 数据表名 set 字段1='" + Text1.Text + "',字段2='" + Text2.Text + "' where id='" + Text3.Text + "'", conn, 1, 3
注,其中 where id = 为更新特定数据表的查找!
如果熟悉SQL语句,还可编写出其他方式的查找,这里就不多做介绍了!
Ⅸ VB连接SQL最简单方法
Public conn As ADODB.Connection Public rs As ADODB.Recordset Public addFlag As Boolean '声明部分 Public Function OpenCn(ByVal Cip As String, ByVal users As String, ByVal pw As String, ByVal sjk As String) As Boolean '连接模块 填写数据库等信息 Dim mag As String On Error GoTo strerrmag Set conn = New ADODB.Connection conn.ConnectionTimeout = 25 conn.Provider = "sqloledb" conn.Properties("data source").Value = Cip '服务器的名字 conn.Properties("initial catalog").Value = sjk '库名 'conn.Properties("integrated security").Value = "SSPI" '登陆类型 conn.Properties("user id").Value = users 'SQL库用户名 conn.Properties("password").Value = pw '密码 conn.Open OpenCn = True If conn.State = 1 Then addFlag = True Exit Function strerrmag: mag = "连接失败！" Call MsgBox(mag, vbOKOnly, "错误：") addFlag = False Exit Function '连接错误消息 End Function '关闭数据库，释放连接 Public Sub cloCn() On Error Resume Next If conn.State <> adStateClosed Then conn.Close Set conn = Nothing End Sub Public Function openRs(ByVal strsql As String) As Boolean '连接数据库记录集 Dim mag As String Dim rpy As Boolean On 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 Function strerrmag: mag = "记录返回错误！" Call MsgBox(mag, vbOKOnly, "错误：") openRs = False 'End Exit Function '连接错误消息 End Function Public Sub cloRs() On Error Resume Next If rs.State <> adStateClosed Then rs.Clone Set rs = Nothing '释放记录集 End Sub