T
Tom P.
I've been struggling for some time now getting the relinking of ODBC tables
working as I'd like. I've tried a few methods and while the connectivity
seems to work I can't get some of the other "nice to haves" functioning
correctly. Specifically, when linking to SQL Server tables, I want the
sysprocesses table in SQL server to help me identify the current user of the
process which is generated by Access' use of a table.
The issue is that the HostName field in sysprocesses contains the machine
name of the Access developer who first linked the table. This persists even
after that developer delivers the application to the end user. Even with
programmatic relinking, the original value for the HostName field persists.
What this means is that if I have an Access process locking my SQL Server
table (trust me, it happens quite easily, even when the grants are "SELECT"
only...) I cannot identify the owner of the process. All the processes look
like they are owned by the original developer.
I've tried these two methods:
1. Change the connect property of the linked table, passing a new host name
value as part of the connect string. While the code doesn't error out it
also doesn't change the value.
2. Drop the tables and relink them with a new connect string. The issue
here is that sometimes the developer chooses to use relationships in Access
and I need to remove them before dropping the table. Then I need to recreate
them. I was hoping to avoid that.
So, any thoughts? Anyone else ever tried to accomplish this?
Thanks
working as I'd like. I've tried a few methods and while the connectivity
seems to work I can't get some of the other "nice to haves" functioning
correctly. Specifically, when linking to SQL Server tables, I want the
sysprocesses table in SQL server to help me identify the current user of the
process which is generated by Access' use of a table.
The issue is that the HostName field in sysprocesses contains the machine
name of the Access developer who first linked the table. This persists even
after that developer delivers the application to the end user. Even with
programmatic relinking, the original value for the HostName field persists.
What this means is that if I have an Access process locking my SQL Server
table (trust me, it happens quite easily, even when the grants are "SELECT"
only...) I cannot identify the owner of the process. All the processes look
like they are owned by the original developer.
I've tried these two methods:
1. Change the connect property of the linked table, passing a new host name
value as part of the connect string. While the code doesn't error out it
also doesn't change the value.
2. Drop the tables and relink them with a new connect string. The issue
here is that sometimes the developer chooses to use relationships in Access
and I need to remove them before dropping the table. Then I need to recreate
them. I was hoping to avoid that.
So, any thoughts? Anyone else ever tried to accomplish this?
Thanks