VBA/Excel/Access/Word/Access/Access ADO

Материал из VB Эксперт
Перейти к: навигация, поиск

Creating a New Microsoft Access Database Using ADO

   <source lang="vb">

" you must make sure that a reference to " Microsoft ADO Ext. 2.5 for DDL and Security " Object Library is set in the References dialog box Sub CreateI_NewDatabase()

  Dim cat As ADOX.Catalog
  Dim strDb As String
  Set cat = New ADOX.Catalog
  strDb = "C:\NewAccessDb.mdb"
  On Error GoTo ErrorHandler
  cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDb
  MsgBox "The database was created (" & strDb & ")."
  Set cat = Nothing
  Exit Sub

ErrorHandler:

  If Err.Number = -2147217897 Then
     Kill strDb
     Resume 0
  Else
     MsgBox Err.Number & ": " & Err.Description
  End If

End Sub

</source>
   
  


Deleting Records via ADO

   <source lang="vb">

Public Sub ADOWipeOutAttribute(RecID)

   " Establish a connection transfers.mdb
   MyConn = "C:\mydb.mdb"
   With New ADODB.Connection
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open MyConn
       .Execute "Delete From tblTransfer Where ID = " & RecID
       .Close
   End With

End Sub

</source>
   
  


Refreshing a Link

   <source lang="vb">

Sub RefreshLink()

   Dim cat As ADOX.Catalog
   Dim tdf As ADOX.Table
   Set cat = New ADOX.Catalog
   Set cat.ActiveConnection = CurrentProject.Connection
   Set tdf = cat.Tables("Employees")
   tdf.Properties("Jet OLEDB:Link Datasource") = _
       strNewLocation

End Sub

</source>
   
  


Using the Open Database Method

   <source lang="vb">

Sub OpenExternalSQL()

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Set cnn = New ADODB.Connection
   Set rst = New ADODB.Recordset
   cnn.Open "Provider=sqloledb;" & _
       "Data Source=(local);" & _
       "Initial Catalog=yourDb;" & _
       "User Id=sa;Password=; "
   Set rst = cnn.Execute("Select * from Employees")
   Do Until rst.EOF
       Debug.Print rst.Fields(0).Value
       rst.MoveNext
   Loop
   "Close the connection
   cnn.Close

End Sub

</source>