我写了一个,请各位老师指正。感觉对数据类型的处理(红色部分),仍有问题。
'rs 数据集
'TableName 表名
'PRIMARY_KEY 首字段设置为“主键”
Sub RSnewTable(rs As ADODB.Recordset, TableName As String, Optional PRIMARY_KEY As Boolean = False)
If Not rs.State = 1 Then
MsgBox "RS 数据集 已经关闭!"
Exit Sub
End If
Call DelTable1(TableName) '检查原有表是否存在,存在即删除
Dim SQLstr As String, i As Long, Maxi As Long, Fid As String, Fdtyp As String, FdLen As Long
Maxi = rs.Fields.Count
SQLstr = ""
For i = 0 To Maxi - 1
Fid = "[" & rs.Fields(i).name & "] " '字段名
Fdtyp = FieldType(rs.Fields(i).Type) '读数据类型
FdLen = rs.Fields.Item(i).DefinedSize '字段长度
If Fdtyp = "CHAR" Or Fdtyp = "NCHAR" Or Fdtyp = "VARCHAR" Or Fdtyp = "NVARCHAR" Then '“文本”类型处理
If (FdLen > 255) Then
Fdtyp = "Memo" '更改为“备注”
Else
Fdtyp = "CHAR(" & FdLen & ")" '更改为 定长“文本”
End If
End If
If Fdtyp = "NTEXT" Or Fdtyp = "TEXT" Then Fdtyp = "Memo" '更改为“备注”
If PRIMARY_KEY And i = 0 Then '指定 首字段 为“主键”
Fid = Fid & Fdtyp & " PRIMARY KEY "
Else
Fid = Fid & Fdtyp & " null"
End If
SQLstr = SQLstr & "," & Fid '连接
Next i
If Mid(SQLstr, 1, 1) = "," Then SQLstr = Mid(SQLstr, 2)
SQLstr = "CREATE TABLE [" & TableName & "](" & SQLstr & ")" '装入
Debug.Print SQLstr
DoCmd.RunSQL SQLstr
End Sub