Old Code

[DBL]

I'm using the current code in a function. It was written in Access 2000 and I wondered if there was anyway to update it so it can run in Access 2010 without having to specify the old reference.

Public Function ApplianceList(WOProp As Integer) As String



Dim strSQL1 As String
Dim Answer As String
Dim i As Integer
Dim rs As New ADODB.Recordset

'"SELECT qryCausesOfDeath.CDCase, qryCausesOfDeath.CDOrder, qryCausesofDeath.CDCause "
strSQL1 = "SELECT qryApplianceList.PropAppProp, qryApplianceList.PropAppDesc " _
& " FROM qryApplianceList " _
& " WHERE qryApplianceList.PropAppProp = " & WOProp _
& " ORDER BY qryApplianceList.PropAppID;"

rs.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount >= 1 Then
rs.MoveFirst

For i = 1 To rs.RecordCount
Answer = Answer & rs!PropAppDesc & " "
rs.MoveNext
Next i

ApplianceList = Answer



End If

End Function

I use the code to loop through a filtered list of records and to put all the records into a string for reporting purposes.

Thanks

D

 

[MarkK]

What is the 'old reference' you refer to? It looks like this code will require a reference to 'Microsoft ActiveX Data Objects' if that is what you mean. Also, you could tidy it up a little like ...

Public Function ApplianceList(WOProp As Integer) As String
   Dim sql As String
   Dim tmp As String
   
   sql = _
      "SELECT PropAppProp, PropAppDesc " & _
      "FROM qryApplianceList " & _
      "WHERE PropAppProp = " & WOProp & " " & _
      "ORDER BY PropAppID;"
   
   With New ADODB.Recordset
      .Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      Do While Not .EOF
         tmp = tmp & rs!PropAppDesc & " "
         .MoveNext
      Loop
      .Close
   End With
   
   ApplianceList = tmp

End Function

You don't need to MoveFirst when you open a recordset, but you might need to MoveLast before getting an accurate recordcount. It's a little safer to check for end-of-file.
Cheers,

 

[DBL]

Re: Old Cod

Thanks, I'll make that change.

yes, it's the Microsoft ActiveX Data Objects that I'm having to reference. If I'm working on Access 2010 but the client is using 2003 then the reference isn't working for them and I have to reset it to an older version. Is there any way to late bind this - like the Office objects - so I don't have to tick the correct reference?

 

[boblarson]

Re: Old Cod

ADO references are Windows specific. So, Windows 2000 installed 2.5 (ADO is part of the MDAC) and it went to 2.6 for WinXP, 2.7 for Vista, and 2.8 for Windows 7 and there is a 6.0 which deals with ACCDB files and can't be used on anything prior to Windows Vista (I believe - it could be Windows 7 but I think it is Vista).

 

[MarkK]

Yeah, to late bind you can do this ...

Sub Test()
   Dim rs As Object
   Set rs = CreateObject("ADODB.Recordset")
   rs.Open "SELECT * FROM tTestData", CurrentProject.AccessConnection
End Sub

... but I expect you can find an ADODB reference on your machine that also exists on your customers'. I have Windows 7 that I upgraded from Vista and I have ADODB 2.0, 2.1, 2.5, 2.6, 2.7, 2.8.
So all those versions must ship with Vista and/or W7.

 

[boblarson]

Yes, but you need to set the lowest common one for which OS the user might be on. So if you know they are on XP you would set to 2.6. But setting to 2.5 would be fine for most things. You lose some functionality of later versions obviously but I'm not even sure what changes are in each version.