UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Is There Any Way To Link Multiple Be With A Fe?, Access 2010    
 
   
mitsbd
post May 8 2019, 03:37 PM
Post#1



Posts: 37
Joined: 23-May 18



Hi,
After working on 100 computers, all the information have to copy to the server computer's backend file. Is there a way to link 100 backend files at the same time with a frontend?

Best regards,
MITS
Go to the top of the page
 
tina t
post May 8 2019, 03:43 PM
Post#2



Posts: 6,038
Joined: 11-November 10
From: SoCal, USA


well, assuming that you mean link one or more Access tables from multiple (100) Access databases, into a single Access database, then the answer is probably yes. (it may well be possible even if any of the files are not Access dbs, but i can't speak to that, having only worked with Access.) i've personally never linked tables from 100 different dbs into a single other db, but i don't recall reading/hearing anywhere about a limit specifically to the number of linked tables allowed.

but describe your setup, and what you want to accomplish. there may be easier, more efficient ways than linking tables from 100 databases.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post May 8 2019, 04:33 PM
Post#3


UtterAccess VIP
Posts: 1,561
Joined: 4-June 18
From: Somerset, UK


Agree with Tina.
You can have multiple BEs and far more than 100 linked tables.
For example I have a database with 350+ linked tables from 7 different BEs.
However, trying to link to 100 BEs sounds unwieldy to manage and I would consider finding better ways of doing this if possible

--------------------
Go to the top of the page
 
gemmathehusky
post May 8 2019, 05:30 PM
Post#4


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


I use a local table called MyTables, or similar.

In there I store the backend database name, remote table name, and my linked table name (generally the same)
Generally everything connects to a single back end, but it can connect to more.

At startup , I check that the .connect property of every table in the table manager table is correct. If not, I reconnected all the tables.


Note that you can't enforce referential integrity unless the tables are in the same back end.


You could easily store alternative backends, to enable you to connect to either the "live" data tables, or a set of "test" tables.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Jeff B.
post May 8 2019, 05:42 PM
Post#5


UtterAccess VIP
Posts: 10,271
Joined: 30-April 10
From: Pacific NorthWet


?Why? Why do you want to link "100 separate files" (?tables in dbs?)? Knowing the underlying business need would help us better advise you...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
kfield7
post May 9 2019, 12:51 PM
Post#6



Posts: 978
Joined: 12-November 03
From: Iowa Lot


Adding to Jeff's comment, another way to connect to external db data is through a query. For example,

SELECT tblDepts.Dept FROM tblDepts IN xxx;

where xxx is your path and filename for the external database.

With this, you don't have to manage and retain links to external databases.

If the external databases are similar, you can change the "xxx" via VBA and cycle through until you've retrieve/processed the data you need.

Go to the top of the page
 
mitsbd
post May 9 2019, 03:01 PM
Post#7



Posts: 37
Joined: 23-May 18



Thank you all for your reply. I'm from an education office. I have developed a software using MS Access for schools under our control. Schools are using it to store various information about the students. Using it they can track students attendance and process exam results too. We want to copy all of these schools information in one place to review. Copying separately from 100+ backends is a lengthy process. If we can automate this system it will save my time and the number of mistakes will be reduced. We want to put all the files in the same folder. In this case we will use the BE ... method to name their backend files. Instead of ... we will use the school name.

Regards.
Go to the top of the page
 
tina t
post May 9 2019, 03:41 PM
Post#8



Posts: 6,038
Joined: 11-November 10
From: SoCal, USA


am i correct in thinking:

1) each school has their own database, but all the school databases are identical in structure (same tables and fields)?

2) your goal is to have a central database that is also identical in structure, and dump all the data (table records) from the tables in all the school dbs into the tables in the central db?

if the above is correct, my only other questions are:

1) in the central database, do you need to track where (which school's db) each table record came from?

2) are the school names, or some other unique identifier, in each database's filename?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Jeff B.
post May 10 2019, 07:53 AM
Post#9


UtterAccess VIP
Posts: 10,271
Joined: 30-April 10
From: Pacific NorthWet


I am so with Tina's questions/comments!

I'd add that it isn't necessary to copy the separate dbs into one central db. … and there is a good reason not to … If there are multiple copies of the same data (i.e., an individual school's db and the central db), which one is correct if there's a difference?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
gemmathehusky
post May 10 2019, 01:09 PM
Post#10


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


If you allocate each school an ID number, you could store the ID number in the data rows. Then you could import the data rows into a mega-database, and have all the data in a single table.

Even if you don't have the reference stored in each school's data, YOU could allocate some unique schoolID in YOUR master consolidation database, at the point you import their data.

(and you could automate that process)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
tina t
post May 10 2019, 02:05 PM
Post#11



Posts: 6,038
Joined: 11-November 10
From: SoCal, USA


okay, Dave and i are on the same page here, it looks like. i wouldn't want to link tables from 100 dbs into one db, because that's only the first step. pulling the data together for each "set" of identical tables would take the Union query from hades - if you could even union that many tables in one query (i have no idea on that).

QUOTE
We want to copy all of these schools information in one place to review. Copying separately from 100+ backends is a lengthy process. If we can automate this system it will save my time and the number of mistakes will be reduced.

assuming the 100 dbs are identical in structure, i'd have a "master" db for the admin reviewing - split, of course, in to FE and BE. i'd automate the data consolidation in the BE by linking to the tables in one BE, and writing up the Append queries to copy the data into the master BE tables. then i'd make a copy of that empty BE db as a backup file. then i'd set up a VBA procedure to loop through the 100 BE dbs, either renaming each db to a generic name that is supported in the links, or changing the db name in the links each time, and running the Append queries. this could be fully automated.

the master admin FE db would have all the GUI needed for reviewing the consolidated data, and the master BE db could be deleted and then copied from the empty backup db and repopulated, at any time. no bloat, and no muss no fuss, after the initial setup is done.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mitsbd
post May 11 2019, 04:56 PM
Post#12



Posts: 37
Joined: 23-May 18



Hi Tina,
QUOTE
am i correct in thinking:

1) each school has their own database, but all the school databases are identical in structure (same tables and fields)?

2) your goal is to have a central database that is also identical in structure, and dump all the data (table records) from the tables in all the school dbs into the tables in the central db?

Yes, you are correct in thinking. The central database is also same in structure (tables and fields). But it has some extra queries to review.

QUOTE
if the above is correct, my only other questions are:

1) in the central database, do you need to track where (which school's db) each table record came from?

2) are the school names, or some other unique identifier, in each database's filename?

hth
tina

There is a field namely SchoolID in every database to track where each record come from and first part of StudentsID also contain SchoolID to avoid duplicate ID.
Regards.
This post has been edited by mitsbd: May 11 2019, 04:59 PM
Go to the top of the page
 
tina t
post May 11 2019, 09:55 PM
Post#13



Posts: 6,038
Joined: 11-November 10
From: SoCal, USA


QUOTE
There is a field namely SchoolID in every database

where is the SchooldID? in a single table that holds only that ID value? as a field in every table, so all records are flagged as belonging to "x" db? or...?

in a nutshell, and to expand on my previous post: in the master BE db, you have to make sure that there is a unique primary key for every record in every data table. it's not quite as simple as appending data from db1 and then db2 and then db3, etc. if data in TableA in db1 includes a record with a pk value of 8879, and data in TableA in db2 also includes a record with a pk value of 8879 - and that's probably a likely scenario, depending on what you're using as a pk in each table - there's no problem when those records are in their own separate school dbs. but when those records are dumped into the master BE db, there must be a way to make those two records' pk values unique, so the system can identify them individually.

the above is certainly do-able, and certainly automate-able. the devil is in the details, and may be a pain in the hiney - again, depending on the current structure - but you only have to set it up once. and if you have a table listing all the individual school dbs' names (which someone mentioned in a previous post in this thread), and those SchoolIDs assuming they're all unique, then once the master BE db's tables are set up to support consolidated table data that preserves the pk/fk links between parent/child tables, then the tables are done.

you can write a VBA procedure to loop through that local table holding the list of schools, either renaming each db to a generic name that is supported in the links, or changing the db name in the links each time - as i mentioned in previous post - then running the Append queries, and looping to the next db name in the local table.

may sound complex, but it's really not, and not hard to do. the hardest part of the whole thing, i'd say, is setting up the master BE tables, and writing the queries, to ensure that 1) each record in the tables is uniquely identified, and 2) that the pk/fk links between parent and child tables' data are preserved. the payoff to the time spent on the initial setup is a flexible solution that will accomodate as few or many school dbs as you wish, by simply adding/deleting school name records from the local table, as needed. and getting current data can be done as often as you wish, either by starting the code manually, or setting up an automated run nightly, weekly, monthly, whatever suits your needs.

and btw, when everyone gets "back" from this Mother's Day weekend, you may likely get suggestions for other solutions that will work even better and/or be easier to set up. then you and i will both benefit! :)

hth
tina
This post has been edited by tina t: May 11 2019, 09:57 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
gemmathehusky
post May 12 2019, 04:36 AM
Post#14


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


Yes, Tina and I are on the same page.

The thing is, a "reporting" database containing the consolidated records of all the schools would be different to a "school" database. In the consolidated database, you wouldn't be bothered about adding new teachers/pupils/courses. You no doubt just want the data for a different reason, so the functionality is going to be quite different, and introducing the "school Id", shouldn't be an issue.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
mitsbd
post May 13 2019, 02:26 PM
Post#15



Posts: 37
Joined: 23-May 18



There is a table namely tblSchoolInformation both in Office/Central Database and School Database.

Here is a simple structure for your understand:

tblSchoolInformation:

SchoolID (PK)
SchoolName
* Other fields

tblStuInfo

StudentID (PK) - This ID is generating using SchoolID+Session+ClassID+Serial. So there is no scope of generating duplicate student ID.
SchoolID (FK)
* Other fields


Go to the top of the page
 
mitsbd
post May 16 2019, 03:29 PM
Post#16



Posts: 37
Joined: 23-May 18



Finally I think that there is no problem in database design. I'm familiar in database design. But don't know how to append data from multiple BE using VBA. Can anyone help me please?
Go to the top of the page
 
gemmathehusky
post May 16 2019, 04:03 PM
Post#17


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


you just need a mastertable.
Then this sort of logic.

to do this, you really need all the school databases to be in a single folder.
you can loop round the school databases by doing a dir() loop on the folder.

CODE
delete * from mastertable
databasename = dir("my schools folder")  'with the correct syntax
while databasename >""
    call load_data (databasename)  'call the sub below for each database
    databasename = dir()
wend


CODE
sub load_data(schoolDBS as string)
     link to the table in schoolDBS
     append data from that table to the mastertable
end sub

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 11:44 PM