Creating Link Between Two Tables
Aug 15, 2015
I have data model as below....I have created a unique constraint as below;
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
[StaffID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?
View 15 Replies
ADVERTISEMENT
Feb 20, 2008
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
View 1 Replies
View Related
Feb 3, 2006
When trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
View 8 Replies
View Related
Jul 12, 2006
Hi I'm trying to create link to oracle db and keep getting this error when I try to look at the oracle tables from enterprise manager.
error 7399: ole db provider microsoft.jet.oledb.4.0 reported an error
ole db error trace [ole/db provider microsoft.jet.oledb.4.0 idbinitialize::initialize returned 0x80004005:]
I can connect to oracle db from sql server using oracle sqlplus and I have a ODBC connection to db that works.
sql server 2000
os = MS windows server 2003
oracle client 10.2
View 2 Replies
View Related
Apr 25, 2000
I have used to following Microsoft supplied code to create a link to
SQL server from access: (Is there a way to use a DSN-less connection at comment below? If not is there a way to create a DSN using code?
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Thanks, I really appreciate the help
Sam
View 1 Replies
View Related
Apr 20, 2008
Hi Everyone, I have a webform. I need to save the data inside the form into 2 tables. For that i have 2 separate buttons inside the form. tbl_jobPost(JobId,JobName,StartDate,EndDate,phone,email,jobFile_id) tbl_jobFile(FileId,Filename) 1st i ll be inserting the data into the tbl_jobFile:-(FileId,Filename) like this:- (1,abc) (2,xyz) (3, fsd)Then i will be inserting the data into the next table:-tbl_jobPost(JobId,JobName,StartDate,EndDate,phone,email,jobFile_id) like this:- (1,job1,2008,2009,4534535435,zzz@eee.com,jobFile_id)My question:-How should i pass all the jobFile_id from tbl_jobFile into tbl_jobPost row no. 1?Can you suggest any good solution for this?I m new to Foreign key.Do i need to use here.?Thanks.jack.
View 14 Replies
View Related
Oct 18, 2006
Hello all, I have a quick question. Is it possible to create linked tables in MS SQL, similar to the was MS Access does?
I have a new application(x) that is going to be dependant on another application(y). I just want to link 5 or 6 tables from y into x for the purpose of not duplicating information. Is this possible?
I apologize if I seem a little lacking in the knowledge of MS SQL, I am new to it and trying to figure it out. Any help would be greatly appreciated.
Thanks,
Dan
View 5 Replies
View Related
Jun 26, 2006
Hi all,
I would like to simply link two tables that are in two different databases in the SAME server.
I know that I could use the replication method (snapshot or merge) but I need a simpler method like the Access link table method.
Any suggestions?
Thanks
Alessandro
View 8 Replies
View Related
Apr 2, 2008
Hi,How can I run a query to figure out what tables and correspondingcolumns link to any column in my table T?Thanks, - Dave
View 1 Replies
View Related
Jan 13, 2000
I have linked some SQL7 tables to MSAccess, then I created more tables in the SQL7 database. Trying to link them in MSAccess97 but only the old SQL7 tables show up in the list of choices that can be linked. How to get the newly created tables to show up on the list?
View 1 Replies
View Related
Mar 18, 2004
Hello All,
I am new to this forum and hope that some one can help me with this problem.
I know that in MS Access you can have a linked table in your databse such that as long a the like is not broken, it behaves similar to any other table in the database.
My problem is that I need to do the same thing for multiple databases that I have on a MS SQL Server that ALL currently have copies of the same table in common.
I wish to have a single table that they all share and have access to just link any other table that they currently have but is actually linked.
Can this be done?
And if so, then could someone please guide me in this?
My logic is that if MS Access can link tables then I could suspect that MS SQL Server should be able to also, but I could be wrong.
Thanks in advance,
Lonnie
View 5 Replies
View Related
Apr 24, 2001
Anyone has any idea why I havethis problem?
I have a SQL 2K database on a named instance with tables populated with data and they have no relations between them.
I have a MS Access 2000 database.
I have a system ODBC connection on the client computer to the SQL 2K using trusted connection.
I linked the tables into MS Access 2000 to SQL 2K and I can read the data without any problem.
When I try to update the data on the table, I get the message : The recordset is not updatable.
I am the DBO. So I am supposed to have all rights. All the select, update, insert and DRI rights are enabled.
I tried it with another user and it does not work either.
Anyone has an idea?
Thanks
View 2 Replies
View Related
Jan 24, 2007
Amir writes "Dear Team Members;
Im writing a program in ASP language which it is using SQL Server for database.
I want to know how to create a link between fields of two tables. For example:
I have a table named: Projects containing:
p_name
status
date
member1
member2
and another table named results containing:
p_name
s_status
status1
status2
I want to create a link between field s_status of STATUS table and field status of table Projects
Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.
Could you please help me how to do it?
Regards.
Amir Zandi"
View 1 Replies
View Related
Sep 2, 2007
I realize this a Transact SQL forum. If there is another forum more appropriate, please let me know.
I want to think about the best way to begin to set up this database with respect to the column that will link these tables together.
The info below is what has been given to me as my client's list of values/info she would like to include.
Here is my first pass, knowing that the ID columns need to be fixed. Thank you!
http://www.hazzsoftwaresolutions.net/db_diag.htm
Patients
ID
First Name
Last Name
DOB
1234-12
Joe
Smith
3/1/1960
5432-30
Bob
Jones
1/3/1991
3232-22
Paul
White
5/12/1982
PatientVisits
AutoID
PatientID
VisitDate
WeightPounds
HeightInches
1
1234-12
10/11/2001
180
68.5
2
1234-12
2/1/2003
185
68.7
3
5432-30
4/5/2000
155
63.0
4
5432-30
11/6/2000
165
63.0
5
5432-30
5/12/2001
164
63.5
6
3232-22
1/17/2002
220
75.0
View 3 Replies
View Related
Mar 7, 2000
Hi All
My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries.
I am thinking of linking server but I am not sure about that.
Is someone can tell me what to do and explain me how.
Thanks in advance
Sincerely.
David
View 2 Replies
View Related
Jan 18, 2007
Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -
View 2 Replies
View Related
Jan 30, 2008
Hi All, I have recently moved jobs. From my last job I created a holiday database for the organisation, than I copied it on a storage device. Now, in my new job I would like to use same database. But the problem is most of the tables were linked. I know I can convert linked tables to local tables and I have tried it but it asks me for the new location with the same table names. Can some one please helpppppppppppppppppppppppppppppppppp I as I am really very desperate.
Thanks in advance.
View 1 Replies
View Related
Feb 27, 2007
I am trying to do the following:
Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems.
I'm inserting rows from a Table called EATemplateItems.
So far I have something like this: (I have the PackageID already at the start of the query).
INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.
Any Advice / help would rock! Thanks
View 3 Replies
View Related
Nov 29, 2006
menardo writes "Hi there!
I'm a very newbie in SQL programming. Can anybody help me how do I start to learn scriptings.
Can you give me the process or scripts on how to export it??
Thanks in advance.
-menardo"
View 2 Replies
View Related
Sep 13, 2007
Hi,
I want to design a report in which it will contain fields derived from 2 different stored procedures. I understand a 'table' can display data from a single dataset. How can i bind these two stored procedures into a single dataset so as when i click on the table and use its property 'DataSetName', to be able to select the dataset which holds all columns from stored proc 1 and stored proc 2. How can i link multible tables ( multible stored procedures with different column names in each one) into a single dataset to feed the report?
Thank you
George
View 1 Replies
View Related
Aug 13, 2015
I'm pulling data from XML into tables, but I'm unsure how to link the data after it's imported. This example has names and tasks, and I can pull the data into two tables, but I can't find any way to link the task to the appropriate person. My person and task tables populate without issue, but there's nothing I can find to link the rows together. So in this example Test 1 would go to the first two Tasks and Test 2 would go to the second two work items.
DECLARE @XML TABLE (XMLData XML);
DECLARE @Person Table (Name NVARCHAR(50), Addresss NVARCHAR(50));
DECLARE @Task Table (Name NVARCHAR(50), Details NVARCHAR(50));
INSERT INTO @XML SELECT '
<process>
<header>
<Person><Name>Test1</Name><Address>123 main street</Address></Person>
[Code] .....
View 9 Replies
View Related
Sep 24, 2007
Hi
I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.
Thanks for any help
View 1 Replies
View Related
Jul 16, 2015
Any tool, script, procedure, or otherwise that will solve this problem?
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
View 7 Replies
View Related
Jul 17, 2015
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
View 2 Replies
View Related
May 31, 2007
hello,
What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.
Thanks
View 1 Replies
View Related
May 6, 2002
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3
Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
I hope that this makes sense!!
View 1 Replies
View Related
Nov 29, 2005
Chris writes "How do I create a copy of a table (not temporary).
In Oracle I do .
Create table newtable as select * from oldtable
This command copies data and column data types.
I tried entering
SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable
in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.
I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."
View 6 Replies
View Related
Oct 29, 2005
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil
View 31 Replies
View Related
May 1, 2008
I'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.
I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...
any suggestions?
Thx
View 4 Replies
View Related
Apr 10, 2006
I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is
Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()
Next
I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]
Can anyone see what I am doing wrong.
View 5 Replies
View Related
Apr 27, 2007
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.
Thanks
View 2 Replies
View Related
Oct 16, 2007
hi all,
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
View 1 Replies
View Related
Apr 7, 2008
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
View 11 Replies
View Related
