Create and link a table on the backend for use as a persistent connection

[sark666]

I've read about the speed benefits of creating a persistent connection when there are multiple users. I also read an easy way of doing this is open a hidden form that links to a table, preferably a small table. I have one such table that is not used too often but I read from a couple of users that they had some data corruption issues when using a table for the persistent connection that is also used at other times.

So I thought I'll play it safe and create a small table with 1 record. But I'm not looking forward to this doing it over 200 times.

So is there a way to create and link a table for the backend?

I saw this snippet of code for creating a table, but obviously it creates it on the frontend:

[COLOR=#000000][FONT=verdana]Sub CreateTable()[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]Dim dbs As Database, tbl As TableDef, fld As Field[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]Set dbs = CurrentDb[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]Set tbl = dbs.CreateTableDef("Test")[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]Set fld = tbl.CreateField("test1", dbText)[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]tbl.Fields.Append fld[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]dbs.TableDefs.Append tbl[/FONT][/COLOR]
[COLOR=#000000][FONT=verdana]dbs.TableDefs.Refresh[/FONT][/COLOR]

[COLOR=#000000][FONT=verdana]End Sub[/FONT][/COLOR]

And if possible, I'd like it to be automatically populated with a value.

 

[xenou]

There's no need to create a table in code. Use a table in the backend that already exists permanently (or create one for this purpose). You can do that easily with Access using the Create Table menu items.

 

[sark666]

I realize I could use an existing table, but I've read some users have had more data corruption issues when using an existing table that's used elsewhere as a persistent connection, so it was recommended to use a new table.

I know I could just make a table (which I've done) but there are over 200 seperate databases so I would have to go into each database and do this 200 times to ensure each database has a dummy table for this purpose, that's why I wanted to automate it. So I could just push out a new client, and it would check does the dummy table exist on the backend? No? create it and open it as hidden. If it exists, simply open it as hidden.

Unfortunately, in my tests this didn't result in a speed increase, rather a decrease, so I'm back to square one.

We use access to track attendance with a separate database for each office. With 1 user logged in, it's as fast as you can input. As soon as a second user logs in, it takes about 7 seconds to record one absence. Not horrible but not stellar. So I tried the persistent connection trick, ensuring I had delete file permissions on the backend (my understanding is each user needs the ability to delete the ldb file and not simply having write access).

I added it to the first test client I saw the ldb file was created and remained throughout the login. However now with just 1 user logged in, it takes about 7 seconds to record an absence. So with the persistent connection, performance wise, it behaves as if there are multiple users logged in.

I was hoping this would be a quick and easy way to gain some speed as others have reported, but for whatever reason, no such luck. Unless there's something I'm doing wrong, but I don't see it.

So my original idea, and I have another thread on this, is I put in an idle timeout function. So if someone is idle for 10 minutes they get booted. But this doesn't seem to reclaim the speed. So:

1 user is in - it's fast
2 user joins, it's slow for both
2 user leaves (or gets booted due to idle) and it's still slow for 1st user

I thought periodically checking how many users are logged in and if it's 1 user, relink the tables to reclaim performance. But as a test I ran a relink tables function but yet it remains slow even with 1 user.

So it seems as soon as more than 1 user joins access remains slow even if the additional users logged out. The only way for the lone user to reclaim that speed is to log out/log in. There must be some way to reclaim this speed. I thought relinking the tables would do it, but no go.

At one office we have about 4 users (I've ensured to keep that low), but it's rare they'd all be in at the same time. However sometimes 2 will log in and then the performance is bad throughout the day until they all log out.

Is there some way to mimic a login/logout to reclaim the speed benefits of being the only person in the database without actually logging out?

 

[xenou]

Hi,
By a new table, they only mean one that you put in the back end database. Not one that you create over and over again in code. It goes in the back end. You shouldn't have 200 back ends (do you?). In any case, if you do have 200 back ends then fine - check if the table exists, then create it if it doesn't.

I see no reason why a second user would make a database go from instantaneous to taking seconds for an update. There may be more to this puzzle. There is nothing about one user versus two users that would ordinarily degrade performance this way (that I know of).

 

[sark666]

Thanks for the response. yes we have 200 databases, this was to keep multiusers to a minimum. Otherwise we'd have like 150 simulatenous users and I know that's asking for trouble. So yes I wanted code that would create this table in the frontend for that particular database. Obviously this would be a one time creation and just left open in the background (and not recreated) on subsequent logins.

However as I mentioned, the persistent connection does the exact opposite for me performance wise, so I'm scraping that idea.

My knowledge of access is very limited so I'm just learning tricks here and there and stepping through the code/queries and so far, I've been able to add what's needed. But there's always been this performance issue.

However I seemed to have figured out (at a very basic level) where the performance degrades. It seems to be when the idb file remains open. Again the exact opposite of the persistent connection issue.

The ms database has a main menu. When logged into the main menu no ldb is present on the backend. If the user goes into the absence recording section or the reports section an ldb file is created and remains there until returning to the main menu.

So here's what I observed.

1st user logs in and goes to the record absences section, ldb file is created, it's fast.
2nd user logs in and goes to the record absences section. it's now slow for both.
2nd user logs out, it's still slow for the 1st user.

However, if the 1st user returns to the the main menu and as a result the ldb file is closed and then returns to the record absences screen, it's fast again as if they logged out/logged in.

So this doesn't solve why it's slow for multiple users, but I was thinking is it possible to force closure of the ldb file even if a form remains open that references queries/tables?

I know that may be the complete opposite of msaccess database functionality, but again exposing my ignorance.

I'd rather if the user goes into the record absences and selects an employee, it creates the ldb file by querying their prior absences, lists them etc, and then once it has what it needs, closes the ldb file but simply retains on screen whatever it got from the queries. And then the idb file would be created again once they interact with the form.

So basically I want a constant creation/deletion of the ldb file only upon user interaction and not when they simply sit there on a form idle doing nothing.

I know this is probably off course from normal practice and may not be possible, but it's the only thing I've noticed with any speed benefit.

Unless there's some other basic thing I could check. Or maybe is there a reason why a persistent connection (even with one user) would hurt performance?

 

[xenou]

Personally I think the whole lock file thing is a red herring. It doesn't explain anything - it just happens to coincide with whether or not users are logged in. Having two users or more logged in is the issue. Probably using Access on a server is the real problem. What would make sense here is a real client server DB with one database.

Awaiting your reply in the other thread...

ξ

 

[sark666]

It may be a red herring, but the reason I'm focusing on that, it seems to reflect the performance issues. As I mentioned, without a persistent connection, it's fast for one user. However, with a persistent connection, it's slow (as slow) as if there were more than one user logged in even though only one user is logged in. Why would that be? So I'm thinking it has to do with subsequent writes to the existing ldb file? Without the persistent connection, the ldb file is created when they go to the record absences form and deleted when they return to the main menu.

So what I'm asking at this point is, is there a way to close all connections even when a form is still open and only reopen connections when the user actually does something?

So given my limited knowledge I was using the persistent connection form as a test, I made it visible instead of hidden just to ensure it's open, and I see the ldb file is created once it's open. It's referring to one record in a one record table on the backend. Is it possible to keep this form open (and whatever results it's currently reporting) but close the connection to the backend? If I can figure out a basic way of doing this, I can attempt to apply that logic to the other forms. I realize this will result in numerous creating/deleting of the ldb file which is normally not desired, but I can't think of anything else to try.

Again with two users logged in it's slow unless both return to the main menu (db connection is closed, ldb file is gone) and then one user goes into the record absences (and the connection is open again and an ldb file is recreated) Then it's fast for this one user even though technically two have the ms frontend open. The second user sitting at the main menu with no db connection is not impacting performance. But if that second user goes into the record absences form then it's slow again. This seems to be because of the open connection and subsequent writes to the ldb file. Why else would a persistent connection with just one user result in a performance degredation?

And yes, this is begging to be on an sql server but I'm stuck in this position, and they aren't going to change this anytime soon.

So again, what I'm searching for right now is, can a form be opened that has fields that have queried the backend, but close the connection immediately after the result is obtained and not retain persistent backend connection?

 

[xenou]

You can't have a persistent connection and also not have a lock file. So if you want to go the persistent connection route then there will always be a lock file too. I seriously doubt the cause of any slowness is due to having to write anything to the lock file. I'm pretty skeptical about this whole persistent connection thing doing you any good anyway, but I guess you can try it and if it works it works - seems like you've pretty much discovered already that it doesn't help.

You can rewrite the database to code updates with ADO (i.e., don't bind the forms to any tables). That's a significant design change. It might be better to consider the entire design first - it still seems possible that you have an inappropriate setup on a remote server, and instead of trying to make that work, you may want to address the problem at the root. I'm not sure - don't know exactly what you mean by a server - where it is and how it is accessed.

 

[sark666]

I realize you can't have a persistent connection and also have a lock file. What I meant was I want the exact opposite. Any time a form open whether it's bound to a table (none of these are) or a query or whatever. I want it to get its value and then shutdown the connection. So basically the user is only really connected to the back end when they are actually doing something and not when sitting there idle on whatever form. While in the record absences screen in this database I see the ldb file remains so it is persistent in that part. I'm trying to prevent that from happening.

Again, I'm not even sure if that's possible. What I basically want is to never have a persistent connection in any form, report etc. If the user interacts with the form/report then reopen the connection, get the update/write the update etc. and then immediately close the connection.

 

[xenou]

As stated, you'd have to rewrite the database forms using your own update methods, not linked tables.