Extracting Access data
 

Tom,
Here is one of my procedures in which this problem occurs:

Sub BDF1()
Set Point = Sheets("Extract").Range("AW1")
mycode = Point.Offset(Point.Offset(0, 2) + 1, 0)'code
myyr = Sheets("Extract").Range("AV1") 'year
Sheets("BDFAvailable").Range("a7:e25").ClearConten ts

Const DBLocation = "C:\Documents and Settings\All
Users\CBDApps\Settings\BP\BPLINK.MDB"
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase(DBLocation)
strselect = "SELECT FUND_ID, LEFT([SCCN],2), PROMOTION_DESC,
TOTAL_AMOUNT, " & _
"AMOUNT_PAID FROM SR2R_FORECAST_HDRS " & _
"WHERE ACCT_GRP_CODE = '" & mycode & "' AND PERF_PERIOD_START
=#7/1/" & myyr & "# AND " & _
"PERF_PERIOD_END <=#6/30/" & myyr + 1 & "# ORDER BY LEFT([SCCN],2)"

Set rs = db.OpenRecordset(strselect)
Sheets("BDFAvailable").Range("a7").CopyFromRecords et rs

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
end sub

The error occurs when it hits the "Set rs line" above.
It is looking to extract from the D drive, where it used to reside prior
to the new computer exchange. There are no known linkages in the
worksheet itself. I thought I might have had an old ms query in the
workbook, so I copied sheets over and renamed ranges in those
worksheets.

Tom



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!