thanks!
Did you make SQL Server available for remote connections ?
http://www.datamasker.com/SSE2005_NetworkCfg.htmHTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
link to SQL Server Express from Access 2000
i'm trying to set a data source in Access 2000 to link to SQL Server express..I have mixed mode set..what user/password do I put in? I tried sa, but get error server does not exist, or access denied..
thanks!
thanks!
Link to Exchange
I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFleming
You can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming
|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFleming
You can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming
|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
Link to Exchange
I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the A
D
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing o
ut
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the A
D
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing o
ut
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
>
>
Link to Exchange
I have a SQL 2000 server I want to link to an Exchange Server. Can I used
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
--
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> > I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> > linked servers to access the Exchange database? I do not want to have to
> > import my email addresses into a SQL table for updates.
> >
> > Thank you,
> > JLFleming
>
>
linked servers to access the Exchange database? I do not want to have to
import my email addresses into a SQL table for updates.
Thank you,
JLFlemingYou can use SQL queries via ADSI to query the AD to get the Exchange
information. This will take additional programming and there is nothing out
of the box that I know of that will facilitate this. If you don't need RT
access to it, you could build that into a nightly job.
--
Ryan Hanisco
MCSE, MCDBA
Flagship Integration Services
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> linked servers to access the Exchange database? I do not want to have to
> import my email addresses into a SQL table for updates.
> Thank you,
> JLFleming|||I'm trying to link to Exchange as well. But I'm having difficult query the AD
after I created the link server. When I run the following query in Query
Analyzer from the SQL Server, I received the following error:
Either queries:
select * from openquery (ADSI, 'Select name, adsPath FROM
''LDAP://DC=HQMAIL1, DC=com''
WHERE objectCategory =''Person'' AND objectClass= ''user''')
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.90.13/OU=AE,DC=HomeQuest,DC=Internal,DC=com'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
When I try to access the Linked Server from EM, I received this error:
Error 7301: Could not obtain a required interface from OLE DB provider
'ADSDSOObject'
Not sure what to do, any ideas?
"Ryan Hanisco" wrote:
> You can use SQL queries via ADSI to query the AD to get the Exchange
> information. This will take additional programming and there is nothing out
> of the box that I know of that will facilitate this. If you don't need RT
> access to it, you could build that into a nightly job.
> --
> Ryan Hanisco
> MCSE, MCDBA
> Flagship Integration Services
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:43FDE5A4-EF8C-4923-B6D6-BC07D25774F7@.microsoft.com...
> > I have a SQL 2000 server I want to link to an Exchange Server. Can I used
> > linked servers to access the Exchange database? I do not want to have to
> > import my email addresses into a SQL table for updates.
> >
> > Thank you,
> > JLFleming
>
>
Link to Data
I have a superbase database i like to link to sql server 2005. MS access is not working. How do i link a ODBC data connection into SQL Server.
You can either use the create a linked server for the datasource and then acces it via the four part name with the following syntax
SELECT * FROM LinkedServername.Database.Schema.ObjectName
(Those parts that don′t exists on the target server can be left out like LinkedServerName...ObjectName if applicable)
...or you can use the OPENROWSET function which can take a connection string for adhoc querying
...or you can use the OPENQUERY function to use a linked server to query for (if you want to do more than just querying some data from tables like executing a stored procedure on the remote system)
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
Hi, i tried linked server.This is the message i get.
LE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "superdata" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "superdata".
|||Hi,make sure that the right driver for the ISAM is installed:
http://support.microsoft.com/kb/209805/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Wednesday, March 28, 2012
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.
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 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?
>
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?
>
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
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
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
Is there a way to incorporate link tables in SQL Server similar, if not
the same way Access ineracts with them?Can you explain what a "link table" is?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"EESP" <johnson4@.wwu.edu> wrote in message
news:1145653487.044408.232680@.g10g2000cwb.googlegroups.com...
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
>|||EESP wrote:
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
If the table is located on the same SQL Server instance that you are
using then no kind of linking is required. You can access those tables
by using a three-part name in the form:
database_name.schema_name.object_name.
If the table is located on a different server or on another instance or
not in SQL Server at all then you can create a "linked server" (a
virtual server that corresponds to an OLEDB data source). Lookup linked
servers in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
the same way Access ineracts with them?Can you explain what a "link table" is?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"EESP" <johnson4@.wwu.edu> wrote in message
news:1145653487.044408.232680@.g10g2000cwb.googlegroups.com...
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
>|||EESP wrote:
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
If the table is located on the same SQL Server instance that you are
using then no kind of linking is required. You can access those tables
by using a three-part name in the form:
database_name.schema_name.object_name.
If the table is located on a different server or on another instance or
not in SQL Server at all then you can create a "linked server" (a
virtual server that corresponds to an OLEDB data source). Lookup linked
servers in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
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
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 tables
Is there a way to incorporate link tables in SQL Server similar, if not
the same way Access ineracts with them?Can you explain what a "link table" is?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"EESP" <johnson4@.wwu.edu> wrote in message
news:1145653487.044408.232680@.g10g2000cwb.googlegroups.com...
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
>|||EESP wrote:
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
If the table is located on the same SQL Server instance that you are
using then no kind of linking is required. You can access those tables
by using a three-part name in the form:
database_name.schema_name.object_name.
If the table is located on a different server or on another instance or
not in SQL Server at all then you can create a "linked server" (a
virtual server that corresponds to an OLEDB data source). Lookup linked
servers in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
the same way Access ineracts with them?Can you explain what a "link table" is?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"EESP" <johnson4@.wwu.edu> wrote in message
news:1145653487.044408.232680@.g10g2000cwb.googlegroups.com...
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
>|||EESP wrote:
> Is there a way to incorporate link tables in SQL Server similar, if not
> the same way Access ineracts with them?
If the table is located on the same SQL Server instance that you are
using then no kind of linking is required. You can access those tables
by using a three-part name in the form:
database_name.schema_name.object_name.
If the table is located on a different server or on another instance or
not in SQL Server at all then you can create a "linked server" (a
virtual server that corresponds to an OLEDB data source). Lookup linked
servers in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
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:
>
>
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:
>
>
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:
>
>
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:
> >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
> >
>sql
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:
> >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
> >
>sql
Link SQL Server to MS Access frontend
Hi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
Regards:
PrathmeshHi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
Regards:
Prathmesh
Most likely the issue is that there is no primary key that Access can identify. By default, access will open an ODBC record set in update mode (you can add, delete, or modify the records that you see). But if access can not identify a primary key (or if none exists), then access will open the recordset in read only mode.
As a side note, sometimes even if you have a primary key, Access will "guess" the wrong primary key. In that case all the records will appear as "#DELETED" in the recordset view.
You may want to consider an Access Data Project (ADP) which uses ADO (not ODBC) to connect to a SQL back end. Access is a wonderful tool and a fine way to generate reports, but it does have some limitations.
Regards,
hmscott|||Just a couple of things you might want to look at:
1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?
In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.
I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.
I hope any of this helps.|||Hi hmscott and pkstormy,
hmscott you were spot on. The lookup table did not have any primary key. Yesterday late night i was trying to get this running and by chance hit upon this solution and in the morning saw your reply. Tks for the "deleted" reference, it is a good thing to keep in mind.
pkstormy, tks for all the ideas. I will record them and use them for future troubleshooting. As for the database, tks a lot for that too. I will definately go through it and try to incorporate those suggestions.
Initially the frontend-backend was Access but the functionality in my database outgrew Access so we decided to move to SQL Server. The emphasis now is move all the 4-5 systems that we have in the organization to SQL Server. Once that is done, then upgrading the frontend, based on all the suggestions, is on the agenda. Thanks for the help guys. Much appreciated.
Cheers.
Prathmesh
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
Regards:
PrathmeshHi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
Regards:
Prathmesh
Most likely the issue is that there is no primary key that Access can identify. By default, access will open an ODBC record set in update mode (you can add, delete, or modify the records that you see). But if access can not identify a primary key (or if none exists), then access will open the recordset in read only mode.
As a side note, sometimes even if you have a primary key, Access will "guess" the wrong primary key. In that case all the records will appear as "#DELETED" in the recordset view.
You may want to consider an Access Data Project (ADP) which uses ADO (not ODBC) to connect to a SQL back end. Access is a wonderful tool and a fine way to generate reports, but it does have some limitations.
Regards,
hmscott|||Just a couple of things you might want to look at:
1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?
In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.
I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.
I hope any of this helps.|||Hi hmscott and pkstormy,
hmscott you were spot on. The lookup table did not have any primary key. Yesterday late night i was trying to get this running and by chance hit upon this solution and in the morning saw your reply. Tks for the "deleted" reference, it is a good thing to keep in mind.
pkstormy, tks for all the ideas. I will record them and use them for future troubleshooting. As for the database, tks a lot for that too. I will definately go through it and try to incorporate those suggestions.
Initially the frontend-backend was Access but the functionality in my database outgrew Access so we decided to move to SQL Server. The emphasis now is move all the 4-5 systems that we have in the organization to SQL Server. Once that is done, then upgrading the frontend, based on all the suggestions, is on the agenda. Thanks for the help guys. Much appreciated.
Cheers.
Prathmesh
Link SQL Server To Intersystem's Cache Database
Using ODBC, I can link to a Cache database using MS Access. I have tried to do the same with SQL Server 2000, but all I can do is copy tables, which I don't want to do. Is there a way to link SQL Server to Cache? The DB is read-only.
Cache is the DB for an Eclipsys Patient Accounting System used at the hospital where I work.
There are two ways to do it:
1. use ODBC + OLE DB Provider for ODBC you can create a linked server from SQL 2k to Cache
2. or if Cache supports its own OLE DB Provider, then you can directly use the OLE DB Provider to create a linked server to Cache
HTH.
sqlLink SQL Server To Intersystem's Cache Database
Using ODBC, I can link to a Cache database using MS Access. I have tried to do the same with SQL Server 2000, but all I can do is copy tables, which I don't want to do. Is there a way to link SQL Server to Cache? The DB is read-only.
Cache is the DB for an Eclipsys Patient Accounting System used at the hospital where I work.
There are two ways to do it:
1. use ODBC + OLE DB Provider for ODBC you can create a linked server from SQL 2k to Cache
2. or if Cache supports its own OLE DB Provider, then you can directly use the OLE DB Provider to create a linked server to Cache
HTH.
Subscribe to:
Posts (Atom)