0 votes
in VBA by Beginner (21 points)
Sub SQL_210()

'This macro is used for running a SQL to get captured data.

'Create Connection Object
Dim Conn As ADODB.Connection 'Connects to SQL server
Dim rs As ADODB.Recordset 'Holds the data
Dim sqlQry As String 'Holds the sql txt
Dim sConnect As String
Dim DocIds As String
'Dim nRow As Long
'Dim nCol As Long

'Assigning Variables
Set rs = New ADODB.Recordset
Set Conn = New ADODB.Connection 'Starts the connection


Dim LastRow_A As Long
With Sheets("210")
    LastRow = .Range("$G$2").CurrentRegion.Rows.Count
End With
'nRow = 2
'nCol = 7
'DocIds = Sheets("210").Cells(nRow, nCol).Value <<<<<< This is the one that works but it does not grab the entire column
'DocIds = Sheets("210").Range("$G$2:G" & LastRow).Value
DocIds = Sheets("210").Range("$G$2:G" & LastRow).Select

Conn.Open ("Provider= SQLOLEDB; Integrated Security= SSPI; Initial Catalog= Basware*****; Data source= drpr-*******")
'(Initial Catalog = database, Data Source = Server)

If IsEmpty(Sheets("210").Range("B2")) = True Then
        Call SQL_211
    End If

    Sheets("210").Select

    'Actual SQL Query
    sqlQry = "Select vc.site_id,vc.document_id" & _
    " from dbo.vwcKey_Current_INH vc" & _
    " join dbo.tbdImage i on vc.document_id = i.document_id" & _
    " where vc.document_id in (" & [DocIds] & ")" & _
    " group by vc.document_id" & _
    " ,vc.site_id" & _
    " order by vc.document_id"

    Set rs = New ADODB.Recordset
    rs.Open sqlQry, Conn, adOpenStatic, adLockOptimistic    <<<<<<<<<< Error Happens Here
    Sheets("210").Cells(2, 8).CopyFromRecordset rs
    rs.Close
Conn.Close


End Sub

I am trying to run a sql query using data from a column in the excel sheet. When I run this I get a Run-time error '-2147217900 (80040e14)'  (Invalid Column Name "True"). I have tried many different things and I have added the other things (in comments) that have worked but don't grab the entire column. I have also concatenated the column I need to be in the format needed for SQL . I need column G (The first record / cell in G does NOT have a  ,  but all the others following it do). 

Thanks,

Ben 

1 Answer

0 votes
by Beginner (132 points)
edited by

Remove the square brackets from DocIDs:

" where vc.document_id in (" & DocIds & ")" & _

Change DocIDs to this:

DocIds = Sheets("210").Cells(nRow, 7).Value

Use a For...Next loop to do your query for each row:

For nRow = 2 To LastRow

DocIds = Sheets("210").Cells(nRow, 7).Value

prepare your query
execute your query

Next nRow

 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...