ADO headache -- what's up?

[SydneyGeek]

Hi, just testing an ADO import routine in Excel and it's choked. It was working a couple of days ago, then I added another field to the SQL string and it hangs. Code:

Sub RetrieveFromXL()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim FirstBU, LastBU
   Dim sFilter1 As String
   Dim sFilter2 As String
   Dim sSQL As String
   Dim Rw As Long
   
   'ADO-based code for downloading JDE data. Provides greater flexibility for filters
   'and simpler filtering changes for each project.
   
   'Define BU range for each project
   FirstBU = Sheets("Acquisition Details").Range("B2").Value
   LastBU = FirstBU + 10000
   
   'Define filters
   ''First filter: BU range to define project
   'version for TEXT columns
   'sFilter1 = "GBMCU >=" & "'    " & FirstBU & "' " & "AND GBMCU <'    " & LastBU & "'"
   'version for NUMERIC columns
   sFilter1 = "GBMCU >=" & FirstBU & " AND GBMCU <" & LastBU
   ''Second filter: omit GBSUB values over 82000 and between 10000 and 11999
   If bLand = "Yes" Then
      'version for TEXT columns
      'sFilter2 = " AND (GBSUB < '10000' OR (GBSUB >= '12000' AND GBSUB < '82000')) "
      'version for NUMERIC columns
      sFilter2 = " AND (GBSUB < 10000 OR (GBSUB >= 12000 AND GBSUB < 82000)) "
   Else
      'version for TEXT columns
      'sFilter2 = " AND GBSUB < '82000' "
      'version for NUMERIC columns
      sFilter2 = " AND GBSUB < 82000 "
   End If
   
   sSQL = "SELECT GBAPYC, GBAN01, GBAN02, GBAN03, GBAN04, GBAN05, GBAN06, "
   sSQL = sSQL & "GBAN07, GBAN08, GBAN09, GBAN10, GBAN11, GBAN12, GBAPYN, "
   sSQL = sSQL & "GBAWTD, GBMCU, GBOBJ, GBSUB, GBSBL1, GBSBLT1, GBFY, GBLT "
   sSQL = sSQL & "FROM [DevAccountBalances$] "
   sSQL = sSQL & "WHERE " & sFilter1 & sFilter2
   Debug.Print sSQL
   
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=M:\Common\JDE\DevAccountBalances.xls;" & _
   "Extended Properties=Excel 8.0;"
      .Open
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, ActiveConnection:=cnn, _
     CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
       Options:=adCmdText

   Sheets("JDE_Filter2").Activate
   Range("A1").CurrentRegion.Offset(1, 0).Clear
   
   Range("A2").CopyFromRecordset rst

   rst.Close
   cnn.Close

End Sub

This is the problem line:

   rst.Open Source:=sSQL, ActiveConnection:=cnn, _
     CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
       Options:=adCmdText

This is the error message:
Run-time error '-2147217904(80040e10)':

No value given for one or more required parameters.

Can anyone shed light on this please?

Denis

 

[SydneyGeek]

Hi, thanks for looking.

It turns out that this was the crucial bit:
then I added another field to the SQL string and it hangs ... I had a typo in the new field name

Back functioning again...

If you're interested, here's a listing of ADO errors:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209050

Denis

 

[NateO]

The error handler here has kind of helped me in the past as well:

Trapping Jet Errors with ADO and the OLE DB Provider
http://support.microsoft.com/?kbid=201476

 

[SydneyGeek]

Thanks Nate



Denis