Solved Split Database on Network Drive - Persistance Requires Opening Network Folder

[MichaelBurston]

Hey Everyone,

I deployed a split database, whereby several fact tables are stored in a backend database on a network drive, and there's a client database with the forms/vba/reports that is put on each user's desktop. The client connects to the backend through the normal linked table method, and I did try linking using UNC as well as the default mapped drive approach (didn't seem to make a difference).

The issue that I ran into is that the forms are slow to open. However, the forms open extremely fast when I have the network drive open to any folder -- doesn't need to be the specific folder that holds the backend, as long as it's any folder on the network drive it will run fast. If I have the network drive closed, it's slow. If it's open, it's fast. It's quite strange, and I almost feel like just having a folder open on that network drive is making the linked table connection persistent (versus if the folder's closed the link table needs to re-establish the connection every time a form is opened).

I could deploy this and tell everyone to keep the network drive folder open whenever using the database, but that's subject to user error (i.e. there's no guarantee they won't close the folder, resulting in a dramatically slower experience). And I did attempt to implement persistence conventionally by creating a hidden form that opens when the database opens, but that didn't work. Literally the only thing I found that works (so far) is having a folder open.

Some factors to consider:
1. On Windows 10, MS Office / Access 2013
2. Connection to network drive is remote, using NetMotion

Does anyone have any theories as to why a database linked to a shared/network drive would run so much faster by simply having the folder open while using the database? And is there some way to make the database have this performance/persistence without having the folder open?

Thanks!

 

[The_Doc_Man]

I did try linking using UNC as well as the default mapped drive approach (didn't seem to make a difference).

It won't until you run into the case that so many people have connected to so many drives via drive letters that you have no clear and consistent mapping option. Most people won't see this case, but we had over 60 projects at my U.S. Navy site. There, I was forced to use UNC because otherwise there was no way to map what they had to map. Too many drive letter usage conflicts.

I had to look up NetMotion. Since you are dealing with desktops, I don't know that it would have any effect on anything, but it would be worth your while to determine if NetMotion closes connections after a certain time limit. NetMotion claims to be a network traffic optimizer and that COULD result in closing certain connections that appear idle. By any chance, was the hidden form bound to a table or query? If not, how did you implement the persistence?

The "hidden form to obtain persistent connections" method is the way it is usually done. The way I did this was that I had a "switchboard" form that was your way to get to various other functions. In that form's _Open routine, if I wasn't going to cancel the connection, I opened a recordset to a table that had a single record in it (and the record's content was unimportant.) When the DB was about to exit, the hidden form's _Close routine would close the recordset. This worked for me quite well but I didn't have an optimizer running in the background (that I knew about, anyway).

 

[MichaelBurston]

Thanks The_Doc_Man. I agree with the UNC thing; I'm sticking with UNC, as you point out, because there will be 7 users of the database, and they could potentially be mapping the same drive but to different letters (N:/ Z:/ etc.); so, the UNC is a safeguard against that, but doesn't really affect performance.

The forms I created were bound to the linked table, and had one field from each respective linked table. They displayed one record each, with a recordsource of "Select Top 1 * From [Linked Table]". Having them open in the background didn't improve the speed by which the normal forms open. They were also affected the same way as the other forms, whereby they would open slow like the other objects in the database when the network folder was closed, but would open fast when the network folder was opened.

I might just try retesting this at the office -- so that I won't need to have Netmotion on when connected, and could rule that out as a factor.

 

[The_Doc_Man]

The part that bothers me is that I have not had issues with this approach. Therefore, one of two things is going on. Either the way the forms were opened to engage that persistent connection failed (because something got closed too soon) OR your NetMotion is doing something to you.

Let me make a really wild-eyed suggestion. If you have this opening form and it would normally open the underlying table directly... don't. Instead, build a query that explicitly names every field in the linked table. Then reference that table only through the query for the persistent connection.

Yes, it sounds crazy - but the only difference between what you described and what I did was that I went through a query of a linked table. And it is a cheap enough thing to try.

 

[MichaelBurston]

I can give that a try. The more I look into it, the less it seems like an issue with a persistent connection. When the DB opens, a list navigation form opens that allows you to open a popup subform for the record chosen in the navigation list. That navigation form stays open throughout the entire session (because there's no reason to close it). It's probably a lot like your switchboard example, as it's the hub of the database and it maintains the connection during the session.

And having that navigation form open always keeps the connection with the backend, which is good. That became more apparent when I looked at when the backend locking file is on. The locking file is persistently open so long as the navigation form is open, which suggests that persistence isn't the issue (at least not in the usual sense).

Basically, performance is best when two conditions are met:
1. The navigation form is open, which engages the backend locking file and the connection maintained (which should not be an issue)
2. Separately, the user has the network drive folder open.

I think we both agree that #1 is a given -- a persistent connection is very good. But I'm still very puzzled that #2 yields the biggest performance improvement. And, like you said, it might just be due to Netmotion or one of the many applications that run in my background.

 

[The_Doc_Man]

Thinking through this, I am having trouble understanding why #2 has a bigger effect than #1 (of your post #5). I'm an old operating systems guy and from a file-lock viewpoint or a path-evaluation viewpoint, I can't see a significant difference between #1 and #2. I've looked into registry settings but at the moment nothing is cropping up that would affect open files.

On the other hand, English descriptions of technical things can sometimes lead one astray. So if I ask a really pedantic question or two, forgive me for digging up the garden. Re-reading about NetMotion, it appears that one of its features would allow you to connect from a remote site via VPN, which is not a bad thing. Do you know if that is what it is being used for? Or is it used for something else as well? Is that remote connection for Access always hard-wired or are there any wireless links to it?

Before I retired, I had a U.S. Navy laptop that I could use from home on a hardwired connection. It started by establishing a VPN, so I have worked with those. However, our VPN wasn't NetMotion. I don't remember offhand whose "flavor" of VPN it was. If all that NetMotion is doing for you is establishing a VPN from desktop to server, that isn't usually a problem. However, depending on factors in the setup, that VPN "tunnel" just MIGHT hide the connections from the FE to the BE. That single-table query I described earlier somehow changed the connection (vs. direct-to-table) so might change the apparent connection. I've had other troubles with direct-to-table connections, which is why I always had the single-table queries set up for everything I needed to touch. Apparently the interactions between FE and BE differ in some way when that query intervenes.

There is also this question as a double-check to be sure I understand: Your navigation form that stays open IS bound to some BE table, right?

 

[MichaelBurston]

Just an update on this for closure, and thanks for all your help!

I had some of my analyst colleagues do testing on this DB. They didn't have the same issues that I did -- the DB worked totally fine for them.

The variability in performance (i.e. it works better when the network folder is open) is more likely due to a corruption of my Windows Explorer Shell and a network syncing issue -- meaning my laptop is not correctly syncing over the network, and is repeating connections it doesn't need to repeat. As such, while the Access Client DB is able to connect to the network backend DB and create a locking file, it's not syncing properly and seems to be trying to reinitialize the connection to the backend each and every time a form is opened. While the lock persists during a session, the client / windows doesn't seem to acknowledge that. And that's a windows problem.

So, it's more likely a PC issue, and my IT department is just going to replace my laptop. Likely this was due to having Win 7 upgraded to Win 10 recently, and my laptop didn't take too kindly to it.

Thanks for all your help!