VBA/Excel/Access/Word/Access/Access ADO
Содержание
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>