VBA SQL Loop using QODBC

Question

I am trying to loop through an existing table (tblSalesOrder) and I need to run through every line (where they relate to that particular customer) and write each line into an SQL statement and execute it.

What is the easiest way to go about this procedure?

The number of lines will need to be counted prior to data being written via SQL to a QuickBooks database.

I can code something similar in php using the code below, but I am unsure how to convert this into a VBA friendly format:

$sql_count = "SELECT count(*) FROM tblSalesOrder WHERE Customer='cust_number'";
execute_query($sql_count)

When the above value is greater than 0, the vba code should loop through the queries.

Answer 1

You need a record set to execute and store your query result.

Dim sql_count As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

You need a connection to your database connection. "cn" is storing the connection to QuickBooks.

sql_count = "SELECT count(*) FROM tblSalesOrder WHERE Customer='cust_number'"
Set rs = cn.Execute(sql_count)

Do While rs.Fields(0).Value > 0 ' rs.Fields(0) should get you the count(*)
' your codes
Loop

For the connection string for QuickBooks, you probably could read up here

Answer 2

Please refer below links. You may open the record set:

http://www.qodbc.com/qodbcvisualbasic.htm

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/28/57/example-code-of-update-qb-from-ms-access