看里面这一段程序:
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields(i).Name & " " &
adoRecordset.Fields(i).Value
Next i
'操作的是里面CODE1.XLS文件
为什么打印出的字段名和我用EXCEL打开CODE1看到的字段名不一样,EXCEL打开后看到的
有十几个字段,打印出来的却只是十个字段,不知这些字段名藏在CODE1文件的哪个地方?
这个文件是我们厂数据库所有备品备件记录把它存成XLS文件的,请帮我看一下,谢谢!还有这个CODE1文件其实有15000条记录,这个程序在厂里面电脑运行到
adoRecordset.Open SQL, adoConnection, adOpenKeyset, adLockOptimistic这一句会
提示"系统资源不足",但在我的笔记本上就不会?有没有办法解决?
Public adoConnection As New ADODB.Connection
Public adoRecordset As New ADODB.Recordset
Public FilePath As String
Public FileName As String
Public SQL As StringPrivate Sub Command1_Click()
Dim tempSQL As String
Dim SheetName As String
SheetName = FileName
SQL = "select * from" & " [" & SheetName & "$]"
tempSQL = SQL
If Text2.Text <> "%%" Then
SQL = SQL & " where [partid] like '" & Text2.Text & "'"
End If
If Text3.Text <> "%%" Then
If SQL <> tempSQL Then
SQL = SQL & " and [name1] like '" & Text3.Text & "'"
Else
SQL = SQL & " where [name1] like '" & Text3.Text & "'"
End If
End If
If Text11.Text <> "%%" Then
If SQL <> tempSQL Then
SQL = SQL & " and [model] like '" & Text11.Text & "'"
Else
SQL = SQL & " where [model] like '" & Text11.Text & "'"
End If
End If
Debug.Print SQL
adoRecordset.Open SQL, adoConnection, adOpenKeyset, adLockOptimistic
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields(i).Name & " " & adoRecordset.Fields(i).Value
Next i
'Debug.Print adoRecordset.Fields("name").ValueIf adoRecordset.EOF Then
MsgBox "找不到相关记录"
adoRecordset.Close
Set adoRecordset = Nothing
Else
Form2.Show vbModal
End If
End SubPrivate Sub Command2_Click()
Unload Me
End SubPrivate Sub Form_Load()
If FilePath = "" Then
selid = MsgBox("请选择要查询的EXCEL文件位置", vbOKCancel, "请选择")
If selid = vbOK Then
CommonDialog1.Filter = "EXCEL File(*.xls)|*.xls"
CommonDialog1.ShowOpen
FilePath = CommonDialog1.FileName
tempstr = InStr(FilePath, ".")
For i = Len(FilePath) To 0 Step -1
If Mid(FilePath, i, 1) = "\" Then Exit For
Next i
FileName = Mid(FilePath, i + 1, tempstr - i - 1)
Else
Exit Sub
End If
End If
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & FilePath & ";Extended Properties='Excel 8.0;HDR=Yes'"End Sub
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields(i).Name & " " &
adoRecordset.Fields(i).Value
Next i
'操作的是里面CODE1.XLS文件
为什么打印出的字段名和我用EXCEL打开CODE1看到的字段名不一样,EXCEL打开后看到的
有十几个字段,打印出来的却只是十个字段,不知这些字段名藏在CODE1文件的哪个地方?
这个文件是我们厂数据库所有备品备件记录把它存成XLS文件的,请帮我看一下,谢谢!还有这个CODE1文件其实有15000条记录,这个程序在厂里面电脑运行到
adoRecordset.Open SQL, adoConnection, adOpenKeyset, adLockOptimistic这一句会
提示"系统资源不足",但在我的笔记本上就不会?有没有办法解决?
Public adoConnection As New ADODB.Connection
Public adoRecordset As New ADODB.Recordset
Public FilePath As String
Public FileName As String
Public SQL As StringPrivate Sub Command1_Click()
Dim tempSQL As String
Dim SheetName As String
SheetName = FileName
SQL = "select * from" & " [" & SheetName & "$]"
tempSQL = SQL
If Text2.Text <> "%%" Then
SQL = SQL & " where [partid] like '" & Text2.Text & "'"
End If
If Text3.Text <> "%%" Then
If SQL <> tempSQL Then
SQL = SQL & " and [name1] like '" & Text3.Text & "'"
Else
SQL = SQL & " where [name1] like '" & Text3.Text & "'"
End If
End If
If Text11.Text <> "%%" Then
If SQL <> tempSQL Then
SQL = SQL & " and [model] like '" & Text11.Text & "'"
Else
SQL = SQL & " where [model] like '" & Text11.Text & "'"
End If
End If
Debug.Print SQL
adoRecordset.Open SQL, adoConnection, adOpenKeyset, adLockOptimistic
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields(i).Name & " " & adoRecordset.Fields(i).Value
Next i
'Debug.Print adoRecordset.Fields("name").ValueIf adoRecordset.EOF Then
MsgBox "找不到相关记录"
adoRecordset.Close
Set adoRecordset = Nothing
Else
Form2.Show vbModal
End If
End SubPrivate Sub Command2_Click()
Unload Me
End SubPrivate Sub Form_Load()
If FilePath = "" Then
selid = MsgBox("请选择要查询的EXCEL文件位置", vbOKCancel, "请选择")
If selid = vbOK Then
CommonDialog1.Filter = "EXCEL File(*.xls)|*.xls"
CommonDialog1.ShowOpen
FilePath = CommonDialog1.FileName
tempstr = InStr(FilePath, ".")
For i = Len(FilePath) To 0 Step -1
If Mid(FilePath, i, 1) = "\" Then Exit For
Next i
FileName = Mid(FilePath, i + 1, tempstr - i - 1)
Else
Exit Sub
End If
End If
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & FilePath & ";Extended Properties='Excel 8.0;HDR=Yes'"End Sub
改为英文试试看
你是以adOpenKeyset键集方式打开的,当然非常消耗资源。
可以考虑改为:
adoRecordset.Open SQL, adoConnection, adOpenDynamic, adLockOptimistic
试过了也不行啊,还有没有更不耗资源的参数,我只是查看不编辑的