0

I have a sheet that contains a list of zip codes, I need to update a flag for those zip codes in the database. Now the zipcodes are in 3 columns so I am able to select all the zip codes & store them into Variant. Instead of counting the zip codes & running SQL statements for each one, I want to pass the Variant directly to SQL & execute a stored procedure that could do the job. I don't want to loop the SQL command 9 times for this dataset just want to pass the variant in one go.

Here's how my zipcode sheet looks like:

      A       B       C
1    99999   88888   77777
2    55555   66666   44444
3    22222   11111   33333

I have created a macro that selects all the zip codes I'm storing them in Variant name zipArr.

Module1

Sub UpdateZip
     Dim zipArr as Variant   
     Dim oCon as DBConnection
     zipArr = Range("A1:C3").Select
     oCon.UpdateToSQL(zipArr)
End Sub

Public Module

Public Sub DBConnection(arr as Variant)
    Dim con As New ADODB.Connection
    Dim cmd as New String
    con.ConnectionString = "Provider=SQLOLEDB.1;" _
         & "Server=(local);" _
         & "Database=Demo;" _
         & "Integrated Security=SSPI;" 

    con.Open
    cmd.CommandText = "Exec upd_ZipCodes" & arr
    cmd.Execute
    con.close
    set con = nothing
End Sub

Any suggestions are appreciated. I tested running update query but passed a value of 1 zip code & the change happened on SQL. Would be great if that is possible in bulk. I do have a query from SQL Side but I'm trying to resolve this first.

braX
  • 11,506
  • 5
  • 20
  • 33
Starky
  • 135
  • 6
  • The `Select` in `zipArr = Range("A1:C3").Select` looks off, for starters. – BigBen May 19 '21 at 23:31
  • You can't pass in a variant array from VBA and expect SQL server to deal with it. Maybe you can post the definition for `upd_ZipCodes` - that would be helpful here. If it expects a single value then that's what you have to pass in. – Tim Williams May 20 '21 at 00:01
  • I have not yet worked on the SQL part but the update statement that I intend to run in a stored procedure is Update set Flag='Y' where Zipcode in (@array). Something like that. Or do I need a cursor to take an array as an input?
     – Starky May 20 '21 at 00:05
  • I'm not a SQL Server user but this looks relevant here: https://stackoverflow.com/questions/21318757/passing-a-parameter-list-from-excel-2010-to-ms-sql-2012 – Tim Williams May 20 '21 at 00:42
  • As a previous comment states `zipArr = Range("A1:C3").Select` is incorrect. Print that variable to the console and view it. A solution could be to store the list of zips as CSV in a string variable and pass that into the sproc. The sproc would then need to parse that in order to use it, but that it easy in SQL Server. – Michael Z. May 20 '21 at 06:10
  • But Micheal, I tried using Worksheet Functions for TextJoin & Concat but it's excel 2013 so it's not supported. – Starky May 20 '21 at 06:20

0 Answers0