Accdb: "Network_server_organizer"

[vhung]

Good day,

Recently I create a new project that has 40k row data,
it has 5 network connections, using old computers,
installing 2010, 2013, 2016 MS access.

On those days that 5 users work together the system
start to load slowly, I've tried to reset sub forms and combo and queries
but then there were computers that remains on slow connection.

I did not try to use SQL as server because of time deficiency,
but this time I already created a new system,
have not test it yet.

"What I just created now is "Network_server_organizer"
on accdb:
4 link tables from each of computer as server,
4 queries, 5 forms "1 as main form for main Table , 4 forms for link table"
: my main goal is to combine the links data to the main Table,
to be use as output of 4 encoders,
am still wishing that these system would work well,
suggestion and comments is welcome,
could this be successful or not...

In addition, buttons a1 b2 c3 d4 is use to command copy and paste data
from each link to combine on the main Table,
the system already works on copy and paste
but still don't know on actual network connection of each computer,.

 

[strive4peace]

hi @vhung

it is fine to share the back-end(s) of a database, so users can share the data

However, each user must have their own copy of the front-end with forms, queries, reports, macros, and modules

It sounds to me like each user is opening the same accdb with user interface objects,

Although Access doesn't stop you from letting multiple users share the front-end, that's asking for trouble and is a good way to corrput the database

~~~
additionally, "name" is a reserved word in Access and shouldn't be used as a field name

Problem names and reserved words in Access by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

 

[The_Doc_Man]

but still don't know on actual network connection of each computer,.

I'll answer that (or at least, if I understood your uncertainty, I'll answer it).

If you actually have a company INTRAnet, even if it is NOT a formal domain-controller situation, you can run the Windows "standard" network setup wizard for each machine to join with a "home" network. You would tell the wizard that you want "File and Print" sharing. (Doesn't matter if you don't actually have a printer - the name of the option is as I quoted it.)

IF you have a commercial router like one would use for wiring up your home and connecting to the Internet, that router will probably support DHCP to assign addresses, and Windows works well with that. For many years the DHCP option has been fully supported. Use it if you have it. If you DON'T have such a router because this network will be totally stand-alone, then you would have to manually assert fixed addresses in the range 192.168.0.x where x = 1 to 253. 0 will be used for certain template functions. 255 would be used for the sub-net mask. 254 would be reserved for a router (if you ever wanted to add one). The "192.168" part is a network standard for all "local" class-B (65K users/slots) in-house networks. The 0 is a sub-net specification. If you don't have a router, you could probably get away with almost any number except 255 for the 3rd component. If you DO have a router, you can bet it WILL use 192.168.0.x as its address range.

If you have any router fancier than a home network setup, you will need to enable it for Server Message Block (SMB) protocol for which the version (v1, v2, v3) will depend on which version of Windows you are running. Home routers don't usually filter on protocols. Commercial routers can and often DO filter on protocols.

As to physical connections, you would prefer a physical Ethernet "cat 6" cable, though on slow machines "cat 5" would also work. Just don't mix them. In the absence of a router, just drop each connection onto a single Eth cable using "vampire taps" if nothing else is available. If you have a router, it would be possible to have more than one cable but most "home" routers do not have more than four hard connections On the other hand, you can have five or six connections on a single Ethernet cable without much trouble at all.

 

[vhung]

I'll answer that (or at least, if I understood your uncertainty, I'll answer it).

If you actually have a company INTRAnet, even if it is NOT a formal domain-controller situation, you can run the Windows "standard" network setup wizard for each machine to join with a "home" network. You would tell the wizard that you want "File and Print" sharing. (Doesn't matter if you don't actually have a printer - the name of the option is as I quoted it.)

IF you have a commercial router like one would use for wiring up your home and connecting to the Internet, that router will probably support DHCP to assign addresses, and Windows works well with that. For many years the DHCP option has been fully supported. Use it if you have it. If you DON'T have such a router because this network will be totally stand-alone, then you would have to manually assert fixed addresses in the range 192.168.0.x where x = 1 to 253. 0 will be used for certain template functions. 255 would be used for the sub-net mask. 254 would be reserved for a router (if you ever wanted to add one). The "192.168" part is a network standard for all "local" class-B (65K users/slots) in-house networks. The 0 is a sub-net specification. If you don't have a router, you could probably get away with almost any number except 255 for the 3rd component. If you DO have a router, you can bet it WILL use 192.168.0.x as its address range.

If you have any router fancier than a home network setup, you will need to enable it for Server Message Block (SMB) protocol for which the version (v1, v2, v3) will depend on which version of Windows you are running. Home routers don't usually filter on protocols. Commercial routers can and often DO filter on protocols.

As to physical connections, you would prefer a physical Ethernet "cat 6" cable, though on slow machines "cat 5" would also work. Just don't mix them. In the absence of a router, just drop each connection onto a single Eth cable using "vampire taps" if nothing else is available. If you have a router, it would be possible to have more than one cable but most "home" routers do not have more than four hard connections On the other hand, you can have five or six connections on a single Ethernet cable without much trouble at all.

Good day, Doc

Thanks for the ideas, though I already used network switch for "LAN", but I'm afraid to use the online connection,.
What I meant is, find more confident with the system as encoding/updating data's then be save on main Table.
Only the controller will be the one to prepare the whole data counts/summary.

 

[vhung]

hi @vhung

it is fine to share the back-end(s) of a database, so users can share the data

However, each user must have their own copy of the front-end with forms, queries, reports, macros, and modules

It sounds to me like each user is opening the same accdb with user interface objects,

Although Access doesn't stop you from letting multiple users share the front-end, that's asking for trouble and is a good way to corrput the database

~~~
additionally, "name" is a reserved word in Access and shouldn't be used as a field name

Problem names and reserved words in Access by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

Good day, strive

Thanks for the ideas,
every computer is installed with their own complete project objects,
could add/edit and even print their own works.,
field name on example as "name" is example only not the actual name.

I already used network switch for "LAN",
Only the controller will be the one to prepare the whole data counts/summary.

 

[strive4peace]

hi @vhung

you're welcome. So by that you mean each user DOES have their own front-end?

Here are a few things to improve performance that quickly come to mind:

• put back-end in a location with a SHORT path
• Use an AutoNumber (or Long Integer) primary key, and use Long Integer foreign keys (default value is Null)
• Keep text fields that are indexed as short as possible
• Build indexes on fields that are used to look up data
• delete unnecessary indexes

How many fields do the shared tables have?
Are any of them Long Text or other non-standard data type?

 

[vhung]

hi @vhung

you're welcome. So by that you mean each user DOES have their own front-end?

Here are a few things to improve performance that quickly come to mind:

• put back-end in a location with a SHORT path
• Use an AutoNumber (or Long Integer) primary key, and use Long Integer foreign keys (default value is Null)
• Keep text fields that are indexed as short as possible
• Build indexes on fields that are used to look up data
• delete unnecessary indexes

How many fields do the shared tables have?
Are any of them Long Text or other non-standard data type?

Hi strive;

Preferably yes,
have to established complete installation to each computer,
only have to provide also split dB as own server,
to where as one source of the link tables on my control/controller.

Always will, with the data integrity,
fields type as date, double number, short text,
There are about 10 fields for data information should be link.

The concept I've try is long idea already has on my mind,
said to be, got to work it out,
for now it works on one computer of 4 split link source,
good thing with this format is by using table queries to have specific files to copied for on the main Table,
link tables is steady unopened on controller,
when forms either a1 b2 c3 d4 is open command copy and paste is executed after click ok,
still laughing for this, what have done, well things work,
have to try the connection with 5 computers soon,
hoping this would be the solution of instead one source server for 5 users,
only user a1 is no access to b2 c3 d4 and vice versa, which is still ok for me,
Almost have it, soon to solve the slow loading when consolidating total data.

Thanks strive, with your kind response.

 

[strive4peace]

hi @vhung

you're welcome

I don't quite understand what you said ... but it would seem you don't have good performance fields to link on. Keep in mind that double precision numbers aren't reliable for linking because they aren't exact ... and date might have the same issue since internally, it is stored as double. That leaves text. The default size for Short Text is 255 -- and that is way too long!

 

[vhung]

hi @vhung

you're welcome

I don't quite understand what you said ... but it would seem you don't have good performance fields to link on. Keep in mind that double precision numbers aren't reliable for linking because they aren't exact ... and date might have the same issue since internally, it is stored as double. That leaves text. The default size for Short Text is 255 -- and that is way too long!

On this project there is no use of purely Date,
the field type is set to text "name, address, and combine row of number&letters like "007A" ,
this project aim to help improve the speed of connection, am still working for the best...

 

[The_Doc_Man]

On this project there is no use of purely Date,
the field type is set to text "name, address, and combine row of number&letters like "007A" ,
this project aim to help improve the speed of connection, am still working for the best...

If your goal is to improve speed, there is no substitute for hard-wired networking at the fastest rate your network interfaces will support. However, food for thought: A slower network that has fewer retransmissions (due to network errors) may well be faster than a faster but noisier network. When seeking speed, seek EFFECTIVE speed.

You talk about combining fields. Combining fields together in Access doesn't help regarding speed of connection. Access doesn't send fields. It sends whole records regardless of whether you have one long, concatenated field or a dozen individual fields. If they are all in the same record, they go together. Makes no difference at all in the speed. I.e. 255 bytes is 255 bytes no matter how you broke it up in field sizes.

If I read your earlier comments correctly, it seems that you are trying to keep subsets of data separate in each front-end. You might seem to get more speed by not transmitting, but you lose something more important with this approach. If you keep a1 and b2 data separate but copy them to a combined table, you have the problem of "authoritative source" confusion. This is a MASSIVE violation of normalization. Duplicating data means that it would be possible to create a report that contains out-of-date data. Not only that, but if you have data to correlate using referential integrity, you cannot apply RI to data in tables on two different files. RI applies only within a file, not across files.

The correct method would be to have all data in the back end with records that contain a field showing the origin of the data such as "a1" or "b2" or whatever designation you use. If you want to restrict what people see, then have a way to mark every record with that "a1" or "b2" or whatever designation and then in the distributed front ends, determine which designation to use as a filter. It sounds like you are trying to speed things up but because you have now added synchronization to your list of things to do, you add more I/O load to each system.

 

[vhung]

If your goal is to improve speed, there is no substitute for hard-wired networking at the fastest rate your network interfaces will support. However, food for thought: A slower network that has fewer retransmissions (due to network errors) may well be faster than a faster but noisier network. When seeking speed, seek EFFECTIVE speed.

You talk about combining fields. Combining fields together in Access doesn't help regarding speed of connection. Access doesn't send fields. It sends whole records regardless of whether you have one long, concatenated field or a dozen individual fields. If they are all in the same record, they go together. Makes no difference at all in the speed. I.e. 255 bytes is 255 bytes no matter how you broke it up in field sizes.

If I read your earlier comments correctly, it seems that you are trying to keep subsets of data separate in each front-end. You might seem to get more speed by not transmitting, but you lose something more important with this approach. If you keep a1 and b2 data separate but copy them to a combined table, you have the problem of "authoritative source" confusion. This is a MASSIVE violation of normalization. Duplicating data means that it would be possible to create a report that contains out-of-date data. Not only that, but if you have data to correlate using referential integrity, you cannot apply RI to data in tables on two different files. RI applies only within a file, not across files.

The correct method would be to have all data in the back end with records that contain a field showing the origin of the data such as "a1" or "b2" or whatever designation you use. If you want to restrict what people see, then have a way to mark every record with that "a1" or "b2" or whatever designation and then in the distributed front ends, determine which designation to use as a filter. It sounds like you are trying to speed things up but because you have now added synchronization to your list of things to do, you add more I/O load to each system.

Good day Doc,

You shared a very nice thoughts,
"If your goal is to improve speed, there is no substitute for hard-wired networking at the fastest rate your network interfaces will support. However, food for thought: A slower network that has fewer retransmissions (due to network errors) may well be faster than a faster but noisier network. When seeking speed, seek EFFECTIVE speed."

I already know that,
"Access doesn't send fields. It sends whole records regardless of whether you have one long, concatenated field or a dozen individual fields. If they are all in the same record, they go together."
That is the actual result of this project..

This seems interesting to me though it is with the reality,
"Makes no difference at all in the speed. I.e. 255 bytes is 255 bytes no matter how you broke it up in field sizes."

Only few could observed this,
"If you keep a1 and b2 data separate but copy them to a combined table, you have the problem of "authoritative source" confusion. This is a MASSIVE violation of normalization. Duplicating data means that it would be possible to create a report that contains out-of-date data. Not only that, but if you have data to correlate using referential integrity, you cannot apply RI to data in tables on two different files. RI applies only within a file, not across files."

I already try to resolved issues like the above mentioned,
Thus; a1 is focus data from area aa1 while b2 is on bb2,
Besides I have created Form that could identify and count the duplicates if there is
Another thing the "data referential integrity", I oversee this already that problems of connection to reports be debugged

This so is pleasant,
"The correct method would be to have all data in the back end with records that contain a field showing the origin"
"It sounds like you are trying to speed things up but because you have now added synchronization to your list of things to do, you add more I/O load to each system."