To answer the question, Dennis Wallentin has provided VBA code to create a Sql Server CE database
here[
^]. Copy of the code for future reference:
Option Explicit
Sub Create_SSCE_Database()
Const C_stDBName = "XLDennis.sdf"
Const C_stPath = "C:\Users\Dennis Wallentin\Documents\SSCE\"
Dim xCat As ADOX.Catalog
Dim xTable As ADOX.Table
Dim xCol As ADOX.Column
Dim stConnection As String
stConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
"Data Source=" & C_stPath & C_stDBName & ";" & _
"Persist Security Info=False;"
Set xCat = New ADOX.Catalog
Set xTable = New ADOX.Table
On Error Resume Next
Kill C_stPath & C_stDBName
On Error GoTo 0
xCat.Create stConnection
Debug.Print xCat.ActiveConnection
With xTable
.Name = "ReportedData"
.Columns.Append "Department", adVarWChar, 10
.Columns.Append "Quater", adVarWChar, 6
.Columns.Append "Budget", adSmallInt, 4
.Columns.Append "Result", adSmallInt, 4
End With
xCat.Tables.Append xTable
Set xCol = Nothing: Set xTable = Nothing: Set xCat = Nothing
End Sub