1,216
社区成员
发帖
与我相关
我的任务
分享关于在ACCESS数据库中创建新表！
我用如下方法创建新表
Public oCnn As ADODB.Connection
Dim strLink as String
Dim strSQL as String
strLink = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SpectrumDataBase.mdb"
Set oCnn = New ADODB.Connection
oCnn.Open (strLink)
strSQL = "creat table " & strName & " (wavelength integer,reflectivity single) " 'on [primary]"
oCnn.Execute strSQL
编译错误提示说：
无效的SQL语句；期待DELETE，INSERT，PROCEDURE，SELECT 或 UPDATE
请问问题出在什么地方，该如何解决？！
还有没有其他方面在程序中动态的创建新表
谢谢了
Public oCnn As ADODB.Connection
Dim strLink as String
Dim strSQL as String
strLink = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SpectrumDataBase.mdb"
Set oCnn = New ADODB.Connection
oCnn.Open (strLink)
strSQL = "creat table " & strName & " (wavelength integer,reflectivity single) " 'on [primary]"
oCnn.Execute strSQL
编译错误提示说：
无效的SQL语句；期待DELETE，INSERT，PROCEDURE，SELECT 或 UPDATE
请问问题出在什么地方，该如何解决？！
还有没有其他方面在程序中动态的创建新表
谢谢了
...全文
请发表友善的回复…
发表回复
lihonggen0 2003-10-12
- 打赏
- 举报
引用 Microsoft ADO Ext. 2.7 for DDL and Security
The following code shows how to create a new Microsoft Jet database with the Create method.
Attribute VB_Name = "Create"
Option Explicit
' BeginCreateDatabseVB
Sub CreateDatabase()
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"
End Sub
' EndCreateDatabaseVB
The following code demonstrates how to create a new table.
' BeginCreateTableVB
Sub CreateTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog
'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl
End Sub
The following code shows how to create a new Microsoft Jet database with the Create method.
Attribute VB_Name = "Create"
Option Explicit
' BeginCreateDatabseVB
Sub CreateDatabase()
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"
End Sub
' EndCreateDatabaseVB
The following code demonstrates how to create a new table.
' BeginCreateTableVB
Sub CreateTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog
'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl
End Sub
上官云峰 2003-10-12
- 打赏
- 举报
和楼上的一样的方法。
changechange 2003-10-12
- 打赏
- 举报
你得语句更本写错了
strSQL = "creat table " & strName & " (wavelength integer,reflectivity single) " 'on [primary]"
注意：是create不是creat，另外strname必须保证不是保留字
请参考
http://access911.net/index.asp?board=4&recordid=74FAB61E16DC
http://access911.net/index.asp?board=4&recordid=71FAB01E15DC
strSQL = "creat table " & strName & " (wavelength integer,reflectivity single) " 'on [primary]"
注意：是create不是creat，另外strname必须保证不是保留字
请参考
http://access911.net/index.asp?board=4&recordid=74FAB61E16DC
http://access911.net/index.asp?board=4&recordid=71FAB01E15DC
northwolves 2003-10-11
- 打赏
- 举报
Sub createtable() '创建数据库的表
On Error Resume Next
Dim mycat As New ADOX.Catalog
Dim mytable As New ADOX.Table
mycat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SpectrumDataBase.mdb"
For i = 1 To 9
mytable.Name = "表" & i
mytable.Columns.Append "字段1", adDate
mytable.Columns.Append "字段2", adInteger
mytable.Columns.Append "字段3", adBoolean
mytable.Columns.Append "字段4", adVarChar
mycat.Tables.Append mytable
Set mytable = Nothing
Next
MsgBox "创建 表1----表9 成功！"
Set mycat.ActiveConnection = Nothing
End Sub
On Error Resume Next
Dim mycat As New ADOX.Catalog
Dim mytable As New ADOX.Table
mycat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SpectrumDataBase.mdb"
For i = 1 To 9
mytable.Name = "表" & i
mytable.Columns.Append "字段1", adDate
mytable.Columns.Append "字段2", adInteger
mytable.Columns.Append "字段3", adBoolean
mytable.Columns.Append "字段4", adVarChar
mycat.Tables.Append mytable
Set mytable = Nothing
Next
MsgBox "创建 表1----表9 成功！"
Set mycat.ActiveConnection = Nothing
End Sub
