Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked table
s
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client
?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?[/vbc
ol]
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.
[vbcol=seagreen]
> Do you still run the risk of corrupting the "back-end" database if a writ
e
> operation is interrupted if you use tables in SQL Server database as linke
d
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked tables
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?
> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.

> Do you still run the risk of corrupting the "back-end" database if a write
> operation is interrupted if you use tables in SQL Server database as linked
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

linked table 'HOST' field, Access->SQLserver

when you create a table link from Access to SQL server, the connection
string identifies the host as the computer at the time the link was
made, regardless of which computer is running Access. Can these table
links be updated at run time so that the SQL Enterprise Manager will see
the login as originating from the computer of origin instead of from the
computer that made the link?
Thanks,
MarkIf you are talking about linking SQL Server tables to an Access .mdb
front end, then the best way to do that is to write VBA/DAO code that
creates the links at runtime when the application starts up, and then
when the application shuts down, code runs that deletes all the links.
For good measure, the code that runs when the application starts up
should also delete any existing links. That is the only way to
reliably clear out any security information which may have been cached
locally in the TableDef objects in the mdb.
--Mary
On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:

>when you create a table link from Access to SQL server, the connection
>string identifies the host as the computer at the time the link was
>made, regardless of which computer is running Access. Can these table
>links be updated at run time so that the SQL Enterprise Manager will see
>the login as originating from the computer of origin instead of from the
>computer that made the link?
>Thanks,
>Mark|||Now that's clever, didn't know you could do that!. I don't program in Access
,
but like the approach you suggest. Care to offer a code fragment for
create/delete example? I never liked the form/data object model in Access,
and having forms linked to missing DAO objects seems, well, interesting!
thanks,
mark
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> If you are talking about linking SQL Server tables to an Access .mdb
> front end, then the best way to do that is to write VBA/DAO code that
> creates the links at runtime when the application starts up, and then
> when the application shuts down, code runs that deletes all the links.
> For good measure, the code that runs when the application starts up
> should also delete any existing links. That is the only way to
> reliably clear out any security information which may have been cached
> locally in the TableDef objects in the mdb.
> --Mary
> On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>|||Here ya go:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
--Mary
On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:
[vbcol=seagreen]
>Now that's clever, didn't know you could do that!. I don't program in Acces
s,
>but like the approach you suggest. Care to offer a code fragment for
>create/delete example? I never liked the form/data object model in Access,
>and having forms linked to missing DAO objects seems, well, interesting!
>thanks,
>mark
>"Mary Chipman [MSFT]" wrote:
>|||Thank you; that works rather nicely.
mark/
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> Here ya go:
> Public Sub LinkODBConnectionString()
> Dim strConnection As String
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Set db = CurrentDb
> ' Specify the driver, the server, and the connection
> strConnection = "ODBC;Driver={SQL Server};" & _
> " Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
> ' Specifying a SQLS user/password instead of integrated security
> ' strConnection = "ODBC;Driver={SQL Server};" & _
> ' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
> ' Create Linked Table. The LinkedTableName and the
> ' ServerTableName can be the same.
> Set tdf = db.CreateTableDef("LinkedTableName")
> tdf.Connect = strConnection
> tdf.SourceTableName = "ServerTableName"
> db.TableDefs.Append tdf
> Set tdf = Nothing
> End Sub
> --Mary
> On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>

servers.

Hello,

I have a problem when I try to query one SQL Server 2000 server from another SQL 2005 by linked server throught another computer with SQL Server 2005 installed. the three computers are in the same domain

After creating the link, I can access to the system databases but not to the user databases. When I try I get this message:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "EXP-THELMA" does not contain the table ""Thelma_Newsletter"."dbo"."UE_F"". The table either does not exist or the current user does not have permissions on that table.

I'm sure that the user has the right permissions to access, so I can't understand what's happening.

Any help we'll be welcome.

Thanks in advance,

Radamante.

the error is self explantory. This issue is the user does not have permission. The servers are able to communicate. check the remote user which u used to connect to Remote Server in sp_addlinkedsrvlogin has the permission to access the database

Madhu

|||

As a test try to execute exec sp_tables_ex localserver1 to test connection. If it works, you will get the list of tables available on localserver1.

Also try

select * from localserver1..dbo.dummyobjects to see what you get.

|||

Hi everybody,

Thank you very much for your help, I'm still having problems, but different one.
I'm trying to link different servers in different domains: Prod and Dev (some of them are SQL 2000 and some SQL 2005) using double-hop.
Between servers placed in Dev domain I don't have any problem, but when I try to link servers from Dev to Prod or viceversa or between servers in Prod, I receive the next message for SQL Server 2000:

OLE DB provider "SQLNCLI" for linked server "192.168.1.11" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

and this one for SQL Server 2005:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I execute the query: "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@.@.spid"

the response is: TCP - KERBEROS

This seems indicate a problem in Prod domain configuration, but I'm a little lost. I don't have a clue what's happening, could you help me, please?
Thanks a lot,

Radamante

|||

I guess there could be a trust problem between the domains if you are using a different domains between the SQL servers.

Might try to create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass". Grant testuser permission to access SS2k5 on machine A.

Also check whether the SID is similar on both the servers.

servers.

Hello,

I have a problem when I try to query one SQL Server 2000 server from another SQL 2005 by linked server throught another computer with SQL Server 2005 installed. the three computers are in the same domain

After creating the link, I can access to the system databases but not to the user databases. When I try I get this message:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "EXP-THELMA" does not contain the table ""Thelma_Newsletter"."dbo"."UE_F"". The table either does not exist or the current user does not have permissions on that table.

I'm sure that the user has the right permissions to access, so I can't understand what's happening.

Any help we'll be welcome.

Thanks in advance,

Radamante.

the error is self explantory. This issue is the user does not have permission. The servers are able to communicate. check the remote user which u used to connect to Remote Server in sp_addlinkedsrvlogin has the permission to access the database

Madhu

|||

As a test try to execute exec sp_tables_ex localserver1 to test connection. If it works, you will get the list of tables available on localserver1.

Also try

select * from localserver1..dbo.dummyobjects to see what you get.

|||

Hi everybody,

Thank you very much for your help, I'm still having problems, but different one.
I'm trying to link different servers in different domains: Prod and Dev (some of them are SQL 2000 and some SQL 2005) using double-hop.
Between servers placed in Dev domain I don't have any problem, but when I try to link servers from Dev to Prod or viceversa or between servers in Prod, I receive the next message for SQL Server 2000:

OLE DB provider "SQLNCLI" for linked server "192.168.1.11" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

and this one for SQL Server 2005:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I execute the query: "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@.@.spid"

the response is: TCP - KERBEROS

This seems indicate a problem in Prod domain configuration, but I'm a little lost. I don't have a clue what's happening, could you help me, please?
Thanks a lot,

Radamante

|||

I guess there could be a trust problem between the domains if you are using a different domains between the SQL servers.

Might try to create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass". Grant testuser permission to access SS2k5 on machine A.

Also check whether the SID is similar on both the servers.

server: Performance Issue with OpenQuery & SP best practices

We are using the linked server feature of SQL Server 2000 to execute queries
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications

> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes

> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queri
es
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causin
g
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

server: Performance Issue with OpenQuery & SP best practices

We are using the linked server feature of SQL Server 2000 to execute queries
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

server with Oracle

I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

me to getting the same problem so u got the solution please tell to me

my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711

|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance

server with Oracle

I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].me to getting the same problem so u got the solution please tell to me

my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711

|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance