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.

 

[sark666]

Well, I'm a newb. For example, this ms access database also generates flat reports. Meaning it's just an output of some info and all the user can do is print/save or close the report, but yet I see an ldb file persists while this report is on screen. Could you give me a basic example of how one would prevent that?

 

[xenou]

Any time you are accessing data Access will use a lock file. I suppose you can play around with lock settings and recordset types but as far as I know Access would still use a lock file. That is how Access works. When you close the reports, queries, forms you are using, the lock file will go away (as long as other users aren't also doing something).

Or, as I've said (!), you can write your own connections in code to retrieve data (using "unbound" forms and reports). Since you are an Access newb and you are dealing with a fairly large application with a lot of users, I don't recommend this - you should get a consultant to come in and fix this for you.

 

[sark666]

You're probably right that I need outside help, but on the other hand I needed help when I was writing in excel to query all the databases and generate reports. So usually, I can figure it out in the end. I guess I was looking for a really basic example of an unbound form or report, that still presents data from a table is not connected to the backend tables. But I'll google more.

Again, my only goal here is to speed up multiuser, I was focusing on the ldb file for the previously stated reasons. And maybe this is an inherit limitation of access with multiusers. But from what I read, it's a cumulative loss in performance as more users join, whereas what I'm experiencing is a noticeable drop in performance and as soon as a second user joins (and remains as such even if they quit, leaving one user) and a third or forth user doesn't really have much of a negative impact.

 

[xenou]

I found a thread at Access world forums similar to your problem but I suspect it's the one you referred to earlier (and it was just as convoluted as this one). When you are dealing with more than one network there's a big variable thrown into the equation, both in terms of network speed and reliability as well as file access on the other end - privileges, permissions, file locking - things we don't usually have to worry about. To my mind, just use a server based DB and be done with it - you'll solve this problem as well as be able to condense your 200 different databases into one, which will be a big help. Especially if you're going to the trouble of a major re-write, then you'll get a lot more bang for your buck.