How to highlight a row based on a DB query result?

I think you'd be better off with:

PHP Code: 

    SELECT x
    FROM 
        table
    WHERE
        x in (1154, 1155,1156,1157) 


This would return a recordset of simply the invoice ids(?) that are in the database. You can then just do a VLookup on your primary dataset. You only then need to do 1 query

Originally Posted by Kyle123

I think you'd be better off with:

PHP Code: 

    SELECT x
    FROM 
        table
    WHERE
        x in (1154, 1155,1156,1157) 


This would return a recordset of simply the invoice ids(?) that are in the database. You can then just do a VLookup on your primary dataset. You only then need to do 1 query

I agree that would be a possibility and less queries, but as I mentioned I am stuck at the aspect as well. I can get the results to print to the ActiveSheet but it is in 1 row not along a column, which would allow me to do a vLookup.

That query should give you a list of numbers in a single column

Vlookup works down a column , just return the first column. You could use match if you prefer though

excelresult.png
You would think so yes, but it returns the results into a row not column as seen above.

Sub VerifyInvoices()
' Declare the QueryTable object
 Dim qt As QueryTable
 Dim i As Integer
 
' Set up the connection string, reference an ODBC connection
 ' There are several ways to do this
 ' Leave the name and password blank for NT authentication
 connstring = "ODBC;DSN=;UID=;PWD=;Database="
 i = 0
 ' Set up the SQL Statement
 For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
 sqlstring = "Select Distinct 1 from Trans (NOLOCK) Where uref = '" & ce.Value & "' "
 With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring).Refresh
 End With
 i = i + 1
 Next ce
 



End Sub

Here is the code currently which gives the previous post result.

Of course it does because you're only returning a single record?

Fields go across, records go down the sheet. Each time you are running the query it inserts a cell with the value in and moves the previous value across (I'm guessing)

Try my way - you will end up with a list of numbers going straight down column B and only need to query the db once

How would I go about adding all row.values into the SQL? The numbers of values that I would need to add to the query could range from a few dozen to several hundred.

THANK YOU for helping out. I don't do this normally so all pointers are appreciated

No problem at all

All you need to do is keep adding the ids to the SQL string you build in your loop with commas between them. Once the loop has finished pass the SQL string to the query table.

Try a hard coded SQL string first to see if it gives you the result you expect, then incorporate the loop afterwards.

Ok I go that working

Sub VerifyInvoices()
' Declare the QueryTable object
 Dim qt As QueryTable
 Dim i As Integer
 
' Set up the connection string, reference an ODBC connection
 ' There are several ways to do this
 ' Leave the name and password blank for NT authentication
 connstring = "ODBC;DSN=;UID=;PWD=;Database="
 sqlstring = "Select uref, hperson, sTotalAMount from Trans (NOLOCK) Where uref in ("
 ' Set up the SQL Statement
 For Each ce In Range("G1:G" & Cells(Rows.Count, 1).End(xlUp).Row)
 sqlstring = sqlstring & "'" & ce.Value & "',"
 Next ce
 
 sqlstring = Left(sqlstring, Len(sqlstring) - 1) & ")"
 
 With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Sheet1").Range("A1"), Sql:=sqlstring)
 .Refresh
 End With


End Sub

I tis returning the resultset to a new sheet starting in A1 and that is great. Now here is the caveat. The actual file may have the same number but a different value in column B and person in column C. How would I add this to the SQL so that it would look like

Select x,y,z from table Where x=A1 and y=B1 and z=C1

Then return that to the sheet correctly.

The whole story is I have a list of invoices from vendors that I want to check against the database to see if I have already entered a similar invoice in the system before uploading a potential duplicate. Again THANKS!!!!!

I wouldn't, I'd just return all those columns and leave the query as it is. You can then do the matching in Excel, it simplifies things somewhat

I haven't looked yet but is there a way to embed the DSN connection and not allowing a user to access the information of the connection for viewing without a password?

Thanks for the help I think I have what I need and will just allow the connection to be how it is with a DSN file.