I have narrowed the problem down.
The problem:
Opening a recordset using the instruction below causes a failure refreshing
linked tables, linked to csv file, and queries related to these tables.
Set rst = qdfTemp.OpenRecordset()
Any suggestions would be greatly appreciated.
"Kevin N" wrote:
[Quoted Text] > I am having problems with corruption of a Data Query in an Excel workbook
> which works fine until I run the routine below which causes a
> ‘[Microsoft][ODBC Misrosoft Access Driver] The text file specification ‘…’
> does not exist. You can not import, export or link using the specification.’
> error message when I try to refresh data queries in other worksheets. When I
> look in the Access MSysIMEXSpecs hidden system table there is a record ‘…’
> relating to the supposedly missing text file specification! The routine was
> written to import data from a crosstab query with inconsistent field names,
> works fine and is shown below.
>
> Sub forecastimport()
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
> Const qryname = "q_mycrosstab"
> Const dbsname = "mypath\myfile.mdb"
> Const sht = "myworksheet"
>
> Set dbs = OpenDatabase(dbsname)
> ' Open a forward-only-type Recordset object.
> Set rst = dbs.OpenRecordset(qryname, dbOpenForwardOnly)
>
> 'Copy data onto worksheet
> With Worksheets(sht)
> .Cells.Clear
> 'Set-up headers
> For iCols = 0 To rst.Fields.Count - 1
> .Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
> Next
> 'Copy records
> .Range("A2").CopyFromRecordset rst
> End With
>
> 'Clean up
> rst.Close
> dbs.Close
> Set dbs = Nothing
> Set rst = Nothing
> End Sub
>
> I am using Office 2003.
>
> Do you have any ideas or suggestions?
|