Error Querying Access Database From Excel
 

Hi,

Using Excel 2000 (9.0.4402 SR-1)

I am querying an access database and the first time I run a query
everything works fine, if I try a second time the query does not run
(creating an error which I capture), I have to close and then re-open
Excel in order to run it again

Anyone have any idea why this happens ...

Below is the code


wFolder = "\\Kels-dsk-001\Apps\Raymond Allan\Access"
sDir = "`\\Kels-dsk-001\Apps\Raymond Allan\Access\Item Branch`"

FamId = Main.mFamId

On Error GoTo Data_Error

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DBQ=" & wFolder & "\Item
Branch.mdb;DefaultDir=" & wFolder & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
_
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Sheet1.`2nd Item Number`, Sheet1.Description,
Sheet1.`Leadtime Level`, " _
, "Sheet1.`Reorder Qty Min`, Sheet1.`Order Multiple`, " _
, "Sheet1.`Safety Stock`, Sheet1.`Standard Cost`,
Sheet1.`Expected Cost` " _
, "FROM " & sDir & ".Sheet1 Sheet1 " _
, "WHERE (Sheet1.`Sales Catalog Section`=" & FamId & ") " _
, "ORDER BY Sheet1.`2nd Item Number`")
.Name = "Item_Branch_Info"
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.RefreshPeriod = 0
.Refresh BackgroundQuery:=False
End With

TIA
Raymond Allan