We have a database that has the data stored in SQL, with an Access front end. State of the art, I know...
There is a spreadsheet that pulls data from the database onto a worksheet, and allows the user to search through it (Contacts - user can enter a contact first or last name, or leave it blank for all contacts).
This spreadsheet worked fine until this morning, when I deployed a new version of the database into production. Then, for some unknown reason, there were a handful of contacts entered over the last few days that were not showing up in the spreadsheet. Older contacts were there, but the newer contacts were not.
So I initially double checked to make sure the database I deployed was pointed to the production sql database, not our testing environment. It wasn't. I went in through SQL server and verified the new contacts were in the production database - they were. I went into the front end and verified the data was in the production tables (linked tables to sql server). They were.
But the spreadsheet would not pull them.
After about 2 hours, on a whim I modified the code that opens the database and pointed it to the local copy of the database instead of the network copy. The local copy that just minutes ago resided on the server before I copied it to my c:\Data folder. So it is identical to the network version. Ran the process in the spreadsheet. Voila! It pulled the new data.
So I'm really at a loss as to what is going on here. The local copy is copied over from the master copy out on the server, and is a direct/complete copy. Why would one copy work, while one doesn't?
My first thought was permissions, but it isn't an issue of not being able to access any of the server, or even any specific table. It is an issue of not getting all of the records when using the network version versus the local version. But if I go into the network version, and select all of the records in the contact table, there are 12,342. If I go into the local version, again there is 12,342 records.
While I have 'resolved' the issue by pointing the spreadsheet to the local copy (which has the benefit of being faster...), I don't know why or how I resolved the problem.
Has anyone seen this before? Any thoughts or suggestions?
I haven't posted actual code at this point, because the exact same code is being executed against both front-ends...
We are running Office 2016/Office 365; I'm on Windows 7 Enterprise, while other users are running Windows 10. It doesn't seem to be an OS issue, because I have the same issue they have.
Thanks
Steve
Bookmarks