Can I connect my database with more than one database at the same time?

[belkhedr]

can i connect my database with more than database at the sametime

[Seth Schrock]

If you mean can you connect more than one front end database to a back end database, the answer is usually. It depends on what the back end database is and if the front end is opening it in exclusive mode. As there are many options, it would be helpful for you to provide more details.

[zmbd]

You can have more than one backend connected to the frontend.

I currently have an application connected to 4 active ACCDB formated and an SQL-Server and they want me to push to Sharepoint... YUCK!... been stalling on that as the new AZURE seems to be the next wave.

You simply need to construct the connection strings correctly and more than likely will not be able to use the "CurrentDB" shortcut while coding so make sure that your connections are named so that you can keep track of them... personally, avoid any non-alphanumeric and underscore characters and aware of the reserved, token, keywords.

[belkhedr]

at first thank you for your fast reply
what i asked about is i have one front end access database and want to connect with more than one back end at time
but both back ends have same table names

[Seth Schrock]

If the table names are the same, you can't connect to both backends at the same time. You can however create a function to switch between the two. Relinking ODBC Tables using VBA - Post #2 has a really good function to relink to a backend. It would be pretty simple to modify this to go back and forth between two backends.

The other option (Z would be able to provide more information on this option) would be to merge the data from the two existing backends into one backend. That way the frontend doesn't have to switch between them. I was part of a thread that described this very well. I'll see if I can dig it up.

[zmbd]

That's not exactly true Seth...
In the linked table manager, you would give the tables different names.

Each of my departements has a "tbl_scheduled" in the ACCDB formated files that shows the inbound sample ID and some related information. When I connected to the department backends, the "tbl_scheduled" becomes "tbl_scheduled_main", "tbl_scheduled_Dept1", "tbl_scheduled_Dept2", etc...

As for the SQL-Server, I open the connection and record sets in VBA as I need them; however, these record sets are open at the same time I am connected to the the Access database backends - with different names ofcourse.

Here, I'll post a simple one in a little bit. Currently "in the rough" in the lab so I don't have the time right now to fab one up for you.

[zmbd]

Here's one I bodged at home last night and zipped.

This is a very simple DB

This is for Access 2010; however, I've re-used the code I used for the v2003 application so it should open within 2007 although the dialog box on the main form may break.
Once extracted to your location you should have:

• (...)\bytesthread_947435_multibackend
  containing:
  • Bytes_peopleanddata_template.accde
  • (...)\bytesthread_947435_multibackend\backends
  containing:
  • Bytes_peopleanddata_template_be_1.accdb
  • Bytes_peopleanddata_template_be_2.accdb
  • Bytes_peopleanddata_template_be_3.accdb

• I've opted for an ACCDE file here as I've the MicroSoft Office 14 Objects library referenced for my applications in an effort to avoid the dialog box breaking if it's needed.
  
• I could have used seperate directories for the backends such as "(...)\backend_1\"; "(...)\backend_2\"; and "(...)\backend_3\" and left the actual backend datafile name the same. It is the link path that makes the difference.

In any case, preferably in V2010, open Bytes_peopleanddata_template.accde, the relinker should start on its own and find the backends. The code is fairly standard and should run on v2003 and up.

As the name states, this is based on a template of made-up people with first, last names, and a few with emails. Then there is a generic data table. The two tables are not related.

In the backends, I've split the people and the data into roughly thirds so people 1 thru 9 in the first, 10 thru 18, etc... same split with the data table. No queries. No Reports. Other than the file names and the data in the tables, these files are the same structurally.

In the front end, the linked tables, a few queries on the people table within each backend to show the information in each part and then a union query to show all of them as a whole table.

I've done nothing with the data table.

Ribbon, External Data, Linked Table Manager
You can see how the tables are linked to different backends.
You could also do this without using linked tables via various VBA methods... this is just an example.

[belkhedr]

at first thank you for your fast reply i downloaded tamplate but i found that table names inside every back end are no with same names
my problem is i have on front end contains forms an reports and many back-ends contains tables with same structure but data differs one back end for one year

[zmbd]

belkhedr:
I'm sorry, I don't follow you at all.
Did you extract the file or attempt to use it in "zip" state?

As for the example. It has a form... that links, and several queries. I didn't think that you'd need much more than an example about how things could be done.
If you are asking for a complete project, please understand that such would be against the site rules.

[belkhedr]

hi
sorry for bad English
i downladed your example
it contains 3 back-ends
table names inside each are not the same
----
i asked about linking one front end
with many back ends at time
all back ends are with same table names

thanks

[zmbd]

Let me clear that up:

Open each of the back-end files directly. You will find that the tables within each file has the exact same structure and names: "tbl_data" and "tb_people"

I know this for a fact because I took the original template, made three copies of that master, renamed the copies are they are now, altered the data directly within each of those files. I've even downloaded the file to a third PC to verify that there were no alterations to the construct.

I then opended a blank database and created the front end. Linking to the backends... now you can not have the same table names within the front end. If I had tried that then the Access would have appended a "(#)" to end of each of the linked tables.

This is the same as I stated would happen in post #6

the department backends, the "tbl_scheduled" becomes "tbl_scheduled_main", "tbl_scheduled_Dept1", "tbl_scheduled_Dept2", etc...

This will be same with your front/back-end links.

I will be out most of the day today; however, hopefully I will be able to get back to your questions this evening (CST).

[Z{edit{Added the jpg showing all three of the backend files opened on my PC at home. I didn't place inline as I didn't think everyone would need to see it :) }]

[belkhedr]

thank you
and sorry for wasting your time

[zmbd]

@belkhedr
Absolutely no need to apologize... you did not waste my time, especially if one considers that English isn't your primary language.... it just takes a little longer to get the correct wording.

My hope here is that you can use the information to solve your problem and if that had taken another dozen posts then time well spent.

Best of Luck with your project!