Question

VBA编程 连接access数据库总是提示“from子句错误” 但是找不到

Private Sub CommandButton1_Click()
Dim ssz, cxz, company As String
Dim i As Integer
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
If OptionButton1.Value = 1 And OptionButton2.Value = 0 Then
company = "VTCN"
a = True
Else
company = "VTKT"
a = False
End If
ssz = ComboBox1.SelText
If ssz = "客户号" Then cxz = "CID"
If ssz = "客户名" Then cxz = "CName"
If ssz = "邮编" Then cxz = "CZip"
ListBox1.AddItem
ListBox1.List(0, 0) = "客户号"
ListBox1.List(0, 1) = "客户名"
ListBox1.List(0, 2) = "地址"
ListBox1.List(0, 3) = "邮编"
ListBox1.List(0, 4) = "联电话1"
ListBox1.List(0, 5) = "联系系人1"
ListBox1.List(0, 6) = "联系电话2"
ListBox1.List(0, 7) = "联系人2"
i = 1
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" & ThisWorkbook.Path & "\VOITH.accdb"
rs.Open "select * from Customer-" & company & " where " & cxz & " like %" & TextBox1.Text & "%", cn
rs.MoveFirst
Do
ListBox1.Clear
ListBox1.AddItem
ListBox1.List(i, 0) = rs.Fields("CID")
ListBox1.List(i, 1) = rs.Fields("CName")
ListBox1.List(i, 2) = rs.Fields("CName")
ListBox1.List(i, 3) = rs.Fields("CName")
ListBox1.List(i, 4) = rs.Fields("CName")
ListBox1.List(i, 5) = rs.Fields("CName")
ListBox1.List(i, 6) = rs.Fields("CName")
ListBox1.List(i, 7) = rs.Fields("CName")
i = i + 1
rs.MoveNext
Loop While Not rs.EOF
ListBox1.SetFocus
rs.Close
cn.Close
End Sub

Answer 1

怀疑错误提醒应该是“form字句错误”吧，不是from，这两个单词是完全不一样的，另外
Dim ssz, cxz, company As String这个定义语句，实际上“ssz”和"cxz"为变量类型，你后来赋值语句

If ssz = "客户号" Then cxz = "CID"If ssz = "客户名" Then cxz = "CName"有可能造成类型不匹配。当然以上不一定可以完全解决，但是从你的这种心细程度看，最好还是一字一句检查，不要指望VB系统自检。

Answer 2

试试把
Dim temp as string
temp="select * from Customer-" & company & " where " & cxz & " like %" & TextBox1.Text & "%", cn
再看看整个句子有没有错,
有时欠一个Space或'

Answer 3

cxz 是什么？当执行 SQL 查询语句的时候还没有定义么？！

追问

If ssz = "客户号" Then cxz = "CID"If ssz = "客户名" Then cxz = "CName"If ssz = "邮编" Then cxz = "CZip"
cxz是为了动态查找，当用户选择用“客户号查询的时候，cxz就为"CID"，因为我在数据库的Customer表中设置了客户编码属性为CID