Re: Refreshing sql links to sql server need reboot to work

On Jul 29, 12:18 am, SmartbizAustralia <t...@xxxxxxxxxxxxxxx> wrote:
Weird issue.

Need to change the connection (dsn less connection of course) to sql
server from access 2007 because they blew away the security model!

But the database requires a reboot before it takes effect!

Anyone else have this issue and/or know the fix?

I assume you mean relink tables when you say 'change the connection'?
If so...I use DAO to do this and it seems to work fine for me. Here
is the code I use. There are a couple of external calls to functions
that check to see if a tabledef already exists and whether a connect
string utilizes a trusted connection or not, but the general idea of
the code should be clear. I seem to recall it being necessary to
completely delete the original tabledef for each linked table and also
to refresh the tabledefs collection in order for the process to work
properly.

Sub RelinkTable_SQL(strTbl As String, _
strConnect As String)

' strTbl - Name of table to be (re)connected
' strConnect - database connect string
Dim tdf As DAO.TableDef
Dim lngAttribute As Long

Dim db As DAO.Database
On Error GoTo RelinkTable_SQL_Error

Set db = CurrentDb

If TableDefExists(strTbl) Then
db.TableDefs.Delete strTbl
db.TableDefs.Refresh
End If

If SQL_ConnectString_TrustedConnection_get(strConnect) Then
' TRUSTED CONNECTION
Set tdf = db.CreateTableDef(strTbl)
Else
' SAVED UID AND PASSWORD
Set tdf = db.CreateTableDef(strTbl, dbAttachSavePWD)
End If

tdf.Connect = strConnect
tdf.SourceTableName = strTbl
db.TableDefs.Append tdf

db.TableDefs.Refresh

On Error GoTo 0
Exit Sub

RelinkTable_SQL_Error:

Dim uError As New clsError

Select Case Err.Number
Case Else
uError.Handle Err, "Form_frmUpdateLinkedTables_Relink",
"RelinkTable_SQL"
End Select


End Sub

HTH,
Bruce
.

Relevant Pages

  • Re: ADO connections question
    ... I have a module which creates the ADO connection object to validates the ... Function dbLogin(txtUser As String, txtPword As String, txtServer As String, ... I have a specific login form that calls the dbLogin function as shown below: ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Nochmals Treeview
    ... private void button1_Click(object sender, System.EventArgs e) { ... private void TabelleAnlegen(string strDBPfad, string strDB, string strTabellenName){ ... OleDbConnection connection = new OleDbConnection; ... OleDbCommand command = new OleDbCommand; ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)