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

Creating a New Microsoft Access Database Using ADO

 
" 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

Deleting Records via ADO

 
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

Refreshing a Link

 
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

Using the Open Database Method

 
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