473,322 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

can i connect my database with more than database at the sametime
Mar 3 '13 #1

✓ answered by 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 :) }]


12 2494
Seth Schrock
2,965 Expert 2GB
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.
Mar 3 '13 #2
zmbd
5,501 Expert Mod 4TB
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.
Mar 4 '13 #3
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
Mar 4 '13 #4
Seth Schrock
2,965 Expert 2GB
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.
Mar 4 '13 #5
zmbd
5,501 Expert Mod 4TB
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.
Mar 4 '13 #6
zmbd
5,501 Expert Mod 4TB
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.
Attached Files
File Type: zip bytesthread_947435_multibackend.zip (151.6 KB, 113 views)
Mar 5 '13 #7
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
Mar 7 '13 #8
zmbd
5,501 Expert Mod 4TB
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.
Mar 7 '13 #9
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
Mar 8 '13 #10
zmbd
5,501 Expert Mod 4TB
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 :) }]

Attached Images
File Type: jpg bytesthread_947435_openedbe.jpg (54.8 KB, 476 views)
Mar 8 '13 #11
thank you
and sorry for wasting your time
Mar 8 '13 #12
zmbd
5,501 Expert Mod 4TB
@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!
Mar 9 '13 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: bettina | last post by:
Hello, My domain: www.coaster.ch My database: coasters User: bettina In the programm I wrote: <?php $db_server = "www.coaster.ch";
0
by: Kate Perry | last post by:
I am trying to execute a query on a db2 database using the dbi module. I'm wondering if someone can take a look at my code and tell me what I'm doing wrong. I'm assuming it's a problem with my...
1
by: DAVID | last post by:
Hello, With regards to the Connect dialog on Oracle forms 6i via which we can connect to Oracle database, should we use the same functionality on forms run time again? I mean, I want to make a...
6
by: Jeff Sandler | last post by:
I have a database I created in mySQL. I've been entering data every day into the database using a Java application that I wrote. The database and the Java program are on the same Win 98 SE...
3
by: Lee | last post by:
Hi, I'm developing a socket program to connect to Informix database through the ODBC. In here i called my socket program as "tap" . My tap will listen for data from unix through port 1070. After...
4
by: Scott Holland | last post by:
HELP - Need to connect to DB2 database on AIX from NT server. Also AS/400 from NT Server -- I am experienced in ORACLE and a novice at DB2. What tools would be the equivalent of Net*8 or...
3
by: Jassim Rahma | last post by:
I would like to know what is the best way to onnect to connect to a database in general which provides you with full functionality & fast access? Best Regards, Jassim Rahma *** Sent via...
0
by: vicky | last post by:
Hello Experts, Trying to run sample Postgrel's ECPG(Embedded SQL)programs on RHL 9.0. Im unable to connect to PostgreSQL database (sirishadb) when I run the program .... # su postgres...
3
by: Familjen Karlsson | last post by:
Here is an example from the help on the keword OleDbConnection, in VB.Net, they don't give the path to the database just the word localhost. How can it connect to the database without knowing where...
11
by: =?Utf-8?B?c2NodXJzdDMw?= | last post by:
Hi, I am currently trying to get back into ASP and have begun looking at an old webpage I developed some years ago, it was fully functional back in 2002 and I had it up and running with no...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.