Excel Marco, Firebird driver, ODBC Driver does not support the requested properties

I am receiving the following error message:

"Runtime error '-214727887(80040e21)'
ODBC driver does not support the requested properties"

I am trying to translate this SQL call into VBA
select count( co.orderid )
from customerorder co
join employee ee on ee.emplyid=co.emplyid join emplytimecard2 etc on etc.emplyid=co.emplyid and etc.scheddate=co.shipdate join timecarddetails2 tcd on tcd.timecardid=etc.timecardid and co.cds_ between tcd.startime and tcd.endtime where ( (tcd.emplytasks='SLS') or (tcd.emplytasks is null and ee.dept='SLS') or (tcd.emplytasks='MGM' and ee.dept='SLS') ) and co.orderstat='CP' and co.shipdate between '9/17/15' and '9/23/15'

Open in new window



This is my attempt to convert the SQL into VBA
 Function SQLSCountTransactionsSalesSF(BegDate1, EndDate1) As String
SQLSCountTransactionsSalesSF = "Select COUNT( customerorder.orderid)AS sum_Sales" & _
    " From customerorder" & _
    " JOIN employee ON employee.emplyid=customerorder.emplyid" & _
    " JOIN emplytimecard2 ON emplytimecard2.emplyid=customerorder.emplyid AND emplytimecard2.scheddate=customerorder.shipdate" & _
    " JOIN timecarddetails2 ON timecarddetails2.timecardid=emplytimecard2.timecardid AND customerorder.cds" & _
    " BETWEEN timecarddetails2.startime and timecarddetails2.endtime" & _
    " WHERE ( (timecarddetails2.emplytasks='SLS') OR (timecarddetails2.emplytasks is null AND employee.dept='SLS') OR (timecarddetails2.emplytasks='MGM' AND employee.dept='SLS') )" & _
    " AND orderdetail.orderstat = 'CP'" & _
    " AND orderdetail.shipdate BETWEEN (" & BegDate1 & ") AND (" & EndDate1 & ") "
End Function

Open in new window



The error occurs at the line:
rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic

Function CountTransactionsSalesSF(B<wbr ></wbr>egDate1, EndDate1) As Variant

Dim conSQL As ADODB.Connection
Dim strSQL As String
Dim i As Integer
Dim Total As Variant
Dim rs As ADODB.Recordset

Total = 0
i = 0

  Set conSQL = New ADODB.Connection
  conSQL.Open "DSN=CDHO;Driver={Firebird<wbr ></wbr>/InterBase<wbr ></wbr>(r) driver};Dbname=192.168.0.2<wbr ></wbr>05:d:\mult<wbr ></wbr>idev\gdbcr<wbr ></wbr>eation\sv1<wbr ></wbr>020_012HO.<wbr ></wbr>gbb;CHARSE<wbr ></wbr>T=NONE;UID<wbr ></wbr>=BETAVIEW;<wbr ></wbr>Client=C:\<wbr ></wbr>Windows\Sy<wbr ></wbr>sWOW64\gds<wbr ></wbr>32.dll"
  strSQL = SQLSCountTransactionsSales<wbr ></wbr>SF(BegDate<wbr ></wbr>1, EndDate1)
  Set rs = New ADODB.Recordset
  rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic
     
  If Not rs.EOF Then
    CountTransactionsSalesSF = rs.GetRows(-1, 1, "sum_sales")(0, 0)
    If IsNull(CountTransactionsSa<wbr ></wbr>lesSF) Then
       CountTransactionsSalesSF = 0
    End If
  Else
    CountTransactionsSalesSF = 0
  End If
  conSQL.Close
End Function

Open in new window

This function works for many of my other SQL calls. However, none have the join statement.

Thanks,

Ron
bikeskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
>>Function CountTransactionsSalesSF(B<wbr ></wbr>egDate1, EndDate1) As Variant
>>strSQL = SQLSCountTransactionsSales<wbr ></wbr>SF(BegDate<wbr ></wbr>1, EndDate1)
??

shoudn't it be as:

Function CountTransactionsSalesSF( egDate1, EndDate1) As Variant
strSQL = SQLSCountTransactionsSales

?

I guess you had done a mass replace and some of your codes had been replaced?

similarly to:

conSQL.Open "DSN=CDHO;Driver={Firebird<wbr ></wbr>/InterBase<wbr ></wbr>(r) driver};Dbname=192.168.0.2<wbr ></wbr>05:d:\mult<wbr ></wbr>idev\gdbcr<wbr ></wbr>eation\sv1<wbr ></wbr>020_012HO.<wbr ></wbr>gbb;CHARSE<wbr ></wbr>T=NONE;UID<wbr ></wbr>=BETAVIEW;<wbr ></wbr>Client=C:\<wbr ></wbr>Windows\Sy<wbr ></wbr>sWOW64\gds<wbr ></wbr>32.dll"
  etc...

I don't quite understand this...
0
bikeskiAuthor Commented:
Looks like something got messed up with my copy and paste in the original message.... It's still not working.
Function CountTransactionsSalesSF(BegDate1, EndDate1) As Variant

Dim conSQL As ADODB.Connection
Dim strSQL As String
Dim i As Integer
Dim Total As Variant
Dim rs As ADODB.Recordset

Total = 0
i = 0

  Set conSQL = New ADODB.Connection
  conSQL.Open "DSN=CDHO;Driver={Firebird/InterBase(r) driver};Dbname=192.168.0.205:d:\multidev\gdbcreation\sv1020_012HO.gbb;CHARSET=NONE;UID=BETAVIEW;Client=C:\Windows\SysWOW64\gds32.dll"
  strSQL = SQLSCountTransactionsSalesSF(BegDate1, EndDate1)
  Set rs = New ADODB.Recordset
  rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic
     
  If Not rs.EOF Then
    CountTransactionsSalesSF = rs.GetRows(-1, 1, "sum_sales")(0, 0)
    If IsNull(CountTransactionsSalesSF) Then
       CountTransactionsSalesSF = 0
    End If
  Else
    CountTransactionsSalesSF = 0
  End If
  conSQL.Close
End Function

Open in new window

0
Ryan ChongCommented:
is it the error stuck at line:

conSQL.Open "DSN=CDHO;Driver={Firebird/InterBase(r) driver};Dbname=192.168.0.205:d:\multidev\gdbcreation\sv1020_012HO.gbb;CHARSET=NONE;UID=BETAVIEW;Client=C:\Windows\SysWOW64\gds32.dll"
 
?

is the error message still the same as:
"Runtime error '-214727887(80040e21)'
ODBC driver does not support the requested properties"
?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

bikeskiAuthor Commented:
Yes, same error,

stuck at:
rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic
0
bikeskiAuthor Commented:
Go it to work now, here's the SQL string:

Function SQLSCountTransactionsSalesSF(BegDate1, EndDate1) As String
SQLSCountTransactionsSalesSF = "select count(*) AS sum_Sales" & _
    " from customerorder " & _
    " join employee on employee.emplyid=customerorder.emplyid " & _
    " join emplytimecard2 on emplytimecard2.emplyid=customerorder.emplyid" & _
    " and emplytimecard2.scheddate=f_striptime(customerorder.cds_) " & _
    " join timecarddetails2 on timecarddetails2.timecardid=emplytimecard2.timecardid and customerorder.cds_ between timecarddetails2.startime and timecarddetails2.endtime " & _
    " WHERE customerorder.shipdate BETWEEN (" & BegDate1 & ") AND (" & EndDate1 & ")" & _
    " and ( (timecarddetails2.emplytasks='SLS') or (timecarddetails2.emplytasks is null and employee.dept='SLS') or (timecarddetails2.emplytasks='MGM' and employee.dept='SLS') )"
End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bikeskiAuthor Commented:
figured out internally
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.