ADO headache -- what's up?

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:
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:
Code:
   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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,148,228
Messages
5,745,482
Members
423,953
Latest member
MrC54

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top