从VBA创建Sql Server CE数据库 [英] Create Sql Server CE database from VBA

问题描述

从VBA访问SQL Server CE数据库不是问题：

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

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

有一个使用C＃ here。 [ ^ ]



是否可以从VBA创建新的SQL Server CE数据库？

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?

推荐答案

如果要从VBA创建数据库，则需要在VBA中创建数据库商店d程序并通过ADO在VBA中实现它。

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.

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

为了回答这个问题，Dennis Wallentin提供了VBA代码来创建一个Sql Server CE数据库这里 [ ^ ]。代码复制供将来参考：

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

'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

这篇关于从VBA创建Sql Server CE数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！