0 votes
in VBA by Beginner (21 points)
edited by
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

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

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


'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 (12447314)" & _
"group by vc.document_id" & _
",vc.site_id" & _
"order by vc.document_id"


Set rs = New ADODB.Recordset

    rs.Open sqlQry, Conn, adOpenStatic, adLockOptimistic
    Sheets("210").Cells(2, 8).CopyFromRecordset rs
    rs.Close

Conn.Close

End Sub

I keep on running into this error and I can't figure out why (Run-Time Error '-2147217900 (80040e14)' ) when trying to connect to SQL. I am new at trying to use a query in vba so please pardon my ignorance!!! 

Had to star out the actual name of the database and server for security reasons ( not that way in the actual macro )

1 Answer

+1 vote
by Beginner (132 points)
selected by
 
Best answer

Add a space character before each of the sqlQry continuation lines as follows:

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 (12447314)" & _
" group by vc.document_id" & _
" ,vc.site_id" & _
" order by vc.document_id"

 

by Beginner (21 points)
Thank you for the quick response! That did the trick! I do have another question you might or might not know the answer to.

In this part of the code where the stars are I want to use data from the current sheet that I am on do you know how to create that syntax / code where the doc id its pulling is from the current sheet:

where vc.document_id in (************)" & _
by Beginner (132 points)

Something like this:

" where vc.document_id in (" & [$A$1] & ")" & _

Replace $A$1 with your cell reference.

by Beginner (21 points)

Thank you, this is actually helping me a lot!!! And your solution works perfectly! And forgive my ignorance, but can you explain why the syntax is how you referenced it and not like 

where vc.document_id in (" Sheets("210").Range("$G$2: G" & LastRow) ")" & _

or even with something like this: 

'Sheets("210").range("$G$2:G" & Range("B" & Rows.Count).End(xlUp).row)

The reason why I am asking is because I need to run this code for 9 other pages so I am going to have to reference the sheet and the specific column for example please see the image below.

I need to input all of the contents in column G starting from G2, but this data changes from sheet to sheet meaning that there will be more records on 1 page than another and I need it to grab all records for that page. Let me know if this makes sense. 

Once again thank you!!!!

by Beginner (132 points)
edited by

Try something like:

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

where MyID=Sheets("210").Cells(nRow, nCol).Value 

where nRow and nCol are calculated Long values. For example, nCol=7 for column G.

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.

...