Sql query from excel vba to access to retrun data that match Array

Question

I would like to know if there is a way without using loop to get multiply results from database using array?

Lets assume that we got this array:

Dim A As Variant
myUserName  = Array("AAA", "BBB", "CCC")

I want to know if there is a way to pass the array like that:

mySQLst = "SELECT * FROM UserTb WHERE (((UserTb.Nickname)='" & myUserName & "'));"

and get back all users who in the database answer the strings in ther array. I use Access database and Excel vba for this.

thank you for help.

Gustav · Accepted Answer · 2020-12-15 14:36:52Z

2

You can use Join:

myUserName  = Array("AAA", "BBB", "CCC")
mySQLst = "SELECT * FROM UserTb WHERE UserTb.Nickname IN ('" & Join(myUserName, "','") & "');"

answered Dec 15, 2020 at 14:36

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

1 Answer 1