Ideas for getting around slow network (1 Viewer)

jaccessable · Apr 6, 2016

I'm hoping that there are some potential solutions to a slow network that I have no control over.

Currently I have a vba program in Access that draws information from a BE database. Sometimes, though, the network is excruciatingly slow - as in 3 second wait times to refresh the listbox after a user enters a number in a user form. Users who have to enter hundreds of numbers are not happy. When I run the program from my local drive, I have no problems at all.

So, my simple idea is to copy the BE upon opening the program to a local (C:\) drive, work with that data, and save to the BE when complete. Unfortunately, I'm really having a problem figuring out how to do this. Trying to use CopyDatabaseFile results in the run-time error 2046 "The command or action 'CopyDatabaseFile' isn't available now." I get this error whether the database file contains the tables or is split. Beyond that, I have yet to figure out how to work with the copy.

I'm wondering if there is any way to get the copy function to work or some other workaround to improve performance. I've searched for hours online for a solution and just can't seem to find one. Any ideas?

pbaldy · Apr 6, 2016

It would depend on your specifics. If the users are just doing data entry, they could enter to a local table, and then append that data to the BE all at once when they're done. I have apps run over a WAN where when the app opens, it copies relatively static data like customers, rates, etc to local tables and uses the local tables for combo row sources. I certainly wouldn't be thinking about copying the whole BE around.

Minty · Apr 6, 2016

From your description it sounds as if the data is purely being read? Not saved?
In which case you could either only pull in the minimum data set you need rather than the whole table or (and much messier) and not elegant, copy the linked tables into local tables on opening the database, then run everything of the local copies.

If you are saving anything then the second option in a non starter without a massive amount of work.

jaccessable · Apr 6, 2016

Sounds like some good ideas. The current process is that the user enters data in a textbox, the data is posted to a table in the BE database on the server, and then the table is refreshed with data from a query. When it's all on my computer, it's instant. When the BE is on the server, the refresh of the listbox takes 3 seconds. I'm using an SQL statement to populate the listbox and regardless of how simple I make that statement, it's still taking 3 seconds.

I've changed over from using the AddItem method to fill the box to the Table/Query method because of the 32K limit with the Value List Row Source Type. My 11 column listbox was regularly exceeding the 32K limit.

So, it sounds like pbaldy is suggesting I only copy tables over, but I'm not sure of the mechanics of that. The front end for users is also run from the network location. Would I copy the tables to the FE file or create a new database on the local drive, copy the tables to that?.... Just not sure what to do.

pbaldy · Apr 6, 2016

Generally the FE should be on the user's computer, not the network. They certainly shouldn't share the same copy of the FE.

I'm saying I would copy lookup tables, likely not transaction tables. The process is basically:

empty local table
copy BE table data to local table

jaccessable · Apr 6, 2016

Ok. I can do that. My concern is how to get updates to the users, but that appears to be another question and I'm seeing several solutions in the forum.

Thanks pbaldy

pbaldy · Apr 6, 2016

No problem, post back if you get stuck.

jaccessable · Apr 6, 2016

Interesting issue I can't seem to solve - The copyobject command is saving the copied table from the BE database to the BE database instead of the FE even if I specify the destination. I can't seem to find a solution to this.

FE location: C:\Users\user.name\Desktop\
BE location: J:\Database\

Here's my command line:

DoCmd.CopyObject "C:\Users\user.name\Desktop\Database File.accdb", "DataTable_Copy", acTable, "DataTable"

When executed, the table ends up as a linked file in the BE database located at J:\Database\Database_BE.accdb

pbaldy · Apr 6, 2016

Sorry for not being clear. I'd run a query or code to populate the local table from the linked table, not actually copy the table. In a query:

INSERT INTO LocalTableName(Fields...)
SELECT Fields...
FROM LinkedTableName

If they have the same structure you shouldn't have to list the fields.

jaccessable · Apr 6, 2016

Ok, thanks.

jaccessable · Apr 7, 2016

That idea worked very well. Here is my code in the Form_Load() procedure:

DoCmd.SetWarnings False
strSQL = "DELETE * FROM Database_Copy"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO Database_Copy SELECT * FROM Database"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Database_Copy is in the FE file on my local drive. The performance issues are cleared up.

pbaldy · Apr 7, 2016

Glad it worked for you.

Ideas for getting around slow network (1 Viewer)

jaccessable

Registered User.

pbaldy

Wino Moderator

Minty

AWF VIP

jaccessable

Registered User.

pbaldy

Wino Moderator

jaccessable

Registered User.

pbaldy

Wino Moderator

jaccessable

Registered User.

pbaldy

Wino Moderator

jaccessable

Registered User.

jaccessable

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread