What is a 'connection' or Spid?

[jsteph]

Hi all,
I know in the abstract what a connection is, but my question is more to the nuts and bolts of what actually is a 'connection' to a sql-server database--specifically and ODBC or OLEDB connection from an Access or Visual Basic application.

In other words, what constitues this connection on the client side--is it a chunk of memory that knows the server ip and has cached credentials, is it a file, what? This is sort of a follow-up to a previous issue from thread thread962-1548216.

In a nutshell, I had an Access app that had linked-tables to a sql server. When I copy the Access .mdb to a physically different machine on our lan, then I delete the linked tables in Access while it's on the new machine and then re-link them (each machine has a same-named ODBC DSN)--the spid in sql server from my original machine is the same one that the new machine is now using.

So my question is how and why? I physically deleted the the table-links, I re-linked from a different machine with a different IP address, yet somehow Sql-server 'knew' it was the same .mdb file and re-used the spid. The manifestation of this behaviour is that I use the hostname and NT-Username from the spid table to identify which computer made changes to certain data. So what's happening is that I now see that this audit information is tainted--it's not accurate.

So I'm really looking for a way--and I don't know if this is a sql-server issue or a VB/Access issue--a way to ensure that when I link a table or create an OLDEB connection for ADO (after deleting existing links and .Closing adodb connections)--that sql server is going to create a brand new spid with correct hostname and username.

I know there are reasons to hold a spid open if it's from the same machine--in the AS/400 world we called it "Lazy Close" becuase it's expensive to open a connection each time--but this is a physically different machine and should be a new spid.
Thanks for any enlightenment on this issue.
--Jim

 

[yelworcm]

A connection, from the client perspective, is a ADODB.Connection object that resides in memory. This object has several methods that will send messages to the database server based on inputs your program provides 9e.g. the connection string). I am not sure why you would need to delete all the linked tables, and re-add them. That is something that should be asked in an Access forum, I would think.

As for the hostname getting carried over, this is something that MS Access can do. I am not sure, but I think it has to do with the somewhat antiquated provider it uses to connect to SQL Server. If you change the ODBC driver to use the SQL Native Client, you will likely see the correct hostname.