Connect different databases

[isladogs]

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.

Your original quote suggested there was a limit of 16 or 32 external databases that could be used for linked tables. As previously stated, I do not believe any such limit exists or existed in the past. Happy to be proved wrong if anyone can supply evidence to the contrary.

As for the above link, that is quoting something else entirely.
As it states, you can of course open multiple databases simultaneously from File Explorer but each opens in a separate instance of Access.

You can also open secondary databases from an existing instance of Access which may or may not open in another Access instance
For example, an add-in opens in the same instance.
However, my DB analyzer opens the database it is analyzing in a new instance. This functionality allows me to open/close each database separately.

BUT this is still totally irrelevant to the original point. There isn't a specific limit on the number of external databases you can use for linked tables

 

[isladogs]

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.

This limit is stated in Access specifications:

However, in many cases other limits may be reached before this point
In any case, I would never advise anyone creating union queries with so many union clauses. It often indicates design issues

 

[LarryE]

Do NOT use a hyperlink data type

@Pat Hartman :
Why not? Is there some reason a text field is preferable? I always knew not to store lots of OLE Objects or Attachments, but why not just the link?

 

[MajP]

Why not? Is there some reason a text field is preferable? I always knew not to store lots of OLE Objects or Attachments, but why not just the link?

My guess would be flexibility. Normally most people store only the file name in the data table and the default folder/s locations/s in a settings table. Then you create the full path in a query combining the two. This makes it super easy to move the folders as needed and only update a record or few records for the folder. Then user can do this simply with a folder browser to update the default path/s.

If you store the full path then when you move the folders it becomes a lot harder to update the new locations. A user can probably not do this easily, This requires someone (unlikely a user) to run update queries. I would think this is even more complicated with updating a hyperlink. Hyperlink fields are hard to export and display. They are made up of three parts that all may need to get updated.

Here are some drawbacks to consider. Some are valid, some not so much.

 

[Pat Hartman]

Thanks @MajP you gave Larry my answer and saved me a lot of typing There is one more reason and that is if you think the db might ever be converted to SQL Server, the Hyperlink data type is not supported and so you would have to convert the hyperlink to text and change all your code. Since most of my apps get upsized or even start out that way, I can never use the abomination data types even if I think they might be easier.

 

[LarryE]

My guess would be flexibility. Normally most people store only the file name in the data table and the default folder/s locations/s in a settings table. Then you create the full path in a query combining the two. This makes it super easy to move the folders as needed and only update a record or few records for the folder. Then user can do this simply with a folder browser to update the default path/s.

If you store the full path then when you move the folders it becomes a lot harder to update the new locations. A user can probably not do this easily, This requires someone (unlikely a user) to run update queries. I would think this is even more complicated with updating a hyperlink. Hyperlink fields are hard to export and display. They are made up of three parts that all may need to get updated.

Here are some drawbacks to consider. Some are valid, some not so much.

Thanks.

 

[LarryE]

Thanks @MajP you gave Larry my answer and saved me a lot of typing There is one more reason and that is if you think the db might ever be converted to SQL Server, the Hyperlink data type is not supported and so you would have to convert the hyperlink to text and change all your code. Since most of my apps get upsized or even start out that way, I can never use the abomination data types even if I think they might be easier.

OK Thanks.

 

[Ivy]

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?

In the meantime, thank you all so much for the replies.

For reasons of privacy of employee files (PDFs), which may contain very sensitive data and which can only be seen by the direct colleague who has to deal with that specific employee, and therefore not allow the rest of the sector (other colleagues) to see all PDFs of all the other employees.

If, for example, a colleague doesn't have any paperwork to do, she/he shouldn't see any employee's PDF.


If I also hide the folder, just do "show hidden files" and they might see them (maybe they don't know it but it could happen).

Or that I put a password (different for each PDF) and then change it after the colleague has finished the work?

(With some VBA code you can't automatically enter the confirmation password to view a PDF, right? )

 

[Pat Hartman]

If you want to store the .pdf's in a database, you need to upsize to SQL Server OR a tool that handles documents securely. What do you do with the .pdf between the time it is created and when it is imported into the ACE database? You may be fooling yourself regarding security of the documents. I think the documents need to be secured as they are created. If they are, then they don't need to be stored in the database, you can just store their names and passwords.

 

[The_Doc_Man]

For reasons of privacy of employee files (PDFs), which may contain very sensitive data and which can only be seen by the direct colleague who has to deal with that specific employee, and therefore not allow the rest of the sector (other colleagues) to see all PDFs of all the other employees.

Since object-level security was removed from Access maybe in the Ac2003/2007 time range, there is no way to stop someone from getting to those files - if they are determined to do so. Access can be hacked even if you take a great deal of effort that in the process would make the DB nearly useless because of difficulty to use. There is a trade-off here - ease of use vs. protection of data.

Understand that old adage that says that anything Man can do, Man can undo.

You might also consider this scenario: You have an employee PDF. So one of your colleagues works with that employee. A little time passes and the colleague is discovered to have committed a major violation against the company, so gets fired. Now you have this scheme in place that would block others from accessing that file... but SOMEONE has to take over responsibility for processing that employee's issues. So NOW what? Try to not paint yourself into an inescapable corner. The more you overthink this, the more inescapable the dilemma when you discover that a particular colleague was more than slightly fallible.

Or suppose the colleague gets pregnant and eventually takes a 3-month maternity leave, just as the employee needs whatever support colleagues give. NOW what?

Or suppose the employee files a complaint against the colleague and requests a new point of contact. Now what?

Beware of absolute protections because the world in which they are found is NOT absolutely predictable. At some point, you have to be able to trust someone - some PERSON - to make a decision, because programming a ton of logic is a LOT harder than having someone who can just make the decision to override the restriction.

Here is one approach (certainly NOT the only possible approach):

1. Block the place where you put the folders so that only the colleagues can see that. Use Windows group permissions to do this. Talk to your SA or IT admin about setting up a group ID for the colleagues and make the folder for the PDFs be restricted to the colleagues, no access to the general public. This involves NO CODE WHATSOEVER on your part and actually for most security managers is a valid approach.

2. In the code of this DB, you have a field somewhere that holds the ID of the colleague assigned to the case, Then you have the code that would open the PDF just disallow access to that file to any other colleague. This ID would probably be associated with the employee table and could be a small field like an integer ID number. The key is that your code doesn't allow a colleague to set that value.

3. Have a "super-colleague" who can override assignments or make new ones. So... get a new employee, the supervisor assigns a colleague to the case and you're off to the races. Get a new colleague, the supervisor assigns this person some employees just to give them something to do.

 

[Pat Hartman]

@Ivy While we appreciate your likes. Simply liking every post doesn't do anything for anyone. Most of the experts trying to help you are not children and so are not posting to see how many likes they can accumulate. It is far better for those who read this post later if you use your words. If a post helped you to solve a problem, then tell us that or if it gave you an idea that led to a solution, tell us that.

 

[gemma-the-husky]

I presume you could use user logins to secure certain folders.

Then if the user didn't have access to those folders, I presume access wouldn't have access either, for users without the permission. That would prevent a user who could see all the employees opening files for employees he's not entitled to view in detail.

But now you are getting into complicated data security issues, rather than simple database issues.