I have some code that connect to a database and pulls info into an excel sheet.
I have only one problem, that is when there is no value in a cell (that cell is populated from a previous SQL statement and serves as input for the next), the code will fail as you can't put a NULL value in the SQL query.
I tried by solving it that if the cell has no value, skip the rest of the code and go to the next Sub. It actually does not work and ends up in "conn.Close" as errror.
Please see below code (I commented it out the code that does not work)
Sub find_company_Accounts_part2() Dim uiValue As String Dim login As String Dim LastRow As Long Dim conn As ADODB.Connection Dim rec1 As ADODB.Recordset Dim thisSql As String Dim branch As String Dim Acc As String Dim j As Long login = UserForm3.login.Text uiValue = UserForm3.uiValue.Text FinalRow = Sheets("Data").Cells(60000, 5).End(xlUp).Row For j = 6 To FinalRow branch = Sheets("Data").Cells(j, 5).Value Acc = Sheets("Data").Cells(j, 6).Value 'If branch = "" Then 'find_company_relation_ID 'Else Set conn = New ADODB.Connection conn.CommandTimeout = 900 conn.Open "DSN=PROD;Databasename=DB01;Uid=" & login & ";Pwd=" & uiValue & ";" If branch Like "002*" Or branch Like "004*" Then thisSql = "Select " & _ "ACCOUNT_TYPE_CODE, " & _ "ACCOUNT_OPEN_DATE " & _ "FROM CUST_CUSTACCT_1 " & _ "Where ((BRANCH_NO)= " & branch & ") And ((ACCOUNT_NO)= " & Acc & ") AND EFFECTIVE_END_DT ='9999.12.31'" Else thisSql = "Select " & _ "ACCOUNT_TYPE_CODE, " & _ "ACCOUNT_OPEN_DATE " & _ "FROM CUST_CUSTACCT_2 " & _ "Where ((BRANCH_NO)= " & branch & ") And ((ACCOUNT_NO)= " & Acc & ") AND EFFECTIVE_END_DT ='9999.12.31'" End If LastRow = Sheets("Results").Cells(60000, 4).End(xlUp).Row + 1 Set rec1 = New ADODB.Recordset rec1.Open thisSql, conn Debug.Print RecordCount Sheets("Results").Cells(LastRow, 4).CopyFromRecordset rec1 End If Next j 'here it goes wrong conn.Close Set conn = Nothing find_company_relation_ID End Sub
The code is in a Userform in excel.