How do I create an empty MDB from ADO

Question

I need to create an empty .mdb file, so that I can then run ADO commands on it (not ADO.NET). Is there a way to create an empty mdb using ADO?

Answer 1

Here are some code snippets that work:

        string sADOProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

        ADOX.CatalogClass cat = new ADOX.CatalogClass();

        string sCreate = MainForm.sADOProvider + sFullPath;

        cat.Create(sCreate);

        // The point of this code is to unlock the access file after we
        // create it.   You can tell it is unlocked if the .ldb file disappears.
        System.Runtime.InteropServices.Marshal.ReleaseComObject(cat);
        cat = null;
        GC.Collect();

Answer 2

Not sure about creating it directly via ADO, but if Access is installed on the machine you could use Access to create the file via COM.

below is an early and late bound example. Both methods have their advantages / disadvantages.

Option Explicit

Sub CreateMDBEarlyBound()
  '' Remember to set your reference to "Microsoft Access XX.0 Object Library"
  Dim acApp As Access.Application

  Set acApp = New Access.Application
  acApp.NewCurrentDatabase ("c:\temp\MyDB-early.mdb")

  Set acApp = Nothing

End Sub


Sub CreateMDBLateBound()

  Dim acApp As Object

  On Error Resume Next
    Set acApp = GetObject(, "Access.Application")
  On Error GoTo 0 '' turn off the resume next

  If acApp Is Nothing Then
    Set acApp = CreateObject("Access.Application")
  End If

  acApp.NewCurrentDatabase "c:\temp\MyDB-late.mdb"
  Set acApp = Nothing


End Sub

Answer 3

In case "not ADO.NET" implies "not .NET", here's Corey Trager's code re-written as VBA:

  Const sADOProvider As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

  Const sFullPath As String = "C:\DeleteMe.mdb"

  Dim cat As ADOX.Catalog
  Set cat = New ADOX.Catalog

  Dim sCreate As String
  sCreate = sADOProvider & sFullPath

  cat.Create sCreate

  ' The point of this code is to unlock the access file after we
  ' create it.   You can tell it is unlocked if the .ldb file disappears.
  Set cat.ActiveConnection = Nothing