Module no longer working when migrated to an accdb

[mattpar]

Okay, I'm currently stumped on a coding issue I have. Right now I am migrating a database from an mdb to an accdb and an important bit of code now no longer works. essentially what the code does is take data from fields in 2 separate table, adds file extensions to them and then combines this info (comma separated) back into one of the tables.

The issue I'm having is that when I run the code on the new accdb it stops and brings up an error message; Compile error: method or data member not found. and it then highlights the first instance of the .edit command in the code.

So I would really appreciate if you guys could help me figure out why this isn't working any more (and why it worked in an mdb and not an accdb). And hopefully help me with a solution to this issue. Here is the code, minus extraneous update queries that run at the end.

Option Compare Database
Option Explicit

Public Function raweditmerge() As Boolean
'This code is for when the field has no DB and does editing and submittal on the PDA

Dim MyTable As String
Dim db As database
Dim rs As Recordset
Dim strsql As String
Dim strsql2 As String
Dim strsql3 As String
Dim strColumn1 As String
Dim strColumn2 As String
Dim strColumn3 As String
Dim strColumn4 As String

On Error Resume Next

'Then we tun off the warnings
DoCmd.SetWarnings False

'This query adds the xyz files into the dataset table
strsql = "UPDATE Data_Editing_Table, Dataset_Table SET Dataset_Table.XYZ_Filenames = [Data_Editing_Table].[XYZ_Filenames] WHERE (((Dataset_Table.Dataset_ID)=[Data_Editing_Table].[Dataset_ID]))"

DoCmd.RunSQL strsql

'sets current database and recordset
MyTable = "Dataset_Table"

Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable, dbOpenDynaset)

'now we start combining the Raw and xyz filenames into the one field and add their extensions
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
strColumn1 = rs!Raw_Filenames
strColumn2 = rs!XYZ_Filenames
strColumn3 = rs!Dataset_ID
strColumn4 = rs!Repeatability_ID

'This Section actually inputs the Filenames
'rs.MoveNext
Do Until rs.EOF
rs.Edit

strColumn1 = rs!Raw_Filenames
strColumn2 = rs!XYZ_Filenames
strColumn3 = rs!Dataset_ID
strColumn4 = rs!Repeatability_ID

If strColumn3 = rs!Dataset_ID Then
'it first tells it to associate by dataset_id
If rs!Redo_Collection = 0 Then
strColumn1 = Replace(strColumn1, ",", ".m61,")
strColumn2 = Replace(strColumn2, ",", ".xyz,")
'Update my Temp field
rs!Temp = strColumn1 & ".m61, " & strColumn4 & ".m61, " & strColumn2 & ".xyz"
rs.Update
End If

'Rest all the strColumn's to null
strColumn1 = ""
strColumn2 = ""
strColumn3 = ""
strColumn4 = ""


Else
If rs!Redo_Collection = 0 Then
strColumn1 = Replace(strColumn1, ",", ".m61,")
strColumn2 = Replace(strColumn2, ",", ".xyz,")
'Update my Temp field
rs!Temp = strColumn1 & ".m61, " & strColumn4 & ".m61, " & strColumn2 & ".xyz"
rs.Update
End If
End If
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
Set db = Nothing

'This query then adds the fields from the temp column into the editing table
strsql2 = "UPDATE Dataset_Table, Data_Editing_Table SET Data_Editing_Table.RawEditFiles = [Dataset_Table].[Temp] WHERE (((Data_Editing_Table.Dataset_ID)=[Dataset_Table].[Dataset_ID]) AND ((Dataset_Table.Temp) Is Not Null))"
'This query clears out the temp column
strsql3 = "UPDATE Dataset_Table SET Dataset_Table.Temp = ''"

DoCmd.RunSQL strsql2
DoCmd.RunSQL strsql3

'Turns warngins back on
DoCmd.SetWarnings True

End Function


-Thanks in advance for any help on this.

 

[dhookom]

Try to change your code to be more explicit:

Dim db As DAO.database
Dim rs As DAO.Recordset

You may need to set a reference to the DAO library if this still causes issues.

Duane
Hook'D on Access
MS Access MVP

 

[mattpar]

Thank you very much dhookom that solved it. though I still find it odd that the code worked when in an mdb but suddenly not when moved to an accdb. Oh well, it's fixed and again thank you.

 

[dhookom]

The issue is that many objects such as recordsets can be DAO or ADO. Your code chooses the first registered object type which in your case is ADO which doesn't support the .edit command. You can either change the order of the references or better yet be explicit.

Duane
Hook'D on Access
MS Access MVP

 

[mattpar]

Thanks for the advice, I'll keep that in mind as I go through the rest of this DB.