OpenRecordset + dbOpenSnapshot works on a table but not a query

[Mithril28]

I thought this was supposed to work on both?

The database is not split and is all in one file

Access:
32 bit
Version 14.0.6023.1000

OS:
Microsoft Windows Server 2008 R2 Standard
Version 6.1.7601 Service Pack 1 Build 7601




Code:
Function Wrap_Detail()
'CallingRoutine As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim WF As Integer


Set db = CurrentDb


WF = FreeFile 'get the next free handle


If CallingRoutine = 1 Then
strSQL = "Select * From [Community_Detail1]"
Close #WF '<<--- don't really need this line
'Open the Text file for writing.
strFileName = "CityDetail.txt"
' Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #1
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #WF 'write to file
ElseIf CallingRoutine = 2 Then
strSQL = "Select * From [Community_Detail2]"
Close #WF '<<--- don't really need this line
'Open the Text file for writing.
strFileName = "CityDetail.txt"
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #WF
End If




Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
If .RecordCount = 0 Then
Print #WF, "Record Count = " & rs.RecordCount


Else
Print #WF, "Record Count = " & rs.RecordCount
End If
End With


With rs
Do While Not rs.EOF
Print #WF, rs.Fields(0)
rs.MoveNext '<<<---- necessary in recordset loops
Loop
End With


rs.Close 'close recordset


Close #WF 'Close text file.


Set rs = Nothing
Set db = Nothing


End Function

[ranman256]

it works fine. But dont use DAO. Access is already a database ,no need to create a db in a db.
Just attach the external data as a table and run a normal query.

[Mithril28]

I should have said that I didn't encounter any errors using the code above, I just didn't get any data unless I converted the data coming out of the query into the table and pulled from the table

I deleted the references to the db (see below) and got an error message when I ran it through the debugger: "Sub or Function not defined", which was in reference to "OpenRecordset"

Also, there is no external data, if I understand correctly, the query is in the db.

Function Wrap_Detail()
'CallingRoutine As Integer
Dim rs As Recordset
Dim strSQL As String
Dim WF As Integer


WF = FreeFile 'get the next free handle


If CallingRoutine = 1 Then
strSQL = "Select * From [Community_Detail1]"
Close #WF '<<--- don't really need this line


strFileName = "CityDetail.txt"
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #WF
ElseIf CallingRoutine = 2 Then
strSQL = "Select * From [Community_Detail2]"
Close #WF '<<--- don't really need this line
strFileName = "CityDetail.txt"
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #WF
End If


Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
If .RecordCount = 0 Then
Print #WF, "Record Count = " & rs.RecordCount
Else
Print #WF, "Record Count = " & rs.RecordCount
End If
End With


With rs
Do While Not rs.EOF
Print #WF, rs.Fields(0)
rs.MoveNext '<<<---- necessary in recordset loops
Loop
End With


rs.Close 'close recordset
Close #WF 'Close text file.
Set rs = Nothing
Set db = Nothing
End Function

[ssanfu]

I modified your code a little - mostly re-arranging lines.

I tried both a table and a query. Both wrote to the text file.... no errors.

Here is the code:

Function Wrap_Detail(pCallingRoutine As Integer)
    Dim rs As Recordset
    Dim strSQL As String
    Dim WF As Integer
    Dim strFileName As String


    WF = FreeFile    'get the next free handle

    strFileName = "CityDetail.txt"

    Close #WF    '<<--- don't really need this line

    Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #WF
    '    Open "f:\forum\" & strFileName For Output As #WF

    If pCallingRoutine = 1 Then
        strSQL = "Select * From [Community_Detail1]"
        '        strSQL = "Select * From [group]"
    ElseIf pCallingRoutine = 2 Then
        strSQL = "Select * From [Community_Detail2]"
        '        strSQL = "Select * From [qrylocation]"
    End If

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rs
        If .RecordCount = 0 Then
            Print #WF, "Record Count = " & rs.RecordCount
        Else
            rs.MoveLast
            Print #WF, "Record Count = " & rs.RecordCount
            rs.MoveFirst
        End If
    End With

    With rs
        Do While Not rs.EOF
            Print #WF, rs.Fields(0)
            rs.MoveNext    '<<<---- necessary in recordset loops
        Loop
    End With


    Close #WF    'Close text file.
    rs.Close    'close recordset
    Set rs = Nothing

End Function

This is how I call the function from a button click event:

Private Sub test12_Click()
    Dim x

    x = Wrap_Detail(1)

'    x = Wrap_Detail(2)

End Sub

I left my testing lines in - they can be deleted.


BTW, the following two lines should be at the top of every module:

Option Compare Database
Option Explicit

[Mithril28]

OK! When I used your code, and changed "If .RecordCount = 0" to "If rs.RecordCount = 0" then I get data from the code. Thank you!!

If writes to the file just fine when I run the program from the debugger, but when I use the code that is in place in the macros it still doesn't work. But! Now that I am getting data I can dink around with it some more. The problem I detailed in this post has been resolved, so if I still can't get it to work I'll open another.

Thank you so much!!!