Access 2010: Error 2105

[djsmith]

I have a database that was created in Access 2003 and has since been upgraded to 2007 and now is being used in 2010. This database currently resides on SharePoint where it is download, updated and published back to SharePoint. The switch to Office 2010 and the end user getting a 64-bit computer occurred at about the same time. So I don't know which affected the database.

There is a button that filters on the Attorney name and Attorney Billing profile and opens an input form to a new record. Since the upgrade I am getting the following error message: "You can't go to the specified record. You may be at the end of a recordset." I can enter directly into the table without issue.

Macro Single Step
Macro Name: Current_Month_FORM.OnOpen
No Condition
Action Name: GoToRecord
Arguments:
-1, ,New,
Stop All Macros
Error Number 2105

No changes have been made to the macros, before the upgrade they worked. We other versions of the same database that are not on SharePoint, they work without issue in Access 2007 and 2010.

Any assistance will be greatly appreciated.

[marcnz]

Hi,

Not sure if this could help but the first thing I would check when moving from one version of MS Access to another are the References in the VBA Editor.

Moving from 2007 to 2010 might end up with some missing references that are still pointing to the older 2007 libraries. If any, they will show a capital "MISSING" and will need to be unchecked and replaced by the new references.

If this is not the case, I have no other solution for the time being.

Thanks
Marc

[djsmith]

Thank you, I have looked at the references and those are fine. I am been trying to work through the issue using a known working version of the database. It seems to work fine until I import the tables. I am going to try to delete the content of the tables and replace with the content of the non working tables. I hope that will do the trick. Otherwise, I am running out of options.

[tgw7078]

Hi DJ,
SharePoint is not being used when the database is opened, correct? In other words, you are not linking to any SharePoint lists?

Do you really need to open your form with all records in the recordset? This is essentially what this macro does. If you only need to open the form to a new record, then I would try eliminating the macro from the On Open event of the form, as shown on the Event tab for Form properties. Instead, select the Data tab. Then set the Data Entry property from No to Yes. This will allow the form to open to a new record, but the recordset will not include previously saved records.

[djsmith]

The database when opened from SharePoint, saves a local copy of the database. When entries are complete the "Publish to SharePoint" button updates the copy on SharePoint.

Unfortunately, I do need the .onopen macro. It runs several tests (Sub Macros) to determine the name of the person the entry will be made by and record that into the table. It also selects and enters the billing profile based on that name.

[djsmith]

Just this morning, I replaced the data in the tables of a known working version of this database and that worked. With this process I used the original table structures. I discovered that someone had added fields to one of the tables and created a query. When I replaced that table with the original table and removed the query--everything worked. They had been running the database that way for over a year, but something in the update did not like it.

Thanks for your suggestions.

[tgw7078]

I, personally, would not use SharePoint in this manner for an Access application. I might be tempted to implement Microsoft's idea of a web-based Access application, where the data is stored directly in SharePoint, in lists, and the presentation layer is browser based. But it would have to be a relatively simple application for me to even be tempted to go down this road.

If all of your users are on a Local Area Network (LAN), there should be no reason to use SharePoint at all. If you need Wide Area Network (WAN) access, then I would go with a redesign that uses SQL Server as the back-end database. We have just such a database at my place of work (Access FE with SQL Server BE, with WAN-based users). It works great, even on fairly slow connections. However, we had to do a total re-architecture of the data access methods, when we converted from a .mdb BE to SQL Server. There are no linked ODBC tables in the re-designed application.

The only macros you should ever need would be an Autoexec macro, and perhaps an Autokeys macro. All other tasks could very likely be done a lot more efficiently with VBA code, and proper error trapping. It was not possible to implement error trapping in macros, in Access 2003. You can do so with Access 2007 and higher, but I wonder if anyone took the time to add this, when the conversions took place.

[djsmith]

Thank you tgw7078 for your suggestions. I will definitely look into revamping this database. Especially since the end user wants to create more tables and reports than previously provided. I am learning to use VBA (hands-on training) and agree that running VBA is more efficient than Macros.

The reason we moved this database to SharePoint was that we had individuals who use Macs and cannot connect to our server. They can connect to SharePoint.

[WendellB]

I agree with Tom that if you can avoid SharePoint for this kind of application you are way ahead of the game. We design all our applications with an Access front-end, and a SQL Server back-end, but use ODBC linked tables rather than the .ADP approach, which Microsoft appears to no longer be enhancing. Also, you might want to look into using Terminal Services for your Mac users - we have some users who have done that very successfully.

[tgw7078]

The reason we moved this database to SharePoint was that we had individuals who use Macs and cannot connect to our server. They can connect to SharePoint.

Your Mac folks would have to be using a built in Windows emulation, in order to even open up a .mdb/.accdb based application. I have very little--and I truly mean very little--experience using Macs. However, I do know that one application I have provided significant help for, in the past, is run in the type of environment that you describe. This is for a non-profit agency that deals with pairing animals that need homes with people. Their office originally used Windows-based desktop PCs, but when these older machines were refreshed, they brought in Macs instead. Their network support person was able to properly configure the emulated Windows within the Macs to use the network file share, so this should be possible in your case.

Earlier, you wrote:

Just this morning, I replaced the data in the tables of a known working version of this database and that worked.

Now, I'm left wondering if your current setup, with McIntosh machines accessing this file from SharePoint, might (?) somehow be contributing to index corruption in the tables. This is just a WAG on my part...

If you have SharePoint 2010, and Access 2010 for the development machine, you might have a valid business case with a mixed environment of Windows-based PCs and McIntosh PCs to go ahead and try the browser-based application that Access 2010 is capable of creating. However, you cannot use any VBA code in the portion of the application that will be browser based; you will need to accomplish everything using the macro editor in Access 2010. This is admittedly a lot more powerful than the classic macros you are used to using with Access 2003. It is also out of my area of expertise, so I cannot advise much further than to say that it should be possible.

[tgw7078]

@Wendell - We also avoided the .ADP approach, for the application I mentioned. We used good 'ole Access 2003 for the FE .mdb application. All forms are unbound forms, and we use ADO code to retrieve recordsets and to write data back to the SQL Server. It was a major re-write of the data access methods, versus the previous linked tables to a .mdb BE file. But, it seems to be very fast even from painfully slow connections. The SQL Server is located in Seattle. I have personally tested adding/editing data from three remote locations (all at airports): Yuma, AZ, Nagoya, Japan, and Tokyo, Japan.