Connect different databases

[Ivy]

Hi,

I have created 3 databases: employees1, employees2, employees3.

In each database there are exactly the same tables (e.g. personal data table and career table) but with obviously different data. In practice I had thought of creating own database for each employee, and then connecting all to a database that only served as a grouping and search of data but the real data and tables are all subdivided.

I then tried to connect all 3 tables (employees1, employees2, employees3) to a database which I called for example "import.accdb" and I wanted to have the list of all the records contained in the tables.

So I make the query, insert employees1.*, employees2.*, employees3.* but the results all come to me on a single record, in practice it has merged all the tables.

Instead, I wanted to "queue" all the records in the tables, each record a row, so as to have the list of all the employees but from different databases.

Is it possible to do this?

Thanks in advance

 

[GPGeorge]

Given the uncommon approach here, it's hard to see how this can be a successful design. But assuming for a moment that you will eventually sort out the various problems it introduces, let's focus on what this statement means.

"... insert employees1.*, employees2.*, employees3.* the results all come to me on a single record".

Are you saying that there is a single record in some destination table?
That snippet of what appears to be SQL is incomplete. What is the full SQL of your query? We can possibly glean more information from it.

I should add, more clearly, that I think this approach is going to cause many more frustrations and complications than any possible benefits it might offer. I think I might guess why you thought to do it this way, but rather than guess, would you mind explaining why this approach was chosen? What problem does it avoid?

 

[Ivy]

Hi, to manage an employee archive I will have to upload many PDFs and I will necessarily have to do it in an "attached" way, so the database will certainly exceed the maximum size of 2GB in a short time. SQL Server and other databases cannot be used.

I had therefore thought of splitting the database, either for each employee his own database or at most aggregating 200 employees (if each employee database weighs 20mb) and then connecting the databases.
I have around 6000 employees, so at least 30 databases I should connect

However it wouldn't be a problem, if I just have to connect them manually, I can do it in 5 minutes.

The problem is that I don't understand how to have the results divided by records, Access merges all the fields of all the tables into a single record and I don't want this:

 

[LarryE]

What data are you attempting keep track of?

 

[Ivy]

What data are you attempting keep track of?

Personal data and work history, with related PDF attachments.

I would like something like this (I did it manually):

Or that I manually create the employee list and then try to link the individual employee via employee ID?

 

[ebs17]

Hi, to manage an employee archive I will have to upload many PDFs and I will necessarily have to do it in an "attached" way, so the database will certainly exceed the maximum size of 2GB in a short time

This is a problem of our own making. If necessary, you should move the attachments to additional backends, but not the actual data.

 

[Ivy]

Anyway with Union Query it works:

 

[The_Doc_Man]

While there are very few absolute rules, a NEAR-absolute rule is that you NEVER embed a file such as a PDF in at database. Instead, you set aside a folder (or hierarchy of folders) where you keep the individual external files organized and in place of the embedded file, you store text fields containing a hyperlink to each of those files.

Whether you embed the files or hyperlink to them, in EITHER case you cannot directly open them with Access anyway. Instead you tell Access to ask Windows to open the file in question. From your viewpoint and from the system viewpoint, the action of opening an embedded or external file is about the same. The biggest difference is that your external individual files take up the same amount of disk space - but their containers have vastly different limits. Directories can hold a large number of files. For example, look at the /Win32 folder on your system disk. Whereas you have run into the 2 GB limit of the back-end file.

If you remove the embedded files and replace them with hyperlinks, your design of multiple identically structured database files collapses into one MUCH smaller file with a bunch of individual external files. The hyperlink method only burdens you with about 1/8 to 1/4 of a kilobyte TOPS for each file (file specs from 128 to 255 bytes long) as opposed to however many KB they take up when embedded.

There is another "gotcha" to consider. If you try to tie those DBs together, you can - but there is a limit on the number of database files you can link into a unified whole. Depending on your version of Access, you have a limit of either 16 or 32 databases. Your method would grow pretty fast if you are storing many KB per PDF file embedded in the DB files. Perhaps 32 to 64 times faster for small PDF files and a LOT faster if the files get bigger.

EDIT: (by The_Doc_Man) The limit of the number of simultaneously open databases in a single workspace may have been extended since the last time I ran into this problem, admittedly not recently. Therefore, do not rely on that limit existing for more recent versions of Access.

 

[GPGeorge]

The_Doc_Man has addressed the underlying problem as well as the other key problem of splitting data into multiple accdbs.

Storing binary files, i.e. PDFs, Word Docs, images, etc. inside an accdb is possible, but highly undesirable. And even more so with the large volume of information you need to track.

If you need help structuring the tables to work with external files, let us know and we can help with that.

 

[LarryE]

You need one database with three tables:

1. Employee Table
2. Job Table
3. Work History Table

The employee table keeps all employee data
The Job Table keeps all Job data within the company
The Work History table is a junction table that brings together each employee with their related job. When an employee has a different job, you simply create another record in the Work History table.
LIke this:

Then as Doc said, create your hyperlink field in one of the tables to hold your pdf link. Of course I just made some of the fields based on common sense. You will need to create whatever fields you require for each table. But the Employees table and the CompanyJobs table is connected in the WorkHistory table with Foreign Keys as shown.

 

[Ivy]

This is a problem of our own making. If necessary, you should move the attachments to additional backends, but not the actual data.

Hi, to do this I have to give each attachment (OLE? or normal attachment) its own ID?

@The_Doc_Man and @GPGeorge: I know it, but I can't link PDFs directly from folders so, I have to find another way

 

[gemma-the-husky]

@Ivy
What happens when you have a fourth or fifth employee. What happens when you only want to select a selection of the employees. Are you going to change your union query each time.

Also you do know that you can't edit data within a union query, don't you.?

You will end up giving yourself perpetual issues by working with your data in such a curious way.

 

[ebs17]

Anyway with Union Query it works

However, with the UNION query you subsequently lose all index usage. A real database regularly comes with a lot of data, and you need some resources to achieve satisfactory performance.

but I can't link PDFs directly from folders

What does that mean?
If you have or can compile the full path in a file system in the database, you can open both a single file and a directory with Application.FollowHyperlink or better ShellExecute. This is often a better way than storing files directly in the database.

One could also ask what role the archive should play. For an accessible archive, it is cheaper if the files are stored in a file system. Then you could add, copy or view such files without the Access application.

For a solution for a financial service provider, I solved it so that each customer has their own directory with the customer's name and customer number within a master directory. This means that a folder can be accessed via Explorer, from the DB application directly via the customer number.
The files are stored in the customer folder with a meaningful name and time stamp so that you can draw conclusions about the contents and are not forced to open each document first.
With some customers you have a lot to do, so you can create and use subdirectories within your directory for logical structuring.

 

[GPGeorge]

Hi, to do this I have to give each attachment (OLE? or normal attachment) its own ID?

@The_Doc_Man and @GPGeorge: I know it, but I can't link PDFs directly from folders so, I have to find another way

What does that mean? You can't link PDF's directly from folders? Access developers do that all the time!

All you need is a field in the table containing the path to the PDF....

 

[The_Doc_Man]

Hi, to do this I have to give each attachment (OLE? or normal attachment) its own ID?

@The_Doc_Man and @GPGeorge: I know it, but I can't link PDFs directly from folders so, I have to find another way

To be able to embed the field into a database, you must be able to copy it from its source to the place where your DB file resides. If you can copy it INTO the database file, you can copy it to a set-aside storage place like a sub-folder of the folder where you would have kept the embedded version. So... you just copy the files NEXT TO the DB rather than INTO it. You have enough disk space to contemplate embedding the file on that disk, so you have the same amount of space on the disk to hold the folder and copied (but separate) files.

Also: Within Windows, if you know the device, path, name, and type - that IS the file's sufficiently unique ID that the file system can always find it. Windows files DO have other obscure IDs but device/path/name/type IS unique. If you were thinking about some sort of implied security by hiding the PDFs inside the DB file, Access security is pretty weak. If your users can get to the DB and actually use it, they have enough access to open a dummy database to use as a way to get into your DB files.

You must explain your inability to link PDFs directly, because (not trying to be mean here, but... ) that inability just doesn't make sense. WHY can you not make a hyperlink to a file that would ideally be on the same disk as the database file, and in a folder presumably under your control?

 

[isladogs]

If you try to tie those DBs together, you can - but there is a limit on the number of database files you can link into a unified whole. Depending on your version of Access, you have a limit of either 16 or 32 databases.

I've only skimmed this thread and I apologise if I'm taking this out of context
Access has many specific limits including the number of 'open tables' (4096 / 2048 depending on version), but I don't believe there is (or ever has been) a limit on the number of databases you can connect to.
Its certainly not listed in this article: https://support.microsoft.com/en-us...ications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

A quick Bing search listed your post as the only source of information for this. Its now being quoted by Co-Pilot and I think its not valid
Can you provide an official link where this limit is stated?

 

[LarryE]

Does each employee have a pdf file? It's easy to select a pdf file and then associate it with an employee through a hyperlink field. Do you need to know how to do that? As I said earlier, you only need one ACCESS db file with 3 tables. The Employee table can hold each employees pdf file data. 6,000 employees is not even close to being a large file for ACCESS.

If you have all 6,000 records with employee names in a text file or EXCEL file or other file acceptable to ACCESS, they can all be imported into a table at one time.

 

[Pat Hartman]

You are really making a nightmare of maintenance for yourself. As @LarryE mentioned, 6,000 employees is a trivial number. As the others have mentioned, embedding the PDF's will take up a huge amount of space so it is better to just log the file name. I keep the path separate so it is easy to change should I have to move folders. I can update 1 record instead of thousands. Do NOT use a hyperlink data type. Just use a standard text field. Then you can use the FollowHyperlink method in a button or the double click event of the file name field to open the pdf or whatever file type it is. As long as the extension is known to Windows so it knows what app to use to open a file, the FollowHyperlink is the simplest way to open external files.

Use one database. Import all the data into a normalized schema. Make sure you have a termination date for the employee table so you can separate active from terminated employees.

 

[The_Doc_Man]

Can you provide an official link where this limit is stated?

Actually, it even gets worse. This reference says "one database at a time."

Open an existing database - Microsoft Support

support.microsoft.com

It used to be, many years ago, that you were limited to the number of different databases you could have simultaneously open in a single workspace. But I am having trouble finding that reference, too. I have edited the article to indicate that it was a historical, not current limit.

 

[xavier.batlle]

Only for fun and after some tests, I realized that I could create a query with 50 UNION tables, when I tested the process with 51 an "Expression to complex" error raised.