MS Access can't remember links to linked tables to MS SQL Server? Every time I have to rejoin linked Tables OBC to MS SQL server. What VBA solution would help automate this link refresh? How to do it from MS Excel?
MS Access can't remember links to linked tables to MS SQL Server? Every time I have to rejoin linked Tables OBC to MS SQL server. What VBA solution would help automate this link refresh? How to do it from MS Excel?
Do you mean you have to type in your password each time, or really relink all tables with the linked table manager?
A bit different, because I use .dsn file. Each time after opening the database, the linked tables to the MS SQL server are unreachable and I have to reconnect them using manually the .dns configuration file.
Then I can work with the database until it closes. The same thing happens when I open Access later
You probably didn't check the Save Password checkbox in the linked table manager (you will get prompted for every table). I would suggest to use a DSN less connection, there are many examples on this forum of how to do that.
Please post back with any attempted code if you get stuck.
Cheers,
Giciu Thanks, you're right. But I didn't Save Password because I haven't trusted connection My confi DSN file is in this way:
[ODBC]
DRIVER=SQL Server
UID=User1
Pwd=Pass1
Trusted_Connection=No
Network=SRVDB01
DATABASE=DB01
WSID=SRVAA
APP=Microsoft Office 2021
SERVER=SRV.ZZZZZ.XX\SQLSRV01
Now I see that it is possible to remember the password despite the lack of a trusted connection.
How to do it best? I also use VBA MS Excel for SQL queries...
You can try to remove all tables and add them one last time manually making sure you check the Save Password checkbox.
Or you can add a bit of code in the startup form (or to the AutoExec macro if you use one) to relink all your tables when you open the file:
Not sure what your question is regarding Excel, whatever you do now will not be impacted by the above changes.Code:Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim Newserver As String DIm sConnect as String sConnect = "ODBC;DRIVER=SQL Server Native Client 10.1;SERVER=" & YourServer & _ ";DATABASE=" & YourDB & ";" & _ "UID=" & UID & ";" & _ "PWD=" & PWD & ";" Set dbs = CurrentDb() ' Loop through TableDefs collection, only processing ' the table if it already has a Connection property. ' (all other tables are local ... not linked.) ' In other words this will only change linked tables. For Each tdf In dbs.TableDefs If tdf.Connect <> "" And Left(tdf.Name, 1) <> "~" Then 'if you have linked tables from multiple back-ends use the Instr() function to look into the Connect string for a keyword tdf.Connect = sConnect tdf.RefreshLink End If Next
Cheers,
Gicu thank you very much! Beautifull, tidying up always helps
Can you help with ADO (or other method) in Excel? In the code below REFRESH doesn't work and I have to delete the table and add it again. How to set it for REFRESH?
Cheers
Code:If conn Is Nothing Then Set conn = New ADODB.Connection If R Is Nothing Then Set R = New ADODB.Recordset With conn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "data source=" & strPath .Open End With Set kat = New ADOX.Catalog Set kat.ActiveConnection = conn Set tbl.ParentCatalog = kat mySrv = "SRV00" DBName = "DB00" myTbl = "Tbl1" myTbl2 = Tbl0" tbl.Name = myTbl kat.Tables.Delete myTbl '!!! How to refresh without deleting? DELETE isn't good... tbl.Properties("Jet OLEDB:Link Datasource") = mySrv tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DRIVER=SQL Server;SERVER=" & mySrv & ";DATABASE=" & DBName & ";UID=" & myName & ";PWD=" & myPass tbl.Properties("Jet OLEDB:Remote Table Name") = myTbl2 tbl.Properties("Jet OLEDB:Create Link") = True kat.Tables.Append tbl kat.Tables.Refresh
I don't use ADO much, but can you please expand on the "doesn't work" part. Nothing happens, you get an error (on which line)? In the code you posted you are already deleting a table and add it back so please show the original code you tried without delete. You are missing a double quote where you set the value on the Mytbl2 line, does your code compile? Also please post the entire sub including the declarations.
Cheers,