By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Upgrading Access Database to 2007 issue

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub b1_Click()
  2.   On Error GoTo e1
  4.   Me!zz = Null
  6.   'ensure the zip file is not being modified
  7.   Set fso = New FileSystemObject
  9.   hold_import_path = "Q:\Biz\Administration\File Exports Imports\"
  10.   hold_import_name = "Base Report for All.xlsx"
  12.   If Not fso.FileExists(hold_import_path & hold_import_name) Then
  13.      mess = "Sorry I cannot find the file"
  14.      mess = mess & crlf & crlf & hold_import_path & hold_import_name
  15.      MsgBox mess, 0, "File not found"
  16.      Set fso = Nothing
  17.      Exit Sub
  18.   End If
  19.   Set fso = Nothing
  21.   'open the excel sheet
  22.   Set xlapp = New Excel.Application
  23.   Set xlworkbook = xlapp.Workbooks.Open(hold_import_path & hold_import_name)
  24.   Set xlworksheet = xlworkbook.Worksheets(1)   '("sheet_name")
  25.   screen_pos = 2   'position on excel sheet to start reading
  27.   While Len(xlworksheet.Range("a" & Format(screen_pos))) > 0
  28.     hold = xlworksheet.Range("a" & Format(screen_pos))
  29.     If IsNumeric(hold) Then
  30.        '[Employee_PVE] table
  31.        hold_crit = "select * from [Employee_PVE] where [Employee_PVE].[Emp Id] = " & CLng(hold)
  32.        Set curr_rs = curr_db.OpenRecordset(hold_crit)
  33.        If curr_rs.RecordCount > 0 Then
  34.           curr_rs.Edit
  35.        End If
  36.        If curr_rs.RecordCount = 0 Then
  37.           curr_rs.AddNew
  38.           curr_rs![Emp ID] = xlworksheet.Range("a" & Format(screen_pos))
  39.        End If
  40.        curr_rs![Name] = xlworksheet.Range("b" & Format(screen_pos))
  41.        curr_rs![Division] = xlworksheet.Range("c" & Format(screen_pos))
  42.        curr_rs![Team] = xlworksheet.Range("d" & Format(screen_pos))
  43.        curr_rs![Location] = xlworksheet.Range("e" & Format(screen_pos))
  44.        curr_rs![Occupational Category] = xlworksheet.Range("f" & Format(screen_pos))
  45.        curr_rs![Gender] = xlworksheet.Range("g" & Format(screen_pos))
  46.        curr_rs![Date Started] = xlworksheet.Range("h" & Format(screen_pos))
  47.      curr_rs.Update
  48.        curr_rs.Close
Jan 10 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 2.5K+
P: 2,545
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.

Jan 10 '12 #2

Expert Mod 15k+
P: 31,494
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 :-)
Jan 10 '12 #3

P: 2
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?
Jan 10 '12 #4

Expert Mod 15k+
P: 31,494
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.
Jan 10 '12 #5

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Dim curr_rs as DAO.Recordset
This at least will rule out the variable type as the source of your error.

Jan 11 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.