Question

vba ACCESS （from子句语法错误）

Private Sub UserForm_Initialize()
Dim mydata110 As String, mytable As String
Dim cnn110 As New ADODB.Connection
Dim rs110 As New ADODB.Recordset
Dim cmd110 As ADODB.Command
Dim par110 As ADODB.Parameter
Dim i As Integer
Dim lmNo As String

mydata110 = "D:\作业\计算机辅助设计与制造\作业\0825110.mdb"
mytable110 = "螺母参数表"
Set cnn110 = New ADODB.Connection
With cnn110
.Provider = "microsoft.jet.oledb.4.0"
.Open mydata110
End With
lmNo = InputBox("请输入要查询的螺母b:", "螺母参数查询")
Set cmd110 = New ADODB.Command
cmd110.ActiveConnection = cnn110
cmd110.CommandText = "select * from mytable110" & "where 螺母b110 = ?"
Set par110 = cmd110.CreateParameter("螺母b110", adDouble, adParamInput, 5, lmNo)
cmd110.Parameters.Append par110
Set rs110 = New ADODB.Recordset
Set rs110 = cmd110.Execute()
If rs110.BOF And rs110.EOF Then
MsgBox "没有该螺母的参数"
Else
For i = 1 To rs110.Fields(i - 1).Name
Next i

End If
rs110.Close
cnn110.Close
Set rs110 = Nothing
Set cnn110 = Nothing
Set cmd110 = Nothing
Set par110 = Nothing

运行到Set rs110 = cmd110.Execute()出就出错，
哪位高手帮帮我吧，谢谢了

Answer 1

你用的是ACCESS2003吧，
VB对ACCESS2003的支持不好，最好采用我的方式，你的SQL无问题。
贴代码
Dim SQL As String '定义SQL为STRING类型变量
Dim Cnn As New ADODB.Connection '新建数据连接对象
Dim Rst As New ADODB.Recordset '新建记录集对象

Cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=FADIAN.MDB;DefaultDir=" & App.path & ";" '打开数据库，后面跟的是连接字符串
'对SQL语句赋值
SQL = "select * from User where Username='" & txtUserName.Text & "'" & " AND PASSWORD='" & txtPassword.Text & "'"
'执行SQL语句
Rst.Open SQL, Cnn

''进入下一个窗体

If Not Rst.EOF Then
'关闭本窗体
Unload Me
'显示主窗体
FrmMDI.Show
Else
'报错提示框
MsgBox "错误登录", vbOKOnly, "登录提示"
End If
'关闭数据集
Rst.Close
'关闭连接
Cnn.Close

Answer 2

"select * from mytable110" & "where 螺母b110 = ?"
中&连接两个字符串中间没有空格，成为
select * from mytable110where 螺母b110.....导致查询语句语法错误
应该在表名与 where之间加一个空格，即
select * from mytable110" & " where 螺母b110 = ?"

Answer 3

from 和 mytable110之间是不是没有留空格