'Dim adoconnection As New ADODB.Connection

'With adoconnection
'    .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC"
'    .Open
'If .State = adStateOpen Then
'    MsgBox "connection to database successful"
'Else
'    MsgBox "connection to database successful"
'End If
'End With

With MADOCONNECTION
    .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .Open
End With

With adors
    .ActiveConnection = MADOCONNECTION
    .Source = "SELECT * FROM library_books"
    .Open
    
    End With
    MsgBox "dfasfaS"


/////數(shù)據(jù)庫的連接2
Dim adors As New ADODB.Recordset
Dim adocommand As New ADODB.Command

With adocommand
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .CommandText = "all_library_books"
    .CommandType = adCmdStoredProc
End With

With adors
    
    .Open adocommand
    
    End With
    MsgBox "dfasfaS"
    
/////切斷記錄集
Dim adors As New ADODB.Recordset
Dim adocommand As New ADODB.Command


With adors
    
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .CursorType = adOpenStatic
    .Source = "SELECT * FROM library_books"
    .Open
    
    Set .ActiveConnection = Nothing
    .AddNew
    .Fields("isbn").Value = "TP789"
    .Fields("title").Value = "國家地理"
    .Fields("author").Value = "lijia"
    
    
   
    
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .UpdateBatch
    End With
    MsgBox "dfasfaS"

End Sub

Private Sub Command2_Click()
Dim adofield As ADODB.Field
Dim adors As New ADODB.Recordset

With adors
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .LockType = adLockOptimistic
    .CursorType = adOpenStatic
    .Source = "[all]"
    .Open
    If .State = adStateOpen Then
    MsgBox "dsfafasdf"
    End If
 End With
 /////讀數(shù)據(jù)
Dim adors As New ADODB.Recordset
Dim adocommand As New ADODB.Command


With adors
    
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    
    .Source = "SELECT * FROM library_books"
    .Open
    
End With

While adors.Fields.Count > 0
    If (Not adors.BOF) And (Not adors.EOF) Then
        Debug.Print adors.Fields("isbn")
    End If
 
    
    
    
  Set adors = adors.NextRecordset
Wend

While (Not .BOF) And (Not .EOF)
        For Each Field In .Fields
            Debug.Print Field.Name & " " & Field.Value
        Next Field
        .MoveNext
    Wend
////////
Dim adors As New ADODB.Recordset
Dim ADOFIELD As ADODB.Field



With adors
    
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Source = "SELECT * FROM library_books"
    .Open
    .AddNew
    If .State = adStateOpen Then
        .MoveLast
        .Fields("isbn") = "X123"
        savechanges = MsgBox("Do you want to save your change?", vbQuestion + vbYesNo)
        If savechanges = vbYes Then
            .Update
        Else
            .CancelUpdate
        End If
        
      While (Not .BOF) And (Not .EOF)
        For Each Field In .Fields
            Debug.Print Field.Name & " " & Field.Value
        Next Field
        .MoveNext
    Wend
        .CancelBatch
        .AddNew
        While (Not .BOF) And (Not .EOF)
        For Each Field In .Fields
            Debug.Print Field.Name & " " & Field.Value
        Next Field
        .MoveNext
    Wend
    End If
        
        
  
End With

//////
.AddNew
    If .State = adStateOpen Then
        .MoveLast
        .Fields("isbn") = "X123"
        savechanges = MsgBox("Do you want to save your change?", vbQuestion + vbYesNo)
        If savechanges = vbYes Then
            .Update
        Else
            .CancelUpdate
        End If
        
      While (Not .BOF) And (Not .EOF)
        For Each Field In .Fields
            Debug.Print Field.Name & " " & Field.Value
        Next Field
        .MoveNext
    Wend
        .CancelBatch
        .AddNew
        While (Not .BOF) And (Not .EOF)
        For Each Field In .Fields
            Debug.Print Field.Name & " " & Field.Value
        Next Field
        .MoveNext
    Wend
    End If
/////Unique table屬性
Private Sub Command3_Click()
Dim adors As New ADODB.Recordset
Dim ADOFIELD As ADODB.Field



With adors
    
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Source = "SELECT s.isbn,s.title ,o.stuname FROM library_books s,(select * from student_books) as o"
    .Properties("Unique Table") = "library_books"
    
    .Open
    Set .ActiveConnection = Nothing
    
    .MoveLast
    .Fields(0).Value = "TT"
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .UpdateBatch
        
        
  MsgBox "DFSAF"
End With

End Sub
/////
With adocommand
    .ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
    .CommandText = "GetSuppliers"
    .CommandType = adCmdStoredProc
End With

With adors
    adors.CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   
   .Open adocommand
   
   Set .ActiveConnection = Nothing
   MsgBox "DFasdf"
End With

Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=大作業(yè);Data Source=KASUMIPC;"
Adodc1.CursorLocation = adUseClient
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = "select *  from library_books"
Adodc1.Refresh