SQL Server ODBC Standard Security: "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;" Trusted connection: "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;" Prompt for username and password: oConn.Properties("Prompt") = adPromptAlways oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;" OLEDB, OleDbConnection (.NET) Standard Security: "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" Trusted Connection: "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" (use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000) Prompt for username and password: oConn.Provider = "sqloledb" oConn.Properties("Prompt") = adPromptAlways oConn.Open "Data Source=Aron1;Initial Catalog=pubs;" Connect via an IP address: "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;" (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset'在MSHFLEXGRID表格控件中显示查询到的内容 Private Sub Command1_Click() If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM USERPASSWORD", cn, adOpenDynamic, adLockOptimistic Set MSHFlexGrid1.DataSource = rs End Sub'向数据库中添加数据 Private Sub Command2_Click() If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM USERPASSWORD", cn, adOpenDynamic, adLockOptimistic rs.AddNew rs!UID = txtUid.Text rs!PWD = txtPwd.Text rs!TRUENAME = txtName.Text rs!CREATEDATE = Format(Now, "YYYY/MM/DD") rs.Update
Command1.Value = True End Sub'从数据库中删除数据 Private Sub Command3_Click() cn.Execute "DELETE FROM USERPASSWORD WHERE UID = '" & txtUid.Text & "'" Command1.Value = True End SubPrivate Sub Form_Load() cn.ConnectionString = "DBQ=" & App.Path & "\TelePhone.mdb;DefaultDir=" & _ App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _ "DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _ "MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _ "Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=admind1234;" cn.Open '查询字符串可以上这里查 'http://www.connectionstrings.com/ End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
Set dbs = DBEngine.Workspaces(0).OpenDatabase(DatabasePath, False, False) On Error Resume Next Set tdfNew = dbs.TableDefs(TableName & "Outputtemp") If Err.Number <> 0 Then Set tdfNew = dbs.CreateTableDef(TableName & "Outputtemp") tdfNew.Fields.Append tdfNew.CreateField(Col1, dbText) tdfNew.Fields.Append tdfNew.CreateField(Col2, dbText) tdfNew.Fields.Append tdfNew.CreateField(Col3, dbText) tdfNew.Fields.Append tdfNew.CreateField(Col4, dbText) dbs.TableDefs.Append tdfNew End If On Error GoTo 0
dim rs as new adodb.recordset dim sql as string sql="select * from table" rs.open sql,conn,1,2 rs(记录集)返回查询结果
ODBC
Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
OLEDB, OleDbConnection (.NET)
Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Prompt for username and password:
oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
Connect via an IP address:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset'在MSHFLEXGRID表格控件中显示查询到的内容
Private Sub Command1_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM USERPASSWORD", cn, adOpenDynamic, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
End Sub'向数据库中添加数据
Private Sub Command2_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM USERPASSWORD", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!UID = txtUid.Text
rs!PWD = txtPwd.Text
rs!TRUENAME = txtName.Text
rs!CREATEDATE = Format(Now, "YYYY/MM/DD")
rs.Update
Command1.Value = True
End Sub'从数据库中删除数据
Private Sub Command3_Click()
cn.Execute "DELETE FROM USERPASSWORD WHERE UID = '" & txtUid.Text & "'"
Command1.Value = True
End SubPrivate Sub Form_Load()
cn.ConnectionString = "DBQ=" & App.Path & "\TelePhone.mdb;DefaultDir=" & _
App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _
"DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _
"MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _
"Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=admind1234;"
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
On Error Resume Next
Set tdfNew = dbs.TableDefs(TableName & "Outputtemp")
If Err.Number <> 0 Then
Set tdfNew = dbs.CreateTableDef(TableName & "Outputtemp")
tdfNew.Fields.Append tdfNew.CreateField(Col1, dbText)
tdfNew.Fields.Append tdfNew.CreateField(Col2, dbText)
tdfNew.Fields.Append tdfNew.CreateField(Col3, dbText)
tdfNew.Fields.Append tdfNew.CreateField(Col4, dbText)
dbs.TableDefs.Append tdfNew
End If
On Error GoTo 0
dim rs as new adodb.recordset
dim sql as string
sql="select * from table"
rs.open sql,conn,1,2
rs(记录集)返回查询结果