ADO动态创建mdb的数据表失败有关问题

ADO动态创建mdb的数据表失败问题
ADOX的相关引用都添加了。我使用如下代码，动态创建了一个新的mdb数据库，在此基础上想动态添加一个新表（家庭开支），却失败了，查了网上提供的资源，仍没有发现问题，请大虾指点，谢谢先！

VB code


Private Sub Command1_Click()
 Dim TblNew As New Table
 With TblNew
   .Name = "家庭开支"
   .Columns.Append "日期", adDate
   .Columns.Append "收入或支出", adVarChar, 4
   .Columns.Append "家庭成员", adVarChar, 20
   .Columns.Append "费用", adSingle
   .Columns.Append "备注", adChar
 End With
 If Creat_Mdb_Table(App.Path & "\nxx.mdb", TblNew) = False Then Msgbox "创建新表失败！"
 Set TblNew = Nothing
End Sub

Private Function Creat_Mdb_Table(cMdbFile As String, tbl As Table) As Boolean
On Error GoTo NoTable
  Dim MyCat As New ADOx.Catalog, pStr As String
  Dim i As Long
  Creat_Mdb_Table = True
  pStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cMdbFile
  
  If FileCheck(cMdbFile) = False Then MyCat.Create pStr '创建新的数据库
  
  MyCat.ActiveConnection = pStr
  MyCat.Tables.Append tbl'建立数据表<---运行到这里出错跳转退出
  Set MyCat = Nothing
  Exit Function
  
NoTable:
  Creat_Mdb_Table = False
  Set MyCat = Nothing
End Function

------解决方案--------------------

VB code


Option Explicit
Dim db As Database
Dim strSQL As String
Dim cnnMDB As ADODB.Connection
Private Sub Form_Load()
    Set db = DBEngine.Workspaces(0).CreateDatabase(App.Path & "\Event.mdb", dbLangGeneral _
             & ";pwd=ksl")
    Set db = Nothing
    strSQL = "CREATE TABLE TB_Event" & "([FD_NO] LONG,[FD_Date] TEXT(50),[FD_UserName] TEXT(50)," _
             & "[FD_EventType] TEXT(255),[FD_Type] TEXT(10))"
    Set cnnMDB = New ADODB.Connection
    cnnMDB.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnnMDB.Open "Data Source = " & App.Path & "\Event.mdb;jet oledb:database password=ksl"
    cnnMDB.Execute strSQL
    Set cnnMDB = Nothing
End Sub

------解决方案--------------------
sql应该一样

------解决方案--------------------

是否是数据类型数据库不接受，试试改变数据类型：
Private Sub Command1_Click()
  Dim TblNew As New Table
  With TblNew
  .Name = "家庭开支"
  .Columns.Append "日期",7
  .Columns.Append "收入或支出", 202, 4
  .Columns.Append "家庭成员", 202, 20
  .Columns.Append "费用", adSingle
  .Columns.Append "备注", 203  '似乎要提供字段长度，如果测试不行，加个长度试试
  End With

ADO动态创建mdb的数据表失败有关问题

相关推荐