|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
refer to db table
hi
I have a SQL server 2005 query and I am trying to refer to an ms access table to use in the query. I cannot create a table or view in SQL server because its note server. What would be the best solution to refer tonthe ms access table (the max from a column) from within my SQL server query. Thanks in advance. Geebee You'll need to create a linked server:
EXEC sp_addlinkedserver @server = 'NWind', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' GO... and then access the table using 4-part naming: SELECT * FROM Nwind...Employees -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message I have a SQL server 2005 query and I am trying to refer to an ms accessnews:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... hi table to use in the query. I cannot create a table or view in SQL server because its note server. What would be the best solution to refer tonthe ms access table (the max from a column) from within my SQL server query. Thanks in advance. Geebee
Show quote
Hide quote
"Tom Moreau" wrote: Create linked server in access? I am new to this so I was wondering if I > You'll need to create a linked server: > > EXEC sp_addlinkedserver > @server = 'NWind', > @provider = 'Microsoft.Jet.OLEDB.4.0', > @srvproduct = 'OLE DB Provider for Jet', > @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' > GO... and then access the table using 4-part naming: > > SELECT * > FROM Nwind...Employees > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > news:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... could get some detailed steps. Thanks in advance Show quoteHide quote > hi > I have a SQL server 2005 query and I am trying to refer to an ms access > table to use in the query. I cannot create a table or view in SQL server > because its note server. What would be the best solution to refer tonthe ms > access table (the max from a column) from within my SQL server query. Thanks > in advance. > > Geebee > > No. You create the linked server in SQL Server and then run queries against
your Access database from there. -- Show quoteHide quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message Create linked server in access? I am new to this so I was wondering if Inews:45AAF87D-5F5C-41E3-9CE8-B1399CC53283@microsoft.com... "Tom Moreau" wrote: > You'll need to create a linked server: > > EXEC sp_addlinkedserver > @server = 'NWind', > @provider = 'Microsoft.Jet.OLEDB.4.0', > @srvproduct = 'OLE DB Provider for Jet', > @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' > GO... and then access the table using 4-part naming: > > SELECT * > FROM Nwind...Employees > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > news:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... could get some detailed steps. Thanks in advance Show quoteHide quote > hi > I have a SQL server 2005 query and I am trying to refer to an ms access > table to use in the query. I cannot create a table or view in SQL server > because its note server. What would be the best solution to refer tonthe > ms > access table (the max from a column) from within my SQL server query. > Thanks > in advance. > > Geebee > >
Show quote
Hide quote
"Tom Moreau" wrote: What do you mean by "4-part naming"?> No. You create the linked server in SQL Server and then run queries against > your Access database from there. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > news:45AAF87D-5F5C-41E3-9CE8-B1399CC53283@microsoft.com... > > > "Tom Moreau" wrote: > > > You'll need to create a linked server: > > > > EXEC sp_addlinkedserver > > @server = 'NWind', > > @provider = 'Microsoft.Jet.OLEDB.4.0', > > @srvproduct = 'OLE DB Provider for Jet', > > @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' > > GO... and then access the table using 4-part naming: > > > > SELECT * > > FROM Nwind...Employees > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canada > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > > news:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... > Create linked server in access? I am new to this so I was wondering if I > could get some detailed steps. Thanks in advance > > > > hi > > I have a SQL server 2005 query and I am trying to refer to an ms access > > table to use in the query. I cannot create a table or view in SQL server > > because its note server. What would be the best solution to refer tonthe Show quoteHide quote > > ms > > access table (the max from a column) from within my SQL server query. > > Thanks > > in advance. > > > > Geebee > > > > > > 4-part naming means you have to use 4 parts when you refer to an object,
each part delimited by a dot. Here's an example: SELECT * FROM Nwind...Employees In this case, two parts are left out but the delimiters are still present. From left to right, you have: Server Catalog (a.k.a. Database) Schema (Owner in SQL 2000 and earlier) Object -- Show quoteHide quoteTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message What do you mean by "4-part naming"?news:8424B6EE-E257-4CB4-9036-6D6F51C9B989@microsoft.com... "Tom Moreau" wrote: > No. You create the linked server in SQL Server and then run queries > against > your Access database from there. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > news:45AAF87D-5F5C-41E3-9CE8-B1399CC53283@microsoft.com... > > > "Tom Moreau" wrote: > > > You'll need to create a linked server: > > > > EXEC sp_addlinkedserver > > @server = 'NWind', > > @provider = 'Microsoft.Jet.OLEDB.4.0', > > @srvproduct = 'OLE DB Provider for Jet', > > @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb' > > GO... and then access the table using 4-part naming: > > > > SELECT * > > FROM Nwind...Employees > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canada > > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > > > > "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message > > news:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... > Create linked server in access? I am new to this so I was wondering if I > could get some detailed steps. Thanks in advance > > > > hi > > I have a SQL server 2005 query and I am trying to refer to an ms access > > table to use in the query. I cannot create a table or view in SQL server > > because its note server. What would be the best solution to refer tonthe Show quoteHide quote > > ms > > access table (the max from a column) from within my SQL server query. > > Thanks > > in advance. > > > > Geebee > > > > > > In addition to Tom's advise
SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\northwind.mdb"; User ID=Admin;Password=' )...Customers Show quoteHide quote "geebee" <geraldj***@hotmail.com(noSPAMs)> wrote in message news:817A357E-4E54-46FA-8CC6-84FE996AF96F@microsoft.com... > hi > I have a SQL server 2005 query and I am trying to refer to an ms access > table to use in the query. I cannot create a table or view in SQL server > because its note server. What would be the best solution to refer tonthe > ms > access table (the max from a column) from within my SQL server query. > Thanks > in advance. > > Geebee  
Other interesting topics
32 bit SQL Server on 64 bit OS
SQL Timeout Error Multiple databases performance setup a sql login which can only reset password kill the long run job automatically how to fix error "Connection is busy with results for another hstmt" Sql server 2000 client libraries and SQL Server 2008 collation for multiple language in a column Import SQL Data to Word/Export Data from Word to SQL? understanding metrics/performance |
|||||||||||||||||||||||
