Using a VBA array in a SQL statement

Question

I am trying to write some code that uses SQL to delete rows from several tables.

A user would type type numbers into a textbox that are separated by a comma which is used in the WHERE clause of a SQL DELETE statement.

I have managed to split the string into a variant array and now I want to insert it into my SQL statement.

How do I insert the variable into the SQL statement and have it run through every element of the array?

EDIT: A bit more digging has taught me about For Each Next statements. This is probably what im looking for.

Community · Accepted Answer · 2017-05-23 11:53:28Z

7

I suggest you build your query in VBA, then your list of numbers can be an IN statement:

 sSQL = "DELETE FROM table WHERE ID In (" & MyList & ")"

Where MyList = "1,2,3,4" or such like.

As you can see, you do not need an array and a textbox would be more suitable than a combobox. If you wish to allow your users to select by say, a name, then a listbox is useful. You can iterate through the selected items in the listbox and build a string from IDs to be used in the Delete statement. ( MS Access 2007 - Cycling through values in a list box to grab id's for a SQL statement )

You can then execute the sql against an instance of a database. For example:

 Dim db As Database

 Set db = CurrentDB
 db.Execute sSQL, dbFailOnError

 MsgBox "You deleted " & db.RecordsAffected & " records."

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jul 23, 2012 at 9:12

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

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

4 Comments

VBwhatnow Over a year ago

Sorry I got my terminology mixed up and used comboxbox when I did actually mean textbox.I did not know about the where in statement and i will use that in the future. I have to do other things to these numbers which I need to use an array for so your answer isnt suitable in this case. I have managed to solve the problem myself. Ill mark as accept though because it is a very good answer and taught me something new.

Fionnuala Over a year ago

It is very easy indeed to create an array from a list in VBA. List = "1,2,3"; Ary = Split(List,","), but I doubt that you can use an array in an sql statement.

VBwhatnow Over a year ago

I used a for each next statement to do it.

Fionnuala Over a year ago

That is the long way round. :)

Madhivanan · Accepted Answer · 2012-07-23 09:09:29Z

0

A generic approach

WHERE 
','+Array+',' like '%,'+col+',%'

It will consider all the numbers available in your Array

answered Jul 23, 2012 at 9:09

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

Jorge Aguirre · Accepted Answer · 2013-01-14 18:45:18Z

0

You could make it simple and elaborate a string, something like

stringBuilder sb = StringBuilder("DELETE FROM YOURTABLE WHERE ");
foreach(string st in stringArray){
    sb.append("YOURFIELD='" + st + "'");
    //If it is not the last element, add an "OR"
    if (st != stringArray[stringArray.length -1]) sb.append(" OR ");
}

//Now, you have a string like
//DELETE FROM YOURTABLE WHERE YOURFIELD='hello' OR YOURFIELD='YES'

//... do something with the command

edited Jan 14, 2013 at 18:45

answered Jul 23, 2012 at 9:10

Jorge Aguirre

2,8673 gold badges22 silver badges30 bronze badges

Comments

Radim Köhler · Accepted Answer · 2013-08-11 04:38:49Z

0

This method will fail if you want to run SQL query on two (or multiple) columns using array values from two different arrays. .e.g

where col1=array1(i) and col2=array2(i)

edited Aug 11, 2013 at 4:38

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

answered Aug 11, 2013 at 4:20

vbalite

111 silver badge6 bronze badges

Collectives™ on Stack Overflow

Using a VBA array in a SQL statement

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related