Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Database Container _ Is There Any Way To Link Multiple Be With A Fe?

Posted by: mitsbd May 8 2019, 03:37 PM

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

Posted by: tina t May 8 2019, 03:43 PM

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

Posted by: isladogs May 8 2019, 04:33 PM

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

Posted by: gemmathehusky May 8 2019, 05:30 PM

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.

Posted by: Jeff B. May 8 2019, 05:42 PM

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

Posted by: kfield7 May 9 2019, 12:51 PM

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.


Posted by: mitsbd May 9 2019, 03:01 PM

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.

Posted by: tina t May 9 2019, 03:41 PM

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

Posted by: Jeff B. May 10 2019, 07:53 AM

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?

Posted by: gemmathehusky May 10 2019, 01:09 PM

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)

Posted by: tina t May 10 2019, 02:05 PM

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

Posted by: mitsbd May 11 2019, 04:56 PM

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.

Posted by: tina t May 11 2019, 09:55 PM

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

Posted by: gemmathehusky May 12 2019, 04:36 AM

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.

Posted by: mitsbd May 13 2019, 02:26 PM

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



Posted by: mitsbd May 16 2019, 03:29 PM

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?

Posted by: gemmathehusky May 16 2019, 04:03 PM

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