Linked Table Manage 2019

[Snowflake68]

I am using MS Acccess 2019 and I need to amend the SQL string of a linked table so that it points to another database. Can this be done without having to physically go and link the table again?

I thought previous version of Access had an 'Edit' button on the Linked table manager but this is not present in MS Access 2019

 

[Minty]

On O365 you can edit the string, that dialogue box looks like the old 2016 or earlier linked table manager?
You set the connection string in code if that helps?

 

[Snowflake68]

On O365 you can edit the string, that dialogue box looks like the old 2016 or earlier linked table manager?
You set the connection string in code if that helps?
View attachment 92599

Thats not what I get in 2019 hence my post because I dont have the Edit button in the version of Access that I have. I believe 2019 is office 365 which is what I have but it appears this dialogue box has taken a backwards step.

 

[Minty]

You would appear to be correct about the versioning, from M$;

Note Depending on the version of Access you have, the Linked Table Manager dialog box works differently. In Microsoft 365, you can use this dialog box to refresh, relink, add, edit, search, and delete linked tables, but you cannot export information to Excel. In Access 2016 and later, the dialog box is much simpler, but you can export information to Excel.

 

[Snowflake68]

You would appear to be correct about the versioning, from M$;

So the simple answer is then, I cant do it in the version that I have. I thought I had 365 but perhaps I dont then. Attached version information if you are able to advise me please.

 

[Minty]

I'm afraid that is not a 365 version, this is what mine looks like;

The other way if you need to do this regularly is to use a bit of code.
Is it just one table or all of them?

 

[Snowflake68]

I'm afraid that is not a 365 version, this is what mine looks like;
View attachment 92601

The other way if you need to do this regularly is to use a bit of code.
Is it just one table or all of them?

All of them but Ive sorted it for now as I got the customer to send me an Access database with all the linked tables to the correct database that I need and then I imported the rest of the objects into that. Not ideal but its up and running at least.

I am going to try and do a DNS less table links for SQL tables in future. I have done this to one of my other projects which works but I then have to store the username and password in the system which some customers dont like.

Thanks for confirming my version.

 

[Minty]

You can use something like this to swap them all over in code;

  Dim sNewBdName As String
  Dim sOldname   As String
 
 sOldname = "YourOriginalDatabaseName"
 sNewBdName = "YourNewDatabseName"
 
 For Each tbl In CurrentDb.TableDefs
        If Len(tbl.Connect) > 0 Then
            If Not InStr(1, tbl.Connect, "Excel") > 0 Then          'dont try and relink the excel objects
                tbl.Connect = Replace(tbl.Connect, "DATABASE=" & sOldname, "DATABASE=" & sNewBdName)
                tbl.RefreshLink
                i = i + 1
            End If
        End If
    Next
    Debug.Print "Updated " & i & " linked tables"

 

[Pat Hartman]

I had the same problem with LTM but some update fixed it.

 

[theDBguy]

Someone please correct me if I am wrong; but if I am not mistaken, you can't simply update or change the connection string without refreshing the link, which means the new BE has to be accessible when you try to do this. If so, I hope that's what you mean or are doing. Is it?