Run-time error '-2147217865(80040e37)':

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi: When i try to execute the query it gives syntax error that
Run-time error '-2147217865(80040e37)':
Table or view not found: Table 'user_view' not found
But if i copy the same query and run into the database it works fine
no error. I dont know why it gives error when i try to execute the
query within the VB code. It gives error when condition is true
"frmUserDistribution.chkInterOffice.Value = 1"

Set rsADODM = cnADODM.Execute(SQL)
-----------------------------------------------------------------
If frmUserDistribution.chkInterOffice.Value = 1 Then
SQL = "set temporary option user_estimates='enabled'; " & _
"select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal, assign_id, dbtr_view.status_code,
dbtr_view.status_date, dbtr_view.next_contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debtor_id,0) " & _
"inner join user_view on user_view.user_id =
dbtr_view.assign_id " & _
"left outer join dm.pmt on (dbtr_view.debtor_id =
pmt.debtor_id,0) where "
Else
SQL = "set temporary option user_estimates='enabled'; " & _
"select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal, assign_id, dbtr_view.status_code,
dbtr_view.status_date, dbtr_view.next_contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debtor_id,0) " & _
"left outer join dm.pmt on (dbtr_view.debtor_id =
pmt.debtor_id,0) where "
End If
SQL = SQL & "dbtr_view.assign_id in ('" & Left$(AssignID, 3) & "'"
SQL = StringToSQL(SQL, AssignID)

'Adjust day to query accounts that have not had a payment since
then
If NoOfDaysPMT <> -1 Then
pmtDate = Format(DateAdd("d", -NoOfDaysPMT, Date), "yyyy-
mm-dd")
'no payment has been made for atleast noOfDaysPmt
SQL = SQL & " and pmt_date < '" & pmtDate & "' "
End If
'Adjust list date accordingly
If lstdateFrom <> -1 Then
ldateFrom = Format$(lstdateFrom, "yyyy-mm-dd")
ldateTo = Format$(lstdateTo, "yyyy-mm-dd")
SQL = SQL & " and list_date between '" & ldateFrom & "'
and '" & ldateTo & "' "
End If
'Only return accounts with legit phone numbers
If GoodPhones Then
'returns accounts with valid phone numbers
SQL = SQL & " and active_phone1 not in
('0000000000','1111111111','2222222222','33333333333','4444444444','5555555555','6666666666','7777777777','8888888888','9999999999')
and " _
& "active_phone1 between '0000000000' and '9999999999'
"
End If

'specifiy client id
If ClientID(1) <> -1 Then

If cltIDNotIn = False Then
SQL = SQL & " and (debt.clt_id like '" & ClientID(1) & "'
"
i = 2
Do While i <= ClientID.Count
SQL = SQL & " or debt.clt_id like '" & ClientID(i) &
"' "
i = i + 1
Loop
SQL = SQL & ") "
Else
SQL = SQL & " and (debt.clt_id not like '" & ClientID(1) &
"' "
i = 2
Do While i <= ClientID.Count
SQL = SQL & " and debt.clt_id not like '" &
ClientID(i) & "' "
i = i + 1
Loop
SQL = SQL & ") "
End If
End If

'Specify balance range of accounts to return
If BalanceRange_Lower <> -1 Then
'checks for upper bound
If BalanceRange_Upper <> -2 Then
SQL = SQL & " and cur_bal between " &
BalanceRange_Lower & " and " & BalanceRange_Upper
Else
SQL = SQL & " and cur_bal > " & BalanceRange_Lower
End If
End If

'Specify status codes of accounts to return
If StatusCodes <> "none" Then

If StatCodesNotIn = False Then
SQL = SQL & " and dbtr_view.status_code in ('" & _
Left$(StatusCodes, 3) & "'"
SQL = StringToSQL(SQL, StatusCodes)
Else
SQL = SQL & " and dbtr_view.status_code not in ('" & _
Left$(StatusCodes, 3) & "'"
SQL = StringToSQL(SQL, StatusCodes)
SQL = SQL & " and dbtr_view.status_code < '400' "
End If

Else
'if no status code specified - be sure only open accounts are
returned

SQL = SQL & " and dbtr_view.status_code < '400' "

End If
Debug.Print SQL
cnADODM = OpenDatabaseConnection(Debtmaster_ReadOnlyConnection)
cnADODM.Open

'Query dbtr_view, pmt, debt tables based on criteria
Debug.Print SQL
Set rsADODM = cnADODM.Execute(SQL)

.

Relevant Pages

  • Re: Dont want to wrap long text - just truncate
    ... document then postprocess the results using VBA, ... All you can do in the standard user interface is specify a table of view, ... you have to specify the SQL in an OpenDataSource method call in VBA. ... via MS Query, MS Query has to be installed, and you will need an ODBC DSN ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Dont want to wrap long text - just truncate
    ... >new document then postprocess the results using VBA, ... > and specify a bit of sorting and filtering (go into the Edit Recipients ... To do more, you either have to go through MS Query, ... > or you have to specify the SQL in an OpenDataSource method call in VBA. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: slow delete query
    ... Try pasting that sql text ... >> query and see if it will work (but back up the database ... >> query builder to design your queries you can specify ...
    (microsoft.public.access.queries)
  • group by errors
    ... Creating a view in SQL and use Access to open the view ... >specify ALL with the CUBE or ROLLUP operators. ... the query. ... >summary rows are introduced into the result set. ...
    (microsoft.public.access.queries)
  • RE: Expire accounts from Active Directory after a period of inactivity
    ... your user groups for all accounts that have been inactive for 30 or more ... It does seem better though to store the output to a SQL database. ... Query it once a month or once a week ... box giving hackers complete access to all your backend systems! ...
    (Focus-Microsoft)