Access FE - SQL Server BE - relink tables

[JohnPapa]

I have an Access FE and an SQL Server BE and I use linked tables.

I would like to relink the existing tables and also be able to link to a copy of the SQL Server BE, situated elsewhere.

 

[JohnPapa]

I can get the Connection string for a table by using

strDum = CurrentDb.TableDefs(TableName).Connect

and I guess would need to store this, for future use

 

[theDBguy]

You might want to look into DSN-less connections.

 

[sonic8]

I would like to relink the existing tables and also be able to link to a copy of the SQL Server BE, situated elsewhere.

Sorry for being stand-off-ish, but please do some research on this topic first. It has been discussed and solved with publicly available solutions a million times.

If you got specific problems applying a solution you are very welcome for help here.

 

[JohnPapa]

I came up with something like the following

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
 
'Get Connection string
Dim strConString As String
strConString = GetLinkedDBName("dbo_tblBuilding")


'Store Connection string
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "dbo_tblSerial", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
rst.MoveFirst
rst!strConnectionString = strConString
rst.Update


' Loop through all tables
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
  
    ' Check if it is a linked table
    If Len(tdf.Connect) > 0 Then
        
        'For linked table check if Connection string is different
        If tdf.Connect <> strConString Then
        
            'Assign proper Connection string
            tdf.Connect = strConString
            On Error Resume Next
            tdf.RefreshLink
        End If
    End If
 
Next tdf

If I msgbox the connection string, the DB Password appears. However, if I hover over the linked table name the DB Password does not appear.

 

[JohnPapa]

You might want to look into DSN-less connections.

Can you have a DSN-less connection and still use linked tables? Are you referring to OLEDB?

 

[Minty]

Short answer yes. That's the point!

ODBC or OLEDB - are just connection methods.

Read here

Creating and managing DSN-less connections

What is Data Source Name (DSN)? It is either a simple text file or values in registry that contains information about how to connect to a ODBC backend. By creating a DSN, you do not have to supply the information and the coding is simpler as you only need to refer to DSN to fill in the...

www.access-programmers.co.uk

 

[JohnPapa]

Short answer yes. That's the point!

ODBC or OLEDB - are just connection methods.

Read here

Creating and managing DSN-less connections

What is Data Source Name (DSN)? It is either a simple text file or values in registry that contains information about how to connect to a ODBC backend. By creating a DSN, you do not have to supply the information and the coding is simpler as you only need to refer to DSN to fill in the...

www.access-programmers.co.uk

Thanks. FYI, in the article you refer the following do not work

 

[JohnPapa]

Also can you please clarify what happens to the linked file link. Is it deleted? Does it remain? Is it used in any way?

 

[Minty]

It is a 15-year-old thread to be fair.

The best connection strings site is here:

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

And Doug Steele's stuff has also moved to here:

Using DSN-Less Connections

 

[sonic8]

If I msgbox the connection string, the DB Password appears. However, if I hover over the linked table name the DB Password does not appear.

This is deliberate.
If you display a string variable in a MsgBox, it will of course show the password if it is included in the string.
If you hover over a linked table, the tooltip deliberately doesn't include the password to not show it to people not supposed to see it. Recently there was a change in Access (I don't know if intentional or accidental) that would include the password in the tooltip. There were complaints about this change and Microsoft rolled back the change to exclude the password from the tooltip again.

While I do not agree with this security by obscurity approach, I understand the reasoning.

 

[JohnPapa]

It is a 15-year-old thread to be fair.

The best connection strings site is here:

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

And Doug Steele's stuff has also moved to here:

Using DSN-Less Connections

Am I correct in assuming that to use the DSNless links (DB password not showing in link) I need a Trusted connection?

 

[JohnPapa]

It is a 15-year-old thread to be fair.

The best connection strings site is here:

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

And Doug Steele's stuff has also moved to here:

Using DSN-Less Connections

I ran the DSNless code.

In my case Trusted_connection=No

and commented out (as stated in the comments) the following line

' Unfortunately, I'm current unable to test this code,
' but I've been told trying this line of code is failing for most people...
' If it doesn't work for you, just leave it out.


    tdfCurrent.Attributes = typNewTables(intLoop).Attributes   'I commented out this line

It ran without an error, but I do not see anything different from before.

 

[sonic8]

Am I correct in assuming that to use the DSNless links (DB password not showing in link) I need a Trusted connection?

No. You can also execute a temporary Pass-Through-Query with the password in the connection string and immediately afterwards link the tables to the very same connection without the password in the connections string.
You would then also need to run that Pass-Through-Query on startup of your application to establish the connection.