0

I need to start a query to retrieve data from Access database using VBA which I want to use a variable number as a parameter. Is it possible?

like the: 

field name: NMT field type (number)
table name: Orders

and the code is like the following:

Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim X as Integer
X = me.textbox1.value
Con.Open "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & U.Database01 & "\DB.accdb;Persist Security Info=False"
Rs.Open "select * from Orders where nmt = '" + X + "'", Con, adOpenDynamic, adLockPessimistic

Whenever I run this query, I get a run-time error '13' type mismatch.

Any suggestions ?

Improve this question
4
  • 3
    Use & to concatenate, not +. Commented Dec 30, 2019 at 19:53
  • 2
    Also, you could look at using parameters also stackoverflow.com/questions/10352211/… Commented Dec 30, 2019 at 19:55
  • 3
    Your query is trying to compare a number field type to a string. Remove the single quotes. Commented Dec 30, 2019 at 19:55
  • 2
    To avoid SQL injection (which may not work in Access but can if you later switch backends to Access app), consider parameterization as @Nathan_Sav. This is a good practice beyond VBA and beyond Access. Commented Dec 30, 2019 at 21:23

1 Answer 1

Reset to default
1

Multiple Issues

  1. Type-mismatch in WHERE clause: Your query (i.e. the WHERE clause) tries to compare a Number-column from database with a String-value (e.g. WHERE numberField = '123'). This will result in a runtime error Type mismatch (Error 13). See also similar question.

  2. Unsafe to use + to concatenate Strings When building the query you tried to concatenate the query-template with the number-parameter by a plus-sign. This works only when operating on numbers. See related question

Solution

  1. remove single-quotes: you should compare the Number-column NMT with a number literal (e.g. WHERE nmt = 123)
  2. use & to concatenate strings. This will also convert numbers to strings. Besides I explicitly used CStr function below.
Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim strSQL As String
Dim nmtNumber as Integer ' you named it x before

nmtNumber = me.textbox1.value
strSQL = "SELECT * FROM Orders WHERE nmt = " & CStr(nmtNumber) ' removed single-quotes and used ampersand to concatenate with converted string
Con.Open "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & U.Database01 & "\DB.accdb;Persist Security Info=False"
RS.Open strSQL, Con, adOpenDynamic, adLockPessimistic

Further improvement

I already extracted the SQL string (building) into a separate variable strSQL above.

Better would be to use predefined/prepared and parameterized queries:

See also

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.