How To Link 4 Databases To Shared Tables?
May 19, 2006
I have 4 databases that share 3 tables.
Each database is used for different purposes and other tables which do not relate to the other databases.
My question is: how do I set up the back ends so each database can retrieve and enter data into the 3 shared tables without conflicts??
I know how to split each of the databases to front and back ends but how would I link them all to the 3 tables they share? Should I create one back end with only the 3 shared tables and link the separate back ends for each database???
Any help much appreciated!
View Replies
ADVERTISEMENT
Mar 6, 2008
I was just wondering what the drawbacks for using Shared and exclusive databases.
id like to create a shared database but has anyone come across any problems?
what happens when 2 people at the same time access the same row and save at the same time? is the first persons work get overwritten?
View 3 Replies
View Related
Feb 24, 2015
I have a form displaying records. I would like the user to be able to select a button, browse for a file on a shared drive and link it to that record.
I have been exploring hyperlinking, which works for the most part, although I need it to display the share name rather than the drive assignment for that user. Such as drive1folder rather than C:folder.
Hyperlinking also unfortunatly requires the user to right click on a field select edit hyperlink then browse.
View 7 Replies
View Related
Jun 24, 2005
Can anyone offer any suggestions as to how I would link two databases on two servers.
Scenario:
I have a legal database that captures issues for different member associations.
There is another database that captures issues for the same associations, though there DB will be installed on a different server.
How will I link a form in the Legal database to the form and tables in the other DB?
Thanks
View 1 Replies
View Related
Jun 13, 2012
I have a question that involves using access 2003. I have created a database with just tables in it, so that I can work, fix, create more tables if someone is working with the other database that I want to create. The other database will have the queries, forms and reports. I do not know how to link the two databases.
View 2 Replies
View Related
Nov 1, 2014
I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.
application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
It is a small application with less than 1000 records per table.
the query will allow the patient to choose an annual physical doctor once a year from available doctors.
Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.
query for all records NOT shared between Doctors and PatientPhysicalDoctors is:
SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );
View 3 Replies
View Related
Sep 6, 2006
Hi All
I have started working with a database/s that was not set up by me and I am having problems appending tables from different databases.
Overview -
There was one original database set up to collect clinical data with a 'mainTBL' (health data) and a 'subTBL' (medications data) linked within the 'mainTBL'. Rather than having one continuous database the data entry person has copied the database and used a copied version for each month (where the records were deleted so new records could be entered) - so I have six databases for six months of data entry and I need to make it into one big database.
There is an ID field in the 'mainTBL' that is an autonumber (and primary key) and this has a relationship with an ID field in the 'subTBL'.
The problem -
The autonumbers in each monthly database are not unique across the six different databases (Eg the autonumber in both february and march starts at 74 so there is duplicate ID's in february/march). I have to append all the monthly databases (including the 'subTBL's) and then start a new autonumber so they are all unique and new data can then be entered. I am having trouble appending because of the duplicates and the relationship between the ID fields - what is the best way around this.
Thanks for any help in advance.
Taigum
View 2 Replies
View Related
Feb 10, 2006
I need to do a join on two tables, one is in the current database, but the onther one is an another database. I am struggling with the syntax. Can anyone advise where I am going wrong?
This is the code, but it says the usual 'Syntax error in FROM clause'
SELECT za06_report_2006.wbs, za06_report_2006.cost_cntr, wbslist.description
FROM za06_report_2006 LEFT JOIN wbslist in '\ukdewgs002gdatapcdatafinancedocumentwbslis t.mdb' on za06_report_2006.wbs= wbslist.[wbs element];
All table names , variable names, and paths are correct.
Thanks for any suggestions
(I am very new to SQL)
View 1 Replies
View Related
Nov 4, 2013
I've currently come across the issue where an end user copied the database locally to their computer and has been using it for months and months. After fixing an issue in the database on the server and then finding that someone still had a database bug is how I figured this out. Anyway, what would be the best way for me to merge the table 1 from the database she was using to table 1 in the database on the server? Is there a way to export a table so I can then import the data? Like I said, the databases are identical other then the data.
View 3 Replies
View Related
Apr 27, 2006
I have a db that runs call stats (Master) this exports to several Department (Slave) Databses. The Slave db's can be accessed by any of number of Managers.
My problems (Amongst others!) is that I run the master every 15 mins and the slaves are linked to the master tables. How can I set this up so that these slaves can be updated whilst they are logged into?
View 2 Replies
View Related
Dec 3, 2005
Dear members,
I have two databases, Source and destination ;
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).
My code is as follows:
Private Sub CommandButton1_Click()
Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database
On Error GoTo ErrorHandler
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")
ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else
strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"
Set mdb = DBEngine.OpenDatabase(StrDestDB)
Debug.Print strSQL
Call mdb.Execute(strSQL)
mdb.Close
Set mdb = Nothing
DoEvents
MsgBox ("The Geodatabase tables have been successfully appended!")
End If
Exit Sub
ErrorHandler:
strTemp = Err.Description & " [Update_SystemTab]"
Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub
I get Syntax Error in "Insert into" clause.
Any help on this will be most appreciated.
Thanks in advance.
Irshad
View 2 Replies
View Related
Dec 8, 2006
Hello,
I have around 10 mdbs and in each I have 4 tables, I want to add all the tables from 10 mdb to one mdb, finally I will have 4 toal tables, right now I am using a appendquery to add tables. Is there way using VBA I can do it on the form and with one click of a button I will be able to add. Thanks for any info..
View 1 Replies
View Related
Oct 14, 2014
We have a case where there is an append query in one database that is is updating another database table. We have moved all the tables to MS-SQL (2008) using the SSMA tool. We were wondering if it would be possible to create a new linked table in the DB where the append query is. Of course we cannot use the SSMA tool again since the table already exists on the MS-SQL server.
Normally when I try to make linked tables from Access, it wants to create a ODBC connection which we are not using.
View 4 Replies
View Related
Feb 13, 2015
I have a club member registration application consisting of a program database and a data database. They reside in the same directory. There is an autoexec macro which runs at the beginning. This macro has to find the DATAdb and make sure that the required tables are linked (Not all tables). If it cannot find the DATAdb and then has to make a call to the user to use a different procedure.
Now to find the DATAdb I have used the following code that works. The function GetPathAndName parses the string db.name and outputs the path and name of the PROGdb. Up to here no problems.
Code:
GetPathAndName db.Name, FrontPath, dName
Then I use the following code to get the fullpath of the last database used and thereby find the path and name of the last DATAdb. The path of PROGdb is used in place of the existing path of DATAdb to check whether the file exists.
Code:
Dim rs As Recordset, cPath As String, cName As String
Set rs = CurrentDb.OpenRecordset("SELECT Database, Type " & _
"FROM MSysObjects " & _
"WHERE ((MsysObjects.Type) = 6) AND ((MsysObjects.Name) = 'MembersTbl') " & _
"ORDER BY MsysObjects.DateUpdate DESC;")
[Code] .....
As the final step I use the table definitions to refresh links that already exist and connect tables that are not linked The code is below. But I can not get it to work. It links 7 tables instead of 16 and when tested in different folder says invalid operation and so on. There is fundamental error some place but I could not figure it out.
Code:
On Error GoTo Error_Linking
Debug.Print "TableDefs.Count="; db.TableDefs.Count
For I = 0 To db.TableDefs.Count - 1
Debug.Print "TableDefs("; I; ").Connect="; db.TableDefs(I).Connect
Debug.Print "TableDefs("; I; ").Name="; db.TableDefs(I).Name
[Code] .....
View 7 Replies
View Related
May 28, 2013
I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
View 1 Replies
View Related
Sep 12, 2013
I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.
View 14 Replies
View Related
Jan 21, 2014
I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.
View 8 Replies
View Related
Sep 26, 2014
there are many threads about Table Linking but i coud not find one that answers my specific questions.
1. Can i either prevent database from linking to my tables? (or give permissins )
2. If not, can i make the table read only when linking?
3. Is there a way to confirm what databases are linked to my tables?
View 12 Replies
View Related
Mar 4, 2014
I have database for each location. Every location will update some information in the databases and send it back to me. In those location based databases, information will be stored in one table called "consolidate_input". I will store these databases in one specific directory and then I want to accommodate the data of "consolidate_input" into one master database having the same identical table in the database.
View 1 Replies
View Related
Dec 24, 2005
Hi There
i am trying to link some more tables to my access front end from the sql server back end
when i select 'ODBC Databases' from the link pop up box, the pop up box just closes
any ideas why, or work arounds would be appreciated
Happy Christmas
View 4 Replies
View Related
Jun 20, 2006
Does anyone know how to link tables automatically/VBA?
I'm trying to import files from our DB2 database in which one file is created monthly and instead of manually importing / linking the files manually, I'm looking to have access link to the new file automatically.
The file names are structured as so..
ITM01 (Jan)
ITM02 (Feb)
ITM03 (March)
Thanks
View 5 Replies
View Related
Mar 3, 2007
Hi All
I enter data in a form "WIP" that is based on a table named "WIP" in the form I have a command button. When I activate the command button it takes me into another form "Materials" which is based on a table named "Materials".
The data that I enter into "Materials" is not linked to the data ie Customer details which I have entered into "WIP".
My aim is to be able to report the materials used.
I have tried variations of relationships but am obviously missing something.
Thanks in Advance
Geoffk
View 2 Replies
View Related
Oct 12, 2005
I have a database with software
ID: autonumber
Name: text
Version: text (some say Beta etc)
Possibly 200 pieces of software
I have a database of computers
ID: autonumber
RoomName: text
ComputerName: text
Description: text
I want to link them so a computer can have up to 200 pieces of software in it or just one piece.
I dont want a table with 200 boolean fields although thats how i will end up doing it if i cant any other way.
Chances are each computer will have about 20 - 30 bits of software installed and therefore the table shouldnt need to be huge.
Anyone have any ideas how i can link these and keep the second table small. Surely i can say Computer [2] has software installed [2, 4, 5, 28, 224] but i dont know how to.
Thanks
.matt
View 3 Replies
View Related
Mar 21, 2006
I have a spilt database and both ends sit on a server. The front end has a short cut on the desktop from five machines.
On one machine I can access the backend but it i try and relink the tables, the linked table manager is empty and i cannot and the "always prompt for new location" option is not available so i cannot even do it individually that way.
Any suggestions of why this would be? Is it a security user issue (though i haven't specifically set up any user or workgroup securities)
I have even tried holding down the Shift key and that doesn't work!!
Thanks
View 1 Replies
View Related
Apr 21, 2006
Is it possible to unlink and link to tables in a back end using VBA in the front end?
I need to do this or something similar because when I send updates of the front end out I may have added an extra table and thus want to be able to get the user to run an update module to link any new tables.
Not sure if this is at all possible???
Any ideas???
View 5 Replies
View Related
May 29, 2006
Anyone can help me how to link fields in different tables. Im creating a databse that has an ID. and that ID will be same with the ID on the other table.
Table 1
ID = 123
Table 2
ID = link to (Table 1 ID) and when i input characters on the Table 1 ID field it will appear or inputed also at Table 2 ID field.
Hope some ideas. Thanks
Michael
View 1 Replies
View Related
