Data Query - Text file specification does not exist



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)
'Set-up headers
For iCols = 0 To rst.Fields.Count - 1
.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
'Copy records
.Range("A2").CopyFromRecordset rst
End With

'Clean up
Set dbs = Nothing
Set rst = Nothing
End Sub

Do you have any ideas or suggestions?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question