Linked table manager error

[SQL_Hell]

Hello,

I am trying to migrate an access front end over to a new SQL server, I don't know much about this application as it wasn't developed by me.

The front end is Access 2010

When I go into the linked table manager to refresh the tables, a lot of tables are showing as ><\unknown>, when I try to update these tables to link to the new SQL server I get the error:

"Unable to continue, some links have been selected with an unknown type"

Any ideas?

 

[SQL_Hell]

Ok so..some update.

I have since found out that there are no DSNs for this access front end, it has DSN-less linked tables.

With a function to refresh the tables upon main form open event.

It's been a long time since I have read any VBA but I am pretty sure this code doesn't work:

(I have deliberately deleted most of the connection for posting here)

Public Const connString As String = "ODBC;DRIVER=SQL Server;SERVER="


Public Sub RefreshODBCLinks()
    Dim connString As String
    
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb
    Set db = Nothing

This code doesn't seem to work because newConnectionString is not declared anywhere nor does it have a value set.

Can anyone give me advice on reconnecting linked tables to a new SQL server in code?

 

[kevlray]

I found the following code. It will create a linked table to an SQL database. I do not know if it will work in your situation.


Option Compare Database

Sub getdata()
Dim strConnectionString As String
Dim strNameInAccess As String
Dim strNameInSQLServer As String

' set the connection string
strConnectionString = "ODBC;DRIVER=SQL Server; " & "SERVER=server;DATABASE=DBName;Trusted_Connection=Yes"

' specify the tables you want to link. The table can be

' known by a different name in Access than the name in SQL server

strNameInAccess = "tableName"

strNameInSQLServer = "tableName"

On Error Resume Next
DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectionString, acTable, strNameInSQLServer, strNameInAccess
End Sub

 

[SQL_Hell]

Thanks, but that looks to be transferring local MS Access tables to SQL server.

In my scenario I just need to update the connection strings on tables that are already linked to SQL server.

 

[Rx_]

My preference is to build a local table with the SQL Table names that should be linked. First, destroy all linked table defs. Then Read table names one at a time and create the new def.
the table object refresh link is something I run after creating the def.
This gives me the option with one command to re-link to a test database or back to the production.

Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
    '    old server is DenREG-Test
    Dim ConnectString As String
    Dim strConnectionString As String
    Dim scn As String
    Dim sLocalName As String
    Dim UID As String
    Dim pwd As String
    Dim ServerString As String
    Dim DatabaseName As String
 
    ' "SERVER=ServerName\DBInstance;DATABASE=" & DataBaseName & ";" & _
    ' 
        ServerString = "Server\Instance"
    UID = "UserName"     
    pwd = "mypassword"
    sLocalName = TableName  ' "Const_Seed" ' <<<---manually add a table name here for one single table
 
    If fTest = False Then ' flag to choose DB or TestDB
        DatabaseName = "RegulatoryDB"
    Else
        DatabaseName = "RegulatoryDBT"    ' <<<<<<<<<<------------  DBT or DB  ////////Uncomment for Test DB Server name next
    End If
 
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=regsql\regulator;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 11.0;" & _
        "SERVER=regsql\regulator;DATABASE=" & DatabaseName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & pwd & ";" & _
        "Table=DBO." & sLocalName & ";Option=3;"
 
 
    ModifiedRefreshDNSLess2 = strConnectionString
    'Debug.Print strConnectionString
End Function

Regarding refreshing existing linked table def, we were just discussing it at this link:
http://www.access-programmers.co.uk/forums/showthread.php?t=268625

does that help?

 

[kevlray]

Actually the code I posted apparently does not transfer the table (since the table never existed in the database where I used the code). But it did a nice job of creating a linked table to an existing table that was already on our SQL server.

 

[SQL_Hell]

Thanks for the help both, I have been able to sort this out now using both of your posts and the following link:

http://support.microsoft.com/kb/892490

Interestingly the linked table manager still shows everything as "unkown" but I have come to the conclusion that this is just a product of DSN less linked tables and it's not really an issue.

Thanks!

 

[Rx_]

Sorry it took so long to respond.
What about your Tool Tips when hovering over a Linked Table?
I can't take a screen shot as my SnagIT closes the Tool Tips when on the capture event.
Does your Tool tips show the DSN and information down to the Table name?
Attached is a copy of my Linked Table Manager for a SQL Server DB.
The RegulatoryDB also has a script for a REgulatoryDBT (test) for example.

A SQL backup/restore creates the DBT.
Then a vba script using a local table with the names of the linked tables can destroy all linked tables, then re-link to either Production or Test.

My ODBC is SQL Server Native Client 11.0

 

[SQL_Hell]

Yeah still not sure why it still says "unkown" in the linked table manager, even if I mouse over the tool tip says the same.

However under the list of tables in access, the tool tips all show the connection string correctly and it definitely now works with the new SQL database server (once I had sorted out the firewall rules)

Since writing this thread I have now updated another 4 Access databases with SQL server backends using the same method (VBA to create DSN less connections) and interestingly none of the other ones have the "unkown" issue in the linked table manager.

RX_ I was thinking about your solution where it converts local access tables to DSN less linked tables and wondered if you could achieve the same thing without the need for local access tables, by looping through an ADO recordset with the data output from "SELECT Name FROM sys.tables". You wouldn't need to have the local tables and if anyone creates a new sql server table it gets auto linked to Ms Access.

 

[Rx_]

Probably could. Is that vague enough?
My local table has extra fields.
For example a couple of columns with checkbox.
This way, custom releases can be made as to what is included for that group.
A Description column with verbose life story of the field for those of us who don't really want to remember all that detail.

On the SQL Side, there are many views that are used by other views that will never be linked directly. The table even has a check-box as to if this should be linked or not (e.g. during development vs production).

Call me old-fashioned and I will resemble that remark. It is an Old Russian Trick shown to me by an Old Russian. When I do get other Access programmers to assist me, it has tended to be straight forward so I don't have to let them muck with my SQL Server.

That may not be the right answer and only be a justification for being set in my ways.