Create Sql Server CE database from VBA

Question

1.00/5 (1 vote)

See more:

Accessing a SQL Server CE database from VBA is not a problem:

VB

Set objConn = New ADODB.Connection
objConn.ConnectionString = _
    "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=D:\testDB.sdf"
objConn.Open

There is an example of creating a new SQL CE database using C# here.[^]

Is it possible to create a new SQL Server CE database from VBA?

Posted 28-Aug-10 5:51am

Uros Calakovic

Updated 25-Apr-17 7:51am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Lantei · Answer 1 · 2010-08-30T03:25:00

Solution 1

If you want to create a database from VBA then you would need to do that in a stored procedure and implement it in VBA through ADO.

SQL

create procedure _procedureName
as
begin
create table _newTable
(
 [<field_names> <datatypes>]
)
end
</datatypes></field_names>

Posted 30-Aug-10 3:25am

Lantei

Updated 30-Aug-10 3:26am

v2

Comments

Uros Calakovic 30-Aug-10 9:39am

Thank you for the comment. I am aware of the possibility to use SQL DDL commands to create a database from VBA and I would do that if this was a 'regular' SQL Server. But, in SQL Server CE database is file and file is database (I figure), and this is I am having problems with: how to connect to SQL Server CE without an existing .sdf file? If this was possible, I could then go on and try to use SQL to create one.

Uros Calakovic · Answer 2 · 2010-09-09T08:14:00

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:

VB

Option Explicit

'Reference to Microsoft ActiveX Data Objects x.x Library.
'Reference to Microsoft ADO Ext.x.x for DDL and Security

Sub Create_SSCE_Database()

'Name of the SSCE database.
Const C_stDBName = "XLDennis.sdf"

'Path to the SSCE database.
Const C_stPath = "C:\Users\Dennis Wallentin\Documents\SSCE\"

'Objects to create database, table and fields.
Dim xCat As ADOX.Catalog
Dim xTable As ADOX.Table
Dim xCol As ADOX.Column

Dim stConnection As String

'Connection 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

'In case we have an already created database.
On Error Resume Next
Kill C_stPath & C_stDBName
On Error GoTo 0

'Create the empty database.
xCat.Create stConnection

'All the properties of the connection string is printed to
'the Immediate Window.
Debug.Print xCat.ActiveConnection

'Create a table with some fields.
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

'Add the created table to the database.
xCat.Tables.Append xTable

'Cleaning up.
Set xCol = Nothing: Set xTable = Nothing: Set xCat = Nothing

End Sub