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:
>