vb操作sql資料庫
方法1，
建立一個模塊
將工程的啟動設置為 SUB MAIN
在模塊里寫下面代碼
Public db As Database
Public rs As Recordset
Sub main()
set db=db.opendatabase("資料庫路徑"<建議用相對路徑--就是把資料庫文件和工程文件放在同一目錄下--相對路徑就是APP.PATH+"資料庫名帶後綴">)
第一啟動窗體<比如：form1>.show
end sub
以後在其他窗體中如果要調用資料庫只用在窗體中寫代碼
set rs=openrecordset(<可以是資料庫的表--如"student",也可以是查詢語句--如"select* form student">)
這時資料庫的內容都存在FILE集合里
比方說set rs=openrecordset("select* form student")
rs.file(0)就是SQL語句查詢結果的第一列
rs.files就是整個結果
使用text1.text=rs.file(0)就可以把文本框於查詢結果幫定起來
如果要對資料庫進行修這時用以下代碼：
db.Execute （<SQL語句--如"insert into student(sno,sname) value("","")">)
方法2，
工程--部件--選擇microsoft data bound grid control 5.0
然後在窗體中添加左邊工具欄的 DATA 和DBGRID 控制項
選擇DBGRID 將屬性DATASOURCE 設置為DATA控制項的名字（如DATA1）
然後選擇DATA 將屬性CONNECT設置為ACCESSS 2000
設置DATABASENAME 選擇資料庫的存放路徑
最後設置RECORDSET 為需要連接的表的名字
運行就可以看到 DATAGRID中顯示了資料庫的信息
Ⅱ 怎麼在VB中調用SQL資料庫，資料庫已經建好，越詳細越好.
Public Function ConnectString() _
As String
ConnectString = "FileDSN=info.dsn;UID=sa;PWD=23" '連接字元器，改成你自己的
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset '這里是執行SQL語句，你只需要調用這個方法，把SQL查詢語句參數傳進來，最後返回一個Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查詢到" & rst.RecordCount & _
" 條記錄 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查詢錯誤: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
Public Function Testtxt(txt As String) As Boolean
If Trim(txt) = "" Then
Testtxt = False
Else
Testtxt = True
End If
End Function
Ⅲ 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資料庫具體操作
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語句與資料庫表格操作
Private
Sub
Command2_Click()
If
Adodc1.Recordset.EOF
=
False
Then
c
=
MsgBox("您確認要刪除該記錄嗎？",
vbOKCancel,
"刪除提示信息")
If
c
=
vbOK
Then
Adodc1.Recordset.Delete
Adodc1.RecordSource
=
"select
*
from
人員表"
Adodc1.Refresh
End
If
Else
MsgBox
"當前資料庫中沒有可刪除的數據記錄",
vbOKOnly,
"提示信息"
End
If
End
Sub
這就是一個刪除按鈕的代碼，希望對你有幫助
Ⅵ 怎樣在vb中訪問sql資料庫。代碼是什麼
On
Error
GoTo
err
Dim
cn
As
New
ADODB.Connection
'有密碼的連接：
'cn.ConnectionString
=
"Provider=SQLOLEDB.1;Persist
Security
Info=False;User
ID=登陸用戶名;Password=登錄密碼;Initial
Catalog=資料庫名;Data
Source=伺服器別名"
'無密碼的連接：
cn.ConnectionString
=
"Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist
Security
Info=False;Initial
Catalog=資料庫名;Data
Source=伺服器別名"
cn.CursorLocation=adUseClient'設置客戶端游標
cn.Open
Ⅶ 如何用VB實現連接SQL資料庫
一是：Public Function ConnectString() _
As String
ConnectString = "Provider=SQLOLEDB;User ID=用戶;Password=密碼;" & "Data Source=" + IP地址 + ";" & "Initial Catalog=資料庫名" _
End Function
二是：
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查詢到" & rst.RecordCount & _
" 條記錄 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查詢錯誤: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
以上復制到模塊「Mole1」中
三是:聲明：
Dim ADO1 As Recordset
Option Explicit
Dim MsgText As String
Dim TXTSQL As String
四是：連接
TXTSQL = "select * from 表名"
Set ADO1 = ExecuteSQL(TXTSQL, MsgText)
ADO1.Requery
五是顯示欄位
Set Text1.DataSource = ADO1
Text1.DataField = "欄位"
Set MSHFlexGrid1.DataSource = ADO1