Upgrading Access Database to 2007 issue

[mtm70]

I am have upgraded my Access database to 2007. It is working fine with the exception of code which imports specific fields out of an excel file into several tables in the database.

This worked fine in 2003 but after the upgrade, I get a 'Compile - Method or Data Member not found'

I am sure there is an easy solution and would like some help. The code is below:

Private Sub b1_Click()
  On Error GoTo e1
 
  Me!zz = Null
 
  'ensure the zip file is not being modified
  Set fso = New FileSystemObject
 
  hold_import_path = "Q:\Biz\Administration\File Exports Imports\"
  hold_import_name = "Base Report for All.xlsx"
 
  If Not fso.FileExists(hold_import_path & hold_import_name) Then
     mess = "Sorry I cannot find the file"
     mess = mess & crlf & crlf & hold_import_path & hold_import_name
     MsgBox mess, 0, "File not found"
     Set fso = Nothing
     Exit Sub
  End If
  Set fso = Nothing
 
  'open the excel sheet
  Set xlapp = New Excel.Application
  Set xlworkbook = xlapp.Workbooks.Open(hold_import_path & hold_import_name)
  Set xlworksheet = xlworkbook.Worksheets(1)   '("sheet_name")
  screen_pos = 2   'position on excel sheet to start reading
 
  While Len(xlworksheet.Range("a" & Format(screen_pos))) > 0
    hold = xlworksheet.Range("a" & Format(screen_pos))
    If IsNumeric(hold) Then
       '[Employee_PVE] table
       hold_crit = "select * from [Employee_PVE] where [Employee_PVE].[Emp Id] = " & CLng(hold)
       Set curr_rs = curr_db.OpenRecordset(hold_crit)
       If curr_rs.RecordCount > 0 Then
          curr_rs.Edit
       End If
       If curr_rs.RecordCount = 0 Then
          curr_rs.AddNew
          curr_rs![Emp ID] = xlworksheet.Range("a" & Format(screen_pos))
       End If
       curr_rs![Name] = xlworksheet.Range("b" & Format(screen_pos))
       curr_rs![Division] = xlworksheet.Range("c" & Format(screen_pos))
       curr_rs![Team] = xlworksheet.Range("d" & Format(screen_pos))
       curr_rs![Location] = xlworksheet.Range("e" & Format(screen_pos))
       curr_rs![Occupational Category] = xlworksheet.Range("f" & Format(screen_pos))
       curr_rs![Gender] = xlworksheet.Range("g" & Format(screen_pos))
       curr_rs![Date Started] = xlworksheet.Range("h" & Format(screen_pos))
     curr_rs.Update
       curr_rs.Close
 

[Stewart Ross]

In the VBA IDE window check that the library references include the Microsoft Scripting Runtime (used for variable fso, a FileSystemObject) and the Microsoft Excel 12.0 library (used by the xlApp Excel application variable and the other Excel-related variables in the sub).

Select Tools, References from the VBA IDE menu and ensure that these two libraries are ticked.

It would be helpful to know which line is throwing the error if you find continued problems.

-Stewart

[NeoPa]

Please see When Posting (VBA or SQL) Code. Posting questions properly helps save your time as well as ours. You will find there some hints that will not only help with posting but may also help you generally.

Another link that might prove useful for this question is Converting to Access 2007 or 2010.

Good luck :-)

[mtm70]

Hi Guys,

Thanks for your help.

Unfortunately, these libraries were already ticked on.

The line that it errors out on is [curr_rs.Edit].

Any other ideas?

[NeoPa]

I take that to mean your error occurs on line #34. It's easier to specify it that way, and easier to find obviously. It's one of the benefits of using the [ CODE ] tags when posting code. All concerned benefit.

PS. What did you find when you checked the second article linked in my earlier post? It generally helps to comment on what you find that's relevant, so as to ensure we all know what you've discovered. Otherwise it seems a little like you didn't even check (which, of course, would effect our attitude when dealing with you in future). I'm sure you appreciate why it makes sense to comment on what is posted.

[Stewart Ross]

Your sub does not have Dim statements for any of the variables concerned. Either they are not being explicitly defined (which is not good practice) or they are global to the code module in which the sub is located (again, not good practice).

I would suggest you should explicitly define all the variables within the sub itself. If you place an Option Explicit directive at the top of your module then compile the module the VBA IDE will identify for you all instances of undeclared variables within the module.

The point of this is that there is more than one kind of recordset available in Access - DAO (which is the built-in recordset type) and ADO. They have different methods and modes of use.

To ensure that your VBA sub is using the correct type of recordset (DAO in this case), place the following declaration statement after the header line of your sub:

Dim curr_rs as DAO.Recordset

This at least will rule out the variable type as the source of your error.

-Stewart