Index and Seek

[Brad]

Thanks for taking the time to read my question.

I'm using Access97 and I can't get my code to work.

If I use the code below as is, it stalls on the .Index line.

If I take that out, it stalls on the first .Seek line.

As I understand it, you need to tell Access what the index is so that it can
seek. I've changed Set rsttable = dbs.OpenRecordset("tblMill") to Set
rsttable = dbs.OpenRecordset("tblMill", dbOpenTable) but then it stalls there.

How can I get this to work?

Thanks for your help.

Brad

This is the first part of the code. I realize I'm missing some stuff at the
end like end if's etc.
code:

Dim dbs As Database, rstquery As Recordset, rsttable As Recordset, qdf As
QueryDef
Dim TheFileAndPath, LastMillName As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryGaryExportIngredSS")
Set rstquery = qdf.OpenRecordset()
Set rsttable = dbs.OpenRecordset("tblMill")

With rsttable
.Index = "MillsID"
End With

On Error GoTo cmdExportDataToExcel_Err

If Right(Me.TxtSaveAsFileName, 4) <> ".txt" Then
MsgBox "You are trying to export a file with an incorrect file
extension. Please make sure that the file extension is .csv and try again.",
48
cmdEditTxtSaveAsFileName_Click
Exit Sub
End If

If Right(Forms!frmReportGenerator!DefaultFolder, 1) = "\" Then
TheFileAndPath = Forms!frmReportGenerator!DefaultFolder &
Forms!frmReportGenerator!TxtSaveAsFileName
Else
TheFileAndPath = Forms!frmReportGenerator!DefaultFolder & "\" &
Forms!frmReportGenerator!TxtSaveAsFileName
End If

If Not rstquery.EOF Then
rstquery.MoveFirst

rsttable.Seek "=", rstquery!MillName

Open TheFileAndPath For Output As #1 ' Open file for output.
Print #1, rsttable!TextFileHeader ' Print text to file.


LastMillName = rstquery!MillName

Do Until rstquery.EOF
If LastMillName <> rstquery!MillName Then
rsttable.Seek "=", rstquery!MillName
Print #1, rsttable!TextFileHeader
End If

Print #1, rstquery!ExportField

LastMillName = rstquery!MillName

rstquery.MoveNext
Loop

Close #1 ' Close file.

 

[John Vinson]

Thanks for taking the time to read my question.

I'm using Access97 and I can't get my code to work.

If I use the code below as is, it stalls on the .Index line.

If I take that out, it stalls on the first .Seek line.

As I understand it, you need to tell Access what the index is so that it can
seek. I've changed Set rsttable = dbs.OpenRecordset("tblMill") to Set
rsttable = dbs.OpenRecordset("tblMill", dbOpenTable) but then it stalls there.

My guess is that it's a linked table: the Seek method works only on
*local* tables within the same .mdb file.

Use the FindFirst method instead. With Access97 the speed advantage of
the Seek method is usually trivial. The syntax of FindFirst is
different; you don't need to (cannot in fact) specify an index;
instead you use a SQL WHERE clause without the word WHERE:

Set rstTable = dbs.OpenRecordset("tblMill", dbOpenDynaset)
' or dbOpenSnapshot if you want this read only
rstTable.FindFirst "MillsID = '" & MillName & "'"


John W. Vinson[MVP]