SQL 来自 Microsoft Access 的表创建 DDL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/172895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table Creation DDL from Microsoft Access
提问by Richard A
Is there any easy way to retrieve table creation DDL from Microsoft Access (2007) or do I have to code it myself using VBA to read the table structure?
有什么简单的方法可以从 Microsoft Access (2007) 中检索表创建 DDL 还是我必须使用 VBA 自己编写代码来读取表结构？
I have about 30 tables that we are porting to Oracle and it would make life easier if we could create the tables from the Access definitions.
我有大约 30 个表要移植到 Oracle，如果我们可以从 Access 定义中创建这些表，将会使生活更轻松。
回答by Richard A
Thanks for the other suggestions. While I was waiting I wrote some VBA code to do it. It's not perfect, but did the job for me.
感谢其他建议。在我等待的时候，我写了一些 VBA 代码来做到这一点。它并不完美，但为我完成了这项工作。
Option Compare Database
Public Function TableCreateDDL(TableDef As TableDef) As String
Dim fldDef As Field
Dim FieldIndex As Integer
Dim fldName As String, fldDataInfo As String
Dim DDL As String
Dim TableName As String
TableName = TableDef.Name
TableName = Replace(TableName, " ", "_")
DDL = "create table " & TableName & "(" & vbCrLf
With TableDef
For FieldIndex = 0 To .Fields.Count - 1
Set fldDef = .Fields(FieldIndex)
With fldDef
fldName = .Name
fldName = Replace(fldName, " ", "_")
Select Case .Type
Case dbBoolean
fldDataInfo = "nvarchar2"
Case dbByte
fldDataInfo = "number"
Case dbInteger
fldDataInfo = "number"
Case dbLong
fldDataInfo = "number"
Case dbCurrency
fldDataInfo = "number"
Case dbSingle
fldDataInfo = "number"
Case dbDouble
fldDataInfo = "number"
Case dbDate
fldDataInfo = "date"
Case dbText
fldDataInfo = "nvarchar2(" & Format$(.Size) & ")"
Case dbLongBinary
fldDataInfo = "****"
Case dbMemo
fldDataInfo = "****"
Case dbGUID
fldDataInfo = "nvarchar2(16)"
End Select
End With
If FieldIndex > 0 Then
DDL = DDL & ", " & vbCrLf
End If
DDL = DDL & " " & fldName & " " & fldDataInfo
Next FieldIndex
End With
DDL = DDL & ");"
TableCreateDDL = DDL
End Function
Sub ExportAllTableCreateDDL()
Dim lTbl As Long
Dim dBase As Database
Dim Handle As Integer
Set dBase = CurrentDb
Handle = FreeFile
Open "c:\export\TableCreateDDL.txt" For Output Access Write As #Handle
For lTbl = 0 To dBase.TableDefs.Count - 1
'If the table name is a temporary or system table then ignore it
If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
'~ indicates a temporary table
'MSYS indicates a system level table
Else
Print #Handle, TableCreateDDL(dBase.TableDefs(lTbl))
End If
Next lTbl
Close Handle
Set dBase = Nothing
End Sub
I never claimed to be VB programmer.
我从未声称自己是 VB 程序员。
回答by Corey Trager
I've done this:
我已经这样做了：
There's a tool for "upsizing" from Access to SQL Server. Do that, then use the excellent SQL Server tools to generate the script.
有一个用于从 Access 升级到 SQL Server 的工具。这样做，然后使用优秀的 SQL Server 工具生成脚本。
回答by Michael OShea
Use Oracle's SQL Developer Migration Workbench.
使用 Oracle 的 SQL Developer Migration Workbench。
There's a full tutorial on converting Access databases to Oracle available here. If its only the structures you're after, then you can concentrate on section 3.0.
此处提供了有关将 Access 数据库转换为 Oracle 的完整教程。如果它只是您所追求的结构，那么您可以专注于第 3.0 节。
回答by Gareth
You can use the export feature in Access to export tables to an ODBC data source. Set up an ODBC data source to the Oracle database and then right click the table in the Access "Tables" tab and choose export. ODBC is one of the "file formats" - it will then bring up the usual ODBC dialog.
您可以使用 Access 中的导出功能将表导出到 ODBC 数据源。将 ODBC 数据源设置为 Oracle 数据库，然后右键单击 Access“表”选项卡中的表并选择导出。ODBC 是一种“文件格式”——然后它会打开通常的 ODBC 对话框。
回答by skamradt
You might want to look into ADOX to get at the schema information. Using ADOX you can get things such as the keys, views, relations, etc.
您可能需要查看 ADOX 以获取架构信息。使用 ADOX，您可以获得诸如键、视图、关系等内容。
Unfortunately I am not a VB programmer, but there are plenty of examples on the web using ADOX to get at the table schema.
不幸的是，我不是 VB 程序员，但网上有很多使用 ADOX 获取表模式的示例。
回答by Stareagle
A bit late to the party, but I use RazorSQL to generate DDL for Access databases.
参加聚会有点晚，但我使用 RazorSQL 为 Access 数据库生成 DDL。
