Link two tables using partial word match

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 Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road

Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS

The output should be:

123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street

Could this be done?

Thank you in advanced.

- Grasshopper -SQL Learner wrote:

Quote:

Originally Posted by

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 Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road
>
Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS
>
The output should be:
>
123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street
>
Could this be done?
>
Thank you in advanced.
>
- Grasshopper -

SELECT t.* FROM tblStreet t WHERE EXISTS(SELECT 1 FROM tblWord w WHERE
t.Street LIKE '%'+Word+'%')

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thank you so much Alex! It works. The only thing is that I replaced
the "%" with "*" since this is Transact SQL in Access. : )

By the way, is there any good book that I can use to learn SQL of this
kind?

- Grasshopper -

Link to another SQL database table

Hello All,
I have a question on linking tables with SQL Server. I have SQL Server 2000 with database A on machine A. I have SQL Server 2000 with database B on machine B. I want to link tables from the database on machine A into a database on machine B. I am not quite sure how to do this - I believe this can be done as I have seen it before. Any information and or resources to direct me to would be greatly appreciated.

ThanksYou can add a linked server using Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_1ozd.asp), or using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) from within Query Analyzer.

-PatP|||http://www.databasejournal.com/features/mssql/article.php/3085211 for detailed explanation.



HTH|||Thanks for the information, I appreciate the responses.

I just have a follow on question. It seems that you can link an entire SQL server but is there any way around this if you cannot have full access to all the databases and tables within?

For example, the SQL Server I need to link to has several databases. I need to link to 4 tables in one database. I will not have the authority to link in the entire database. With Access I can link to a database and select the tables I need to link in, with the SQL Server method I don't see that I can do this (it just may be that I am not doing this right - not too familiar with SQL Server).

Any ideas?
Thanks|||For the linked server privilege to access entire database is not required, you can control the privileges for any logon pertaining to a table in the database.

If the underlying user has required privilege to access the table, then LS connection will be successful and only data can be retrieved from permitted tables.

Refer to the books online for more information on Linked servers and its security.
H
TH|||A linked server gives one server (machine) access to another machine. The user is what determines the permissions, so even though you only expect to be able to "see" or use four tables, depending on the login/user being used you may have access to some other number of tables. You might have a user on your local system that has no permissions at all on the remote system. You might have another local user that would have administrator rights on the remote system, therefore they could access every table.

This probably seems a bit complex after dealing with the much simpler security model that MS-Access (actually Jet, the underlying database engine) provides, but it is actually much more powerful.

-PatP|||Thanks for the information - and thanks to all the responses I am now getting further. In a testing environment I have created the user, limited access to the tables and currently I have used the linked server feature to link in the database.

The main point of doing this was so that I could create a view in my main database to combine data from the linked in database tables. This where I am running into a problem. I keep receiving a message "Could not find "servername" in sysservers, please run sp_addlinkedservers". I have followed the article provided in this thread but was not successful, I cannot get any of the items to execute, I receive syntax errors.

I am not sure if I am in over my head here. Any help would be appreciated.|||Usually at least, linked server information isn't security sensitive. If it is ok, can you post what you tried to execute and what error messages you received? Without at least some hints, I can't help much!

-PatP

Link to an Access table from SQL Server

I'm trying to find how to link to an Access table from within SQL Server. I know I have seen it once, but can not remember where I saw it. I'm using SQL Server 2005.

TIA,Not sure it's the "best" way, but you can create a linked server to an Access db in SQL 2000 (don't have 2005 yet). From there you can get at the data in QA, etc.|||Thanks Paul, but where do I go within SQL Server to create a linked server?

I can't find the menu/or whatever to use to get to the "Add Linked Server" or whatever it is called, page/dialog box.|||In Enterprise Manager, it's under the Security folder. I did it through there to test, but according to BOL you can also:

Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.

For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:

sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'|||Thanks Paul,

Under Security? I knew the first time I found it I thought it was in a strange place, and I've always had a hard time remembering where things are until after I've used them several times. I usually remember where my golf clubs are though. :)

Vic|||If you're like me, the clubs are always in the trunk, ready for any last minute opportunities! Though as I stare at the 6" of fresh snow on my deck right now, I must admit those opportunities are few and far between this time of year. :eek:

Maybe I should roll down there this weekend.

Link to a table

There is a way to create a link from a SQL Server database to a table located on a MSAccess database? I mean like creating links from MSAccess to other databases. The requested table is updated many times/day, and I dont want to import the table each time an update happens.

Thanks,

Richard

Sure. You can use linked servers, which is easy to configure in Enterprise Manager. You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

|||Thanks!

link tables to sql server by code

using access 2000
how i can link a table from sql server by code?
Your best bet is to write this code in Access/VBA. The code creates a
DAO TableDef object and sets its various properties (connection
string, etc.) and appends it to the TableDefs collection. Here's a
sample where tdf represents a TableDef object:
Set tdf = db.CreateTableDef(strLinkName)
' Set the Connect and SourceTableName
' properties to establish the link
tdf.Connect = strConnectionString
tdf.SourceTableName = strSourceTableName
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
--Mary
On Wed, 29 Dec 2004 16:44:35 +0200, "Sam" <focus10@.zahav.net.il>
wrote:

>using access 2000
>how i can link a table from sql server by code?
>

link tables to sql server by code

using access 2000
how i can link a table from sql server by code?Your best bet is to write this code in Access/VBA. The code creates a
DAO TableDef object and sets its various properties (connection
string, etc.) and appends it to the TableDefs collection. Here's a
sample where tdf represents a TableDef object:
Set tdf = db.CreateTableDef(strLinkName)
' Set the Connect and SourceTableName
' properties to establish the link
tdf.Connect = strConnectionString
tdf.SourceTableName = strSourceTableName
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
--Mary
On Wed, 29 Dec 2004 16:44:35 +0200, "Sam" <focus10@.zahav.net.il>
wrote:

>using access 2000
>how i can link a table from sql server by code?
>

Link Tables in SQL Server

Can I create Link Table in SQL Sever that links to a MS Access Table?You create a "Linked Server" to the Access database.

See Books OnLine under Linked Servers.|||You can use the OLEDB provider for Jet for MS Access database.

Link Tables in SQL Server

Can I create Link Table in SQL Sever that links to a MS Access Table?You create a "Linked Server" to the Access database.

See Books OnLine under Linked Servers.|||You can use the OLEDB provider for Jet for MS Access database.

Link tables from other database into SQL

I was wondering if anyone knew of a way to dynamically link a table from a
COBOL (I think) database into a SQL database. I have an ODBC driver that I
have been using to link the tables into Access, but would like to do the same
in SQL Server 2000.
Thanks in advance for any replies!
Cobol is a language so I'm not real sure what database you
are trying to connect to. But if you have a driver for the
database, install it on the SQL Server box. For the linked
server, you'll need to follow similar steps for the
configuration that you used in Access when linking the
tables. Look up linked servers in SQL Server books online to
find more information on creating linked servers in SQL
Server. You link to the server/database in SQL Server rather
than individual database objects as you do in Access.
-Sue
On Fri, 4 Mar 2005 07:49:09 -0800, "cjohnson300"
<cjohnson300@.discussions.microsoft.com> wrote:

>I was wondering if anyone knew of a way to dynamically link a table from a
>COBOL (I think) database into a SQL database. I have an ODBC driver that I
>have been using to link the tables into Access, but would like to do the same
>in SQL Server 2000.
>Thanks in advance for any replies!

Link tables from other database into SQL

I was wondering if anyone knew of a way to dynamically link a table from a
COBOL (I think) database into a SQL database. I have an ODBC driver that I
have been using to link the tables into Access, but would like to do the sam
e
in SQL Server 2000.
Thanks in advance for any replies!Cobol is a language so I'm not real sure what database you
are trying to connect to. But if you have a driver for the
database, install it on the SQL Server box. For the linked
server, you'll need to follow similar steps for the
configuration that you used in Access when linking the
tables. Look up linked servers in SQL Server books online to
find more information on creating linked servers in SQL
Server. You link to the server/database in SQL Server rather
than individual database objects as you do in Access.
-Sue
On Fri, 4 Mar 2005 07:49:09 -0800, "cjohnson300"
<cjohnson300@.discussions.microsoft.com> wrote:

>I was wondering if anyone knew of a way to dynamically link a table from a
>COBOL (I think) database into a SQL database. I have an ODBC driver that I
>have been using to link the tables into Access, but would like to do the sa
me
>in SQL Server 2000.
>Thanks in advance for any replies!

Link Tables

Hi,
Trying to link tables from Access 2000 to SQL 2000. Am successfully linking
tables as long as there are no spaces in the table name.
tableA from access to TableA to SQL link successfully.
table B from access to TableB or table B get odbc -- call failed
help
stoneystoney wrote:
> Hi,
> Trying to link tables from Access 2000 to SQL 2000. Am successfully linking
> tables as long as there are no spaces in the table name.
> tableA from access to TableA to SQL link successfully.
> table B from access to TableB or table B get odbc -- call failed
> help
> stoney
Hi Stoney
You don't say how you link the tables, but try to put [] around the name
- e.g. [Table B].
--
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql

Link Tables

I have two databases and one of them contains a table that
I would like to link between the two. I have used the link
table wizard to link a table from a secondary database
with Microsoft Access and was wondering if there is a way
to do this in SQL Server 2KThank you so very much!!!|||Try a View
CREATE VIEW DatabasenameN.Owner.VW_test
AS
SELECT a.V1, a.V2, b.V3 ......
FROM Databasename1.Owner.Table1 a
JOIN Databasename2.Owner.Table2 b on a.Vx = b.Vy
WHERE ......
Hope this Helps

Link Tables

Hi,
Still i m not getting the result. My question is can link the table in SQL like MSAccess has.(from other database we can use the table). Bcz i want a data from other database table and use the table data for other database.

Thanks
imtdid u mean to join the table or database....

Quote:

Originally Posted by imtmub

Hi,
Still i m not getting the result. My question is can link the table in SQL like MSAccess has.(from other database we can use the table). Bcz i want a data from other database table and use the table data for other database.

Thanks
imt

|||

Quote:

Originally Posted by reon

did u mean to join the table or database....

I want if the data change one table it need to sutomatically update second table. The both table are not in one database. They are diffrent database in sql server. In Access i can use link table to get data from another database table. In sql i don;t know how to use. If anybody knows how to do this option it will very helpfull.

Thanks

Link Tables

Hi,
Trying to link tables from Access 2000 to SQL 2000. Am successfully linking
tables as long as there are no spaces in the table name.
tableA from access to TableA to SQL link successfully.
table B from access to TableB or table B get odbc -- call failed
help
stoneystoney wrote:
> Hi,
> Trying to link tables from Access 2000 to SQL 2000. Am successfully linki
ng
> tables as long as there are no spaces in the table name.
> tableA from access to TableA to SQL link successfully.
> table B from access to TableB or table B get odbc -- call failed
> help
> stoney
Hi Stoney
You don't say how you link the tables, but try to put [] around the name
- e.g. [Table B].
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Link table from another database?

Recently moved on SQL Server 2000.
Looks like I saw somewhere, but can't find now if can link the table from
another database on the same SQL Server 2000.
Also if it's possible, then question is if possible to link table from
another remote
SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
example.
Regards,
Michael
If the tables both reside on the same instance of SQL Server there is no
need to define a linked server. Just use three parts of the four-part
naming convention:
SELECT a.Col1, b.Col2
FROM DBName.Owner.TableName a
JOIN DBName.Owner.TableName b
ON a.Col1 = b.Col1
If they reside on seperate instances, you can define a linked server (see
Books Online for full information)
and reference the tables the same way except use
LinkedServerName.DBName.Owner.TableName
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:uv6WgHWKEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Recently moved on SQL Server 2000.
> Looks like I saw somewhere, but can't find now if can link the table from
> another database on the same SQL Server 2000.
> Also if it's possible, then question is if possible to link table from
> another remote
> SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
> example.
> Regards,
> Michael
>

Link table from another database?

Recently moved on SQL Server 2000.
Looks like I saw somewhere, but can't find now if can link the table from
another database on the same SQL Server 2000.
Also if it's possible, then question is if possible to link table from
another remote
SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
example.
Regards,
MichaelIf the tables both reside on the same instance of SQL Server there is no
need to define a linked server. Just use three parts of the four-part
naming convention:
SELECT a.Col1, b.Col2
FROM DBName.Owner.TableName a
JOIN DBName.Owner.TableName b
ON a.Col1 = b.Col1
If they reside on seperate instances, you can define a linked server (see
Books Online for full information)
and reference the tables the same way except use
LinkedServerName.DBName.Owner.TableName
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:uv6WgHWKEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Recently moved on SQL Server 2000.
> Looks like I saw somewhere, but can't find now if can link the table from
> another database on the same SQL Server 2000.
> Also if it's possible, then question is if possible to link table from
> another remote
> SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
> example.
> Regards,
> Michael
>sql

Link table from another database?

Recently moved on SQL Server 2000.
Looks like I saw somewhere, but can't find now if can link the table from
another database on the same SQL Server 2000.
Also if it's possible, then question is if possible to link table from
another remote
SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
example.
Regards,
MichaelIf the tables both reside on the same instance of SQL Server there is no
need to define a linked server. Just use three parts of the four-part
naming convention:
SELECT a.Col1, b.Col2
FROM DBName.Owner.TableName a
JOIN DBName.Owner.TableName b
ON a.Col1 = b.Col1
If they reside on seperate instances, you can define a linked server (see
Books Online for full information)
and reference the tables the same way except use
LinkedServerName.DBName.Owner.TableName
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:uv6WgHWKEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Recently moved on SQL Server 2000.
> Looks like I saw somewhere, but can't find now if can link the table from
> another database on the same SQL Server 2000.
> Also if it's possible, then question is if possible to link table from
> another remote
> SQL Server 2000, and from another version of SQL, from SQL Server 6.5 for
> example.
> Regards,
> Michael
>

Link Table

Ronia,
this could be achieved using replication (and triggers,
linked servers, DTS...). It largely depends on latency -
the delay between the change made on A and it appearing
on B, and whether the data in B is updatable. If you can
give me a bit more info on these matters, then we'll be
in a better position to advise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Data won't be update by table B. Update will only done via Table A.
so it is only a read only .
There's a replication mode which fit with this scen.
But i am not familiar with replication. and i will ask me not to use system
account with it, I don't know how to do it.
Thanks.
Ronia.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ?
news:8aae01c4d17a$1828d140$a601280a@.phx.gbl ?...
> Ronia,
> this could be achieved using replication (and triggers,
> linked servers, DTS...). It largely depends on latency -
> the delay between the change made on A and it appearing
> on B, and whether the data in B is updatable. If you can
> give me a bit more info on these matters, then we'll be
> in a better position to advise.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Transactional replication would be best for your
scenario. Replication uses agents which are essentially
jobs run by the sql server agent. For the security
requirements have a look in books on line
for 'replication, security', 'Role Requirements'.
To run replication you won't need the sysadmin account
but to set it up, configure it and monitor it, you will.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ronia,
Why dont you create a view in your database "B"?
Use B
Go
Create view T
As
Select * From A.dbo.T
Go
This will let you read the data from the database A, table T.
Sriram
"Ronia" <Ronia@.mail.com> wrote in message news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...
> Hi Paul,
> Data won't be update by table B. Update will only done via Table A.
> so it is only a read only .
> There's a replication mode which fit with this scen.
> But i am not familiar with replication. and i will ask me not to use system
> account with it, I don't know how to do it.
> Thanks.
> Ronia.
|||hi Sriram,
Will there be any preformance issue.. when doing this? However is they
are located within the same server is that performance will not be an
issue?...
compare with Transactional replication which will be better?
Thanks
Ronia
"Sriram" <srivish@.gmail.com> ?
news:25274ca3.0411241354.5e914fc2@.posting.google.c om ?...
> Hi Ronia,
> Why dont you create a view in your database "B"?
> Use B
> Go
> Create view T
> As
> Select * From A.dbo.T
> Go
> This will let you read the data from the database A, table T.
> Sriram
>
> "Ronia" <Ronia@.mail.com> wrote in message
news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
system[vbcol=seagreen]
|||It depends...
If you require a copy of the data, so eg you can do
reports on it, while not affecting - blocking - users who
are making updates on the main table, then replication
would be suitable. It can also be used to effectively
load balance database access if the databases exist on
different drives. Also, so people use snapshot
replication to have a copy of a table that users can play
around with and modify the data indiscriminantly, knowing
that all the changes will later be overwritten.
So, what is your requirement for a copy of the data? If
it doesn't fit into one of these categories, then a cross-
database view may indeed be the solution.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Link Table

I am able to create a link table MS Access to an ODBC datasource using
USQL Transoft.
Is it possible to create the same 'link table' in SQL.
MS Access takes to long to run the query, but I can only see how to import
the data into SQL.
Many thanks
You may be able to use OpenRowSet and OpenDataSource. Read all about
it from the BOL.
Aramid
On Wed, 6 Apr 2005 05:53:03 -0700, "MMouse99"
<MMouse99@.discussions.microsoft.com> wrote:

>I am able to create a link table MS Access to an ODBC datasource using
>USQL Transoft.
>Is it possible to create the same 'link table' in SQL.
>MS Access takes to long to run the query, but I can only see how to import
>the data into SQL.
>Many thanks
>
|||Hi Thanks aramid.
Whats BOL...sorry
"aramid" wrote:

> You may be able to use OpenRowSet and OpenDataSource. Read all about
> it from the BOL.
> Aramid
> On Wed, 6 Apr 2005 05:53:03 -0700, "MMouse99"
> <MMouse99@.discussions.microsoft.com> wrote:
>
>

Link Table

I am able to create a link table MS Access to an ODBC datasource using
USQL Transoft.
Is it possible to create the same 'link table' in SQL.
MS Access takes to long to run the query, but I can only see how to import
the data into SQL.
Many thanksYou may be able to use OpenRowSet and OpenDataSource. Read all about
it from the BOL.
Aramid
On Wed, 6 Apr 2005 05:53:03 -0700, "MMouse99"
<MMouse99@.discussions.microsoft.com> wrote:

>I am able to create a link table MS Access to an ODBC datasource using
>USQL Transoft.
>Is it possible to create the same 'link table' in SQL.
>MS Access takes to long to run the query, but I can only see how to import
>the data into SQL.
>Many thanks
>|||Hi Thanks aramid.
Whats BOL...sorry
"aramid" wrote:

> You may be able to use OpenRowSet and OpenDataSource. Read all about
> it from the BOL.
> Aramid
> On Wed, 6 Apr 2005 05:53:03 -0700, "MMouse99"
> <MMouse99@.discussions.microsoft.com> wrote:
>
>