首先引用ADO: 方法:工程->引用->Microsoft ActiveX Data Object 2.7 Library dim rs As New ADODB.Recordset dim cn As New ADODB.Connection '建立与数据库的连接 cn.ConnectionString = "provider=sqloledb;server=服务器;uid=用户名;pwd=密码;database=数据库" cn.Open与ACCESS的连接 1、带密码的(Access2000) dim rs As New ADODB.Recordset dim cn As New ADODB.Connection cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;user id=admin;jet oledb:database password=密码;data source=数据库"2.不带密码 dim rs as new adodb.recordset dim cn as new adodb.connection cn.connectionString="provide=microsoft.jet.oledb.4.0;data source=数据库" cn.Open
dim strSQL as string strSQL="driver={sql server};database=DBNAME;pwd=***;uid=admin"
access数据库: Cn As New ADODB.Connection Set Cn = New ADODB.Connection Dim Rcount As Integer Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\csy.mdb" Cn.Open sqlserver: Cn.ConnectionString = "provider=sqloledb;database=XF_MANAGEMENT;uid=" & SQL_user & ";pwd=" & SQL_pwd & ";server=" & ServerName"
建立数据工程 access数据库: Cn As New ADODB.Connection Set Cn = New ADODB.Connection Dim Rcount As Integer Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\csy.mdb" Cn.Open sqlserver: Cn.ConnectionString = "provider=sqloledb;database=XF_MANAGEMENT;uid=" & SQL_user & ";pwd=" & SQL_pwd & ";server=" & ServerName" 访问表内数据 dim rs as new adodb.r...(记录集) rs.open后的参数意义sql 为具体查询语句 RS.OPEN SQL,CONN,A,B A: ADOPENFORWARDONLY(=0) 只读,且当前数据记录只能向下移动 ADOPENSTATIC(=3) 只读,当前数据记录可自由移动 ADOPENKEYSET(=1) 可读写,当前数据记录可自由移动 ADOPENDYNAMIC(=2) 可读写,当前数据记录可自由移动,可看到新增记录 B: ADLOCKREADONLY(=1) 默认值,用来打开只读记录 ADLOCKPESSIMISTIC(=2) 悲观锁定 ADLOCKOPTIMISTIC(=3) 乐观锁定 ADLOCKBATCHOPTIMISTIC(=4) 批次乐观锁定
Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Combo1_Click() rs.Open "SELECT * FROM TABLENAME WHERE NAME = '" & txtName.Text & "'", cn, adOpenDynamic, adLockOptimistic If Not rs.EOF Then Label1.Caption = rs!ID Label2.Caption = rs!Memo End If End SubPrivate Sub Form_Load() cn.ConnectionString = "" cn.Open '查询字符串可以上这里查 'http://www.connectionstrings.com/ End Sub
直接用ADO控间不是更好吗?
给你我以前写的一个ADO for ACCESS的代码看看,希望对你有帮助: Private Sub OPDB_Click()
With cd1 .FileName = "" .Flags = 2050 .Filter = "Access DataBase(*.MDB)|*.MDB" .DialogTitle = "Open DataBase As(*.MDB)" .ShowOpen Openfilename = .FileName Openfiletitle = .FileTitle End With
If Openfilename = "" Then 'MsgBox "You had cancel the option of open DataBase" Exit Sub End If
Dim infound As Integer infound = InStr(Openfiletitle, ".") Openfiletitle = Left(Openfiletitle, infound - 1)
infound = InStr(Openfilename, cd1.FileTitle) path = Left(Openfilename, infound - 1) 'Debug.Print path Me.Caption = path & "…" & Openfiletitle If bOpenconn = True Then CNN1.Close Set CNN1 = Nothing End If CNN1.CursorLocation = adUseClient CNN1.Open "provider=microsoft.jet.oledb.4.0;data source= " & Openfilename
PUTVALUE.Enabled = True Putout.Enabled = True ADDTEMP.Enabled = True bOpenconn = True Sb.Panels.Item(1).Text = "数据库已打开" End Sub Private Sub ADDTEMP_Click()
Screen.MousePointer = vbHourglass Sb.Panels.Item(1).Text = "增加空间属性" On Error Resume Next CNN1.Execute "alter table ST_ZD add COLUMN TEMP_ZD string(15);"
Dim RS5 As ADODB.Recordset Dim RS6 As ADODB.Recordset Dim Zdtemp As String Set RS5 = New ADODB.Recordset RS5.CursorType = adOpenDynamic RS5.LockType = adLockBatchOptimistic
RS5.Open "SELECT * FROM ST_ZD ORDER BY OBJECTID", CNN1
Set RS6 = New ADODB.Recordset RS6.CursorType = adOpenDynamic RS6.LockType = adLockBatchOptimistic
Do While Not RS5.EOF Zdtemp = FindFieldValue(RS6, "TEMP_ZD", "OBJECT_ID", "TT_ZD", RS5!OBJECTID) RS5!TEMP_ZD = Zdtemp RS5.MoveNext
Loop
RS5.UpdateBatch RS5.Close Set RS5 = Nothing Set RS6 = Nothing
Screen.MousePointer = vbDefault Sb.Panels.Item(1).Text = "此过程已完成" CNN1.Execute "alter table TT_ZDJZX drop COLUMN TEMP_ZD" CNN1.Execute "alter table TT_ZDJZX drop COLUMN JZD_X" CNN1.Execute "alter table TT_ZDJZX drop COLUMN JZD_Y" CNN1.Execute "alter table TT_ZDJZX drop COLUMN ZD_AREA"
方法:工程->引用->Microsoft ActiveX Data Object 2.7 Library
dim rs As New ADODB.Recordset
dim cn As New ADODB.Connection
'建立与数据库的连接
cn.ConnectionString = "provider=sqloledb;server=服务器;uid=用户名;pwd=密码;database=数据库"
cn.Open与ACCESS的连接
1、带密码的(Access2000)
dim rs As New ADODB.Recordset
dim cn As New ADODB.Connection cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;user id=admin;jet oledb:database password=密码;data source=数据库"2.不带密码
dim rs as new adodb.recordset
dim cn as new adodb.connection
cn.connectionString="provide=microsoft.jet.oledb.4.0;data source=数据库"
cn.Open
strSQL="driver={sql server};database=DBNAME;pwd=***;uid=admin"
Cn As New ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rcount As Integer
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\csy.mdb"
Cn.Open
sqlserver:
Cn.ConnectionString = "provider=sqloledb;database=XF_MANAGEMENT;uid=" & SQL_user & ";pwd=" & SQL_pwd & ";server=" & ServerName"
access数据库:
Cn As New ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rcount As Integer
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\csy.mdb"
Cn.Open
sqlserver:
Cn.ConnectionString = "provider=sqloledb;database=XF_MANAGEMENT;uid=" & SQL_user & ";pwd=" & SQL_pwd & ";server=" & ServerName"
访问表内数据
dim rs as new adodb.r...(记录集)
rs.open后的参数意义sql 为具体查询语句
RS.OPEN SQL,CONN,A,B
A: ADOPENFORWARDONLY(=0) 只读,且当前数据记录只能向下移动
ADOPENSTATIC(=3) 只读,当前数据记录可自由移动
ADOPENKEYSET(=1) 可读写,当前数据记录可自由移动
ADOPENDYNAMIC(=2) 可读写,当前数据记录可自由移动,可看到新增记录
B: ADLOCKREADONLY(=1) 默认值,用来打开只读记录
ADLOCKPESSIMISTIC(=2) 悲观锁定
ADLOCKOPTIMISTIC(=3) 乐观锁定
ADLOCKBATCHOPTIMISTIC(=4) 批次乐观锁定
Dim cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Combo1_Click()
rs.Open "SELECT * FROM TABLENAME WHERE NAME = '" & txtName.Text & "'", cn, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
Label1.Caption = rs!ID
Label2.Caption = rs!Memo
End If
End SubPrivate Sub Form_Load()
cn.ConnectionString = ""
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End Sub
Private Sub OPDB_Click()
With cd1
.FileName = ""
.Flags = 2050
.Filter = "Access DataBase(*.MDB)|*.MDB"
.DialogTitle = "Open DataBase As(*.MDB)"
.ShowOpen
Openfilename = .FileName
Openfiletitle = .FileTitle
End With
If Openfilename = "" Then
'MsgBox "You had cancel the option of open DataBase"
Exit Sub
End If
Dim infound As Integer
infound = InStr(Openfiletitle, ".")
Openfiletitle = Left(Openfiletitle, infound - 1)
infound = InStr(Openfilename, cd1.FileTitle)
path = Left(Openfilename, infound - 1)
'Debug.Print path
Me.Caption = path & "…" & Openfiletitle If bOpenconn = True Then
CNN1.Close
Set CNN1 = Nothing
End If CNN1.CursorLocation = adUseClient
CNN1.Open "provider=microsoft.jet.oledb.4.0;data source= " & Openfilename
PUTVALUE.Enabled = True
Putout.Enabled = True
ADDTEMP.Enabled = True
bOpenconn = True
Sb.Panels.Item(1).Text = "数据库已打开"
End Sub
Private Sub ADDTEMP_Click()
Screen.MousePointer = vbHourglass
Sb.Panels.Item(1).Text = "增加空间属性"
On Error Resume Next
CNN1.Execute "alter table ST_ZD add COLUMN TEMP_ZD string(15);"
Dim RS5 As ADODB.Recordset
Dim RS6 As ADODB.Recordset
Dim Zdtemp As String Set RS5 = New ADODB.Recordset
RS5.CursorType = adOpenDynamic
RS5.LockType = adLockBatchOptimistic
RS5.Open "SELECT * FROM ST_ZD ORDER BY OBJECTID", CNN1
Set RS6 = New ADODB.Recordset
RS6.CursorType = adOpenDynamic
RS6.LockType = adLockBatchOptimistic
Do While Not RS5.EOF
Zdtemp = FindFieldValue(RS6, "TEMP_ZD", "OBJECT_ID", "TT_ZD", RS5!OBJECTID)
RS5!TEMP_ZD = Zdtemp RS5.MoveNext
Loop
RS5.UpdateBatch
RS5.Close
Set RS5 = Nothing
Set RS6 = Nothing
Screen.MousePointer = vbDefault
Sb.Panels.Item(1).Text = "此过程已完成"
CNN1.Execute "alter table TT_ZDJZX drop COLUMN TEMP_ZD"
CNN1.Execute "alter table TT_ZDJZX drop COLUMN JZD_X"
CNN1.Execute "alter table TT_ZDJZX drop COLUMN JZD_Y"
CNN1.Execute "alter table TT_ZDJZX drop COLUMN ZD_AREA"
End Sub