Corruption of Access database when refresh is done in Excel

[mrlddst]

Good evening,


I'm at my wits end and need some help. I have been maintaining a Access 2000 database for a few years. I recently imported all of the objects into Access 2007. I was able to work through the majority of the issues like broken references and "not a valid bookmark" errors. However, I have been stuck for a few days on the same issue. I have a form that the users go hit a button to perform a series of queries that are appended to a 'make' table. The users have a Excel 2007 workbook setup that is linked using the "Database Query" connection type. When the user refreshes the Excel Workbook and returns to Access and try to close the form, they get "not a valid bookmark" error and the entire database proceeds into total corruption. I am at my wits end, I can't figure out what I am doing wrong. I have two other databases that will allow the refresh while connected to access with no issues. I can't put a primary key on a 'make table' and I don't know what else to do. Any ideas?

[ItsMe]

... I have two other databases that will allow the refresh while connected to access with no issues...

So you are using Excel as a database and linking to Access from Excel? Database Query???? You are linking to a query in Access from Excel? What is the file extension of your Access 2007 data base? MDB or ACCDB?

[mrlddst]

So you are using Excel as a database and linking to Access from Excel? Database Query???? You are linking to a query in Access from Excel? What is the file extension of your Access 2007 data base? MDB or ACCDB?

I am linking to Access from Excel using the "from other connections" and selecting the Microsoft query option. I select the odbc type as ms access. The file extension on the access database is accdb. Could it be that the refresh in Excel is altering something in the Access database and since the "make table" has no primary key then the database corrupts?

[ItsMe]

I have never used Excel to connect to other entities. It sounds as though you have users opening and working inside the Access file that Excel connects to. Perhaps the solution is to not allow anyone to double click on or open the Access file that Excel connects to. Instead, place your Access file on a server and designate it as a Back End file. Give front end files to the users that links to the Access BE on the server. Only allow access to the BE to be via a connection and or link.

You can have up to 20, maybe 25, concurrent users open a single Access file. The preferred methodology (topology) is to use a front end/back end set up and link/connect to the tables. You have offered limited insight so I will guess that a user may be disrupting the Make table process invoked by Excel. Splitting the DB is not a solve all but, it may mitigate the file going exclusive as a user interacts with the file directly.

[mrlddst]

When I was testing the connection, no one other than myself had the access database open. The majority of the tables used in the Access database are linked from an Oracle database. When the database was an mdb file in 2003, the users could refresh the excel pivot table while the database was open. In both versions, excel runs a query from Access. I checked the settings in Access 2007 and I have it set to open as a share. I checked the connection type in the Excel and I don't see anything that sets the mode for the share. I compared it to the other two Access 2007 db that do allow a refresh in excel while access is open. I see no clear difference for why those two can connect and refresh while both access and excel are open. Please let me know if there is any information that I can provide to give you a better scope of the issue.

[ItsMe]

I am not sure either. One thng you might consider is permissions.

I am going to send you a PM.

[ItsMe]

The only other things I can think of would be Trusted Locations and if the Access BE file resides on Windows Server 2012.
There seems to be some issues with Sever 2012 and Access shares.
https://www.accessforums.net/access/...tml#post222577

I should have thought of the following first.

Starting with Access 2007, there are registry settings that need to be adjusted for the machine that opens and runs VBA/Macros. For instance, if a machine is using a local FE file to link to tables on a BE file, that machine's registry settings need to reflect the path to the BE as well as the path to the local FE. There are different Keys for local paths and network paths. Additionally, there are Keys for trusted documents. You would need to adjust the registry to recognize the path to files like Word docs and Excel files that want to run Macros or files that Access wants to Automate.

You can manipulate a machine's registry by going to Reg Edit directly, using VBA, or using the options in Access. Under options in Access, you can go to the "Trust Center" and add paths. I have a sample DB somewhere that works similarly to the options GUI in Access if you are interested in using VBA to edit the registry.