从VBA创建Sql Server CE数据库 [英] Create Sql Server CE database from VBA
本文介绍了从VBA创建Sql Server CE数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
从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屋!
查看全文