Potential AUG topic

[NauticalGent]

Stemming from this post, @Josef P. mentioned using Add-Ins to access multiple db's from a single main form.

I myself find that VERY intriguing and think it would make an excellent AUG presentation - one that I would be willing to stay up past me bed time for!

 

[CJ_London]

You don’t need addins to access multiple db’s although I suppose it is one way to do it.

depends on what you are doing but accessing the data is pretty straightforward, opening forms/reports can be more challenging

 

[NauticalGent]

Agreed. Access external tables and running queries in them is pretty straightforward, but actually using them without opening another instance of Access is something I was unaware of.

 

[Josef P.]

This is quickly demonstrated.

Call a procedure from add-in (add-on) by main application:
Application.Run "PathToAccdaFile\AccdaFileNameWithoutExtension.ProcedureName", Param1, ...
Note: accda can be an accde or accdb file.

One thing you have to pay attention to: if you want to access your own database in the add-in/add-on you have to use CodeDb instead of CurrentDb.

Example see attachment.

 

[CJ_London]

but actually using them without opening another instance of Access is something I was unaware of.

some example sql to access data from another db without assigning it to a db object

SELECT *
FROM someTable IN 'C:\path\dbname.accdb';

if a password is required

SELECT *
FROM [MS Access;PWD=mypassword;DATABASE=C:\Path\dbName.accdb].someTable

or a bit more complex

SELECT *
FROM table1 INNER JOIN table2 ON table1.PK  = table2.FK IN '' [ms access;pwd=myPassword;Database=C:\Path\dbName.accdb];

all can be wrapped as an alias to interact with local tables or tables from another db e.g.

SELECT *
FROM localtable INNER JOIN
    (SELECT *
    FROM [MS Access;PWD=mypassword;DATABASE=C:\Path\dbName.accdb].tablename) as otherdb
ON localtable.ID =otherdb.ID

In the query QBE properties, you populate the source database property - (the connection string is used when connecting to other types of data source - sql server, excel, text file, etc)

These will connect to tables or select queries but so far as I know you can't 'execute' an action query in the other db (you would need the db object to do that) but there is no reason why you can't use your own sql to insert, update or delete records in the other db e.g.

DELETE *
FROM [MS Access;PWD=mypassword;DATABASE=C:\Path\dbName.accdb].someTable

Very easy using this technique to create a poor mans 'Qlikview' application (poor because Qlikview is able to provide additional indexing to improve performance)

 

[Cotswold]

Presumably I'm interpreting this correctly but I used multiple backends linked to a single FE in various prpgrams.

One was in waste management. There were two main systems skip hire and weighbridge(WB) for a recycling plant and quarry operations. Some customers bought one or the other but most bought both programs. In that case data in one was used by and transfered to the other. In that situation, the FE to WB looked up skip jobs and wrote weights onto skip tickets. It did this as it added a new WB ticket to its own BE. Presumably I could link as many BE to one FE as would be required and it would work just fine. I thought that the FE, or Access didn't much care where the data came from, or even flagged that more than one BE was in use.

If you looked at a FE with tables from several BEs they are all just different tables. Sometimes tables in one BE had the same name as those of another BE. In those cases I would alias one of them when attaching with a differrent name to suit me and not Access.