Compile Error ??

[NEWER USER]

I used some code from Allen B and it works in Access 2K3. However, it will
not compile in 2K7. It fails at .Edit. Method or data member not found.
What do I need to change/alter to conform to 2K7 standards?

Function UpdateGroupAlias()
On Error GoTo UpdateGroupAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngMakeID As Long
Dim bytMaxColumns As Byte
Dim pbytNumColumns As Byte
Dim db As Database
Dim rs As Recordset

strSQL = "Delete * from tblGroupAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL "INSERT INTO tblGroupAlias ( MakeID, GroupID ) " & vbCrLf & _
"SELECT qryxCatalog1.CodeID, qryxCatalog1.GroupID " & vbCrLf & _
"FROM qryxCatalog1 " & vbCrLf & _
"ORDER BY qryxCatalog1.CodeID, qryxCatalog1.GroupID;"
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblGroupAlias") 'table used to redefine/alias
the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngMakeID = !MakeID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !MakeID = lngMakeID
.Edit ' *****Fails at this line
!Level = btLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateGroupAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateGroupAlias_Err:
Select Case Err
Case Else
UpdateGroupAlias = Err.Number
Resume UpdateGroupAlias_Exit
End Select

End Function

 

[Dirk Goldgar]

I used some code from Allen B and it works in Access 2K3. However, it will
not compile in 2K7. It fails at .Edit. Method or data member not found.
What do I need to change/alter to conform to 2K7 standards?

Function UpdateGroupAlias()
On Error GoTo UpdateGroupAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngMakeID As Long
Dim bytMaxColumns As Byte
Dim pbytNumColumns As Byte
Dim db As Database
Dim rs As Recordset

strSQL = "Delete * from tblGroupAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL "INSERT INTO tblGroupAlias ( MakeID, GroupID ) " & vbCrLf &
_
"SELECT qryxCatalog1.CodeID, qryxCatalog1.GroupID " & vbCrLf & _
"FROM qryxCatalog1 " & vbCrLf & _
"ORDER BY qryxCatalog1.CodeID, qryxCatalog1.GroupID;"
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblGroupAlias") 'table used to redefine/alias
the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngMakeID = !MakeID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !MakeID = lngMakeID
.Edit ' *****Fails at this line
!Level = btLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateGroupAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateGroupAlias_Err:
Select Case Err
Case Else
UpdateGroupAlias = Err.Number
Resume UpdateGroupAlias_Exit
End Select

End Function

Probably, in the A2K7 database you have a reference set to ActiveX Data
Objects (ADO), which has a Recordset object whose methods conflict with
those of the DAO Recordset object that the code you posted is intended to
use. If you aren't using ADO, you can remove that reference: in the VB
Editor, click Tools -> References... and locate the reference to ActiveX
Data Objects 2.x Library, and remove the check mark next to it.

If you are using ADO elsewhere in your application, so you don't want to
remove the ADO reference, you can just qualify object declarations with the
specifier for the library being used:

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

That should take care of it.

 

[NEWER USER]

Thank you so much for the quick response and clear explanation of referencing
databases