VB to connect the access database query execution error-CodePudding

Wrote a small program used to query the product serial number and related information, product information Access database database. The MDB,

Because the product has four main parameters, namely the Subfamily, the Function, the Connection and Actuation, four field name corresponding to the database, based on the combination of these four fields can be returned only a product number,

The code is as follows:

Judgment procedure in the path of * * * * * * *
Dim STR As String
STR=App. Path
If Right (STR, 1) & lt;> "" Then
STR=STR + ""
End the If

* * * * generate connection access string * * * * *
Dim connStr As String
ConnStr="Provider=Microsoft. Jet. The OLEDB. 4.0. Persist Security Info=False; Data Source="& amp; STR & amp; "Database. The MDB" & amp; "; The Jet OLEDB: Database Password=12345678
"
* * * * * create a connection instance and connect * * * * * *
Dim conn As New ADODB. Connection
Conn. Open connStr

* * * * * generate SQL query * * * * *
Dim strSQL As String
StrSQL="select * from HuizhouMicroswitch where Subfamily='" & amp; VarSubfamily & amp; "' and Function=" & amp; VarFunction & amp; "' and Connection=" & amp; VarConnection & amp; "' and Actuation=" & amp; VarActuator & amp; "'
"Query to determine whether the four fields to meet here, four variables such as varSubfamily is according to the previous value of the option button to generate a string,
This statement is used in Access can return results

* * * * * create instances and open the table you * * * *
Dim the rs As the ADODB library you
Set the rs=New ADODB. You
Rs. The Open strSQL
The program has been an error here, prompt "object 'Open' method '_Recordset' failure.

Tried to reduce the judging strSQL four conditions to two, the program can return results; But as long as the increased to three conditions, namely the error,

Consult everybody:
1) whether the SQL query support, at most, only two judgment conditions?
2) if not, how to modify the above statements?

Hope to get everybody's help, thank you!

CodePudding user response:

 strSQL="select * from HuizhouMicroswitch where" & amp; _ 
"Subfamily='" & amp; Str (varSubfamily) & amp; "'" & amp; _ 
"And the Function='" & amp; Str (varFunction) & amp; "'" & amp; _ 
"And the Connection='" & amp; Str (varConnection) & amp; "'" & amp; _ 
"And Actuation='" & amp; Str (varActuator) & amp; "' 
"

CodePudding user response:

After strSQL configuration, add

The Debug. Print strSQL

What see strSQL content,

CodePudding user response:

In theory, in the use of string concatenation operator & amp; With other string concatenation statement, do not need to Str function, the interpreter to type automatic conversion,

CodePudding user response:

Also look at the four field data type is what, in the SQL statement conditions also need according to different data types to write different variable costs:
SQL="select * from tablename where character field='" & amp; Character variables & amp;" 'and numeric fields="& amp; Numeric variables & amp;" "