Moving AccessDB to Windows Server 2008 R2 has broken database connection

[Mithril28]

Our AccessDB used to be on Windows Server 2003 R2, and it has been moved to Windows Server 2008 R2.
Access is 2010 32 bit.

The code (see below) worked before and now it doesn't. Does anyone have any idea what I can do to make this work? Thank you!!
P.S. I've modified it so that it doesn't do much, I have just been trying to get it to return rows.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Function Wrap_Detail()
'CallingRoutine As Integer


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
If CallingRoutine = 1 Then
strSQL = "Select * From [Community_Detail1]"
Close #1
'Open the Text file for writing.
strFileName = "CityDetail.txt"
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #1
ElseIf CallingRoutine = 2 Then
strSQL = "Select * From [Community_Detail2]"
Close #1
'Open the Text file for writing.
strFileName = "CityDetail.txt"
Open "\\Server\shared$\Information Technology\NAS ITDept\" & strFileName For Output As #1
End If


Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)




With rs


If .RecordCount = 0 Then
Print #1, "Record Count = " & rs.RecordCount
Else
Print #1, "Record Count = " & rs.RecordCount
End If
End With




With rs
Do While Not rs.EOF
Loop
End With
Close #1 'Close file.
End Function

[ssanfu]

Is the dB split?
If yes, is the path to the BE identical?
If no, did you relink the FE to the BE?

I modified your code = I added or changed lines in BLUE

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

[Mithril28]

Thanks for the updated code, it's better written. Unfortunately it did not help. Re your questions: nothing has changed except the db was moved from Windows Server 2003 to Windows Server 2008. Could it have to do with the references, that they were not properly configured after the move?

[InsuranceGuy]

I'm not clear on what's broken yet.

Mithril, is the code failing with an error code? Where is it failing? Does it execute but not output to your intentions?

Thanks,

Jeff

[Mithril28]

It executes, but the output file is blank except for the line "Record Count = 0" that was inserted in the first "with" statement. To me that means the there is something wrong with the database connection but I don't know what it is.
Does it matter that the properties are for "Database2"? Thank you for your input!!

[Mithril28]

To be more clear, the only thing I'm after here is a record count of the number of rows in the tables above. I have verified that each table has about 100 rows.

[Mithril28]

And now I've confused myself even further. When I change the query from
strSQL = "Select * From [Community_Detail1]"
to
strSQL = "Select count(*) From [Community_Detail1]"
I get the result: Record Count = 1. So the problem getting the record count for the "select * etc..." couldn't be the db connection. Any suggestions welcome!

[ItsMe]

What is the file extension on your Access file? Is it .adp?

[Mithril28]

accdb. Thanks!

[ItsMe]

I am still not clear about how the DB is split and other questions that have already been asked.

If the problem is querying a table, this problem seems to be solved. Is there another issue?

How many files do you have? Is there a file that resides on the Server and several files that act as front end files, front end files on other computers?

[ssanfu]

Once again:

Is the dB split?

If yes, is the path to the BE identical?

If no, did you relink the FE to the BE?

[Mithril28]

I don't think it is split, but I will describe it for you. It is a single access database (accdb) with tables, queries, macros, a module1 and an autoexec, no outside files, or anything located outside this one file.
When the code is run it uses "Set db = CurrentDb" so that it access the data in its' own database.

I don't think there is a path to the backend in that case is there? It's self-contained?

It is supposed to be querying a query, not a table, but no rows are returned in that case, I get a "RecordCount = 0" (Print #WF, "Record Count = " & rs.RecordCount)

[ItsMe]

I don't think there is a path to the backend in that case is there? It's self-contained?

If you only have one file, you do not have a split DB. It does not seem you have any problems and everything is working correctly. Let us know if you run into trouble later.

[ssanfu]

So "Community_Detail1" and "Community_Detail2" are saved queries?

If you open (execute) "Community_Detail1", are records returned?

[InsuranceGuy]

strSQL = "Select count(*) From [Community_Detail1]" 

Use this, then write rs.fields(0) to the file instead of rs.recordcount. I always let the DB engine do what it's best at.

Jeff