有没有办法在VBA中更改DAO.Recordset字段的类型?
出于某种原因,解释器将记录集字段评估为Type 8(dateTime),但我需要该字段为Text,因为它应该是
我尝试了以下内容:
rs.Fields(0).Type = 10
但这使我无法在此集合中找到项目错误。
有什么方法可以改变DAO.Recordset中特定字段的类型吗?
第1版:以下是到目前为止我得到的
Private Sub doMagic()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sql as String: sql = "SELECT * FROM [Sheet1$] WHERE [Column1] IS NOT NULL"
Set db = OpenDatabase(ThisWorkbook.FullName, False, True, "Excel 8.0; HDR=Yes; IMEX=1;"
set rs = db.OpenRecordset(sql)
Dim updateSQL As String: updateSQL = "ALTER TABLE [Sheet1$] ALTER COLUMN [Column5] TEXT(100)"
' I tried following, non of them working:
' db.RunSql(updateSQL) - error: Metod or data member not found
' db.Execute(updateSQL) - fails with error: Operation is not supported for this type of object.
' DoCmd.RunSql(updateSQL)- error: Object required
End Sub
不能用DAO更改表字段类型。创建DAO记录集时,字段的定义为只读。要更改字段类型,您需要使用DDL(数据定义语言)查询,这就是链接对SQL的解释,这也是您通常会做的
DoCmd.RunSQL "ALTER TABLE yourTableName ALTER COLUMN yourColumnName TEXT(50)"