Refresh Links

[prasadgov]

Hi All,

To refresh the link of an Access Table to a specific table/view in SQL server, how would I do that?

tdf.Connect = "ODBC;Driver={SQL SERVER};Server=xx.xxx.xx.xxx;Database= Reports;Trusted_Connection=no;UID=user;PWD=xxxxxx"
do i give the the reference to the remote sql server table name in the above line or not necessary?

The above code is not refreshing and connection fails.

.......
Err = 0
On Error Resume Next
scurrent = tdf.Name
tdf.RefreshLink
If Err<> 0 then
RefreshLinks = False
Exit Function
End If
........

But If Err<> 0 then returns 0 and it fails

TIA

 

[Gasman]

I will just have a guess as Database = ?
Though I have never had to relink with vba.

 

[prasadgov]

I will just have a guess as Database = ?
Though I have never had to relink with vba.

Database is a required parameter. My question is if I need to just go with the SQL server database = Reports or further parameters?

 

[theDBguy]

Hi All,

To refresh the link of an Access Table to a specific table/view in SQL server, how would I do that?

tdf.Connect = "ODBC;Driver={SQL SERVER};Server=xx.xxx.xx.xxx;Database= Reports;Trusted_Connection=no;UID=user;PWD=xxxxxx"
do i give the the reference to the remote sql server table name in the above line or not necessary?

The above code is not refreshing and connection fails.

.......
Err = 0
On Error Resume Next
scurrent = tdf.Name
tdf.RefreshLink
If Err<> 0 then
RefreshLinks = False
Exit Function
End If
........

But If Err<> 0 then returns 0 and it fails

TIA

Try to remove On Error Next, so you can see what the error is and fix it.

 

[prasadgov]

Try to remove On Error Next, so you can see what the error is and fix it.

I get no error but when I refresh the table, it brings up the SQL server connection gui

 

[Gasman]

See if this post gives you a clue.

Problems with Foxpro dsnless linked tables

Hello! I have a problem when i try to connect two different Foxpro table which are located in different folders. The first link goes fine but when i try to link the second table although in the ConnectionString I specify the path correctly (witch is different from the first path) i get an error...

www.access-programmers.co.uk

 

[Pat Hartman]

I don't think you can hot swap between Jet/ACE and SQL Server. In the database I have where the product supports either ACE or SQL Server, I swap from one to the other by deleting the existing links and then linking to the new source. To manage this, I keep a table with a list of table names. The table includes both the SQL Server name and the ACE name and the linked name. In my case both the ACE name and linked name are the same but in theory they don't have to be.

 

[prasadgov]

I don't think you can hot swap between Jet/ACE and SQL Server. In the database I have where the product supports either ACE or SQL Server, I swap from one to the other by deleting the existing links and then linking to the new source. To manage this, I keep a table with a list of table names. The table includes both the SQL Server name and the ACE name and the linked name. In my case both the ACE name and linked name are the same but in theory they don't have to be.

can you please share the delete and relink link?

 

[Gasman]

can you please share the delete and relink link?

Why not take the time and have a search?

Search results for query: relink

 

[Pat Hartman]

I've attached a database I use to collect statistics. It has a lot of stuff that is irrelevant to the question but rather than attempt to extract only what is relevant and potentially break something, here it is unmodified. It has two forms. One to do the relink and the other to run the capture queries. All you care about is frmODBCLink and the code modules it calls plus tblODBCDataSources. Extract what you need and import it into your own database. Fill tblODBCDataSources with the table names. The app assumes Windows Security so you don't enter a UI or password.

The relink form was extracted from the main database so it also has functionality that is irrelevant to your need. Some of it may be commented out. The code you are interested in runs from the relink buttons on the ODBC tab and the ACE tab.

The code makes no attempt to refresh links. In the real world for this application, the client never swaps BE's. He picks the BE at installation and it never changes.

 

[prasadgov]

My issue now is, I get no error when the code runs.
But when i open the linked table, it prompts me again for login credentials!

 

[Pat Hartman]

Your guess is as good as mine. If you want anyone to think about your problem, you need to post YOUR code. There's no way you could use my code without modifying it.

Are you using Windows authentication or SQL Server authentication?

 

[prasadgov]

I tried to delete the links and create links but It says cannot find the table.
The names of Access table and SQL server table are same.

 Public Function RefreshLinks() As Boolean
   On Error GoTo ErrorOut
       'Refresh table links to a backend database
       Dim dbs As DAO.Database
       Dim tdf As DAO.TableDef
       Dim sCurrentTDF As String
       Dim strLinkedtableName As String
       Dim strSQLtableName As String
       Dim strConnectionName As String
      
 
       ' Loop through all tables in the database.
       Set dbs = CurrentDb
       strLinkedtableName = "dbo_v_excess__Liquidity"
       strSQLtableName = "dbo_v_excess__Liquidity"

    strConnectionName = "ODBC;Driver={SQL SERVER};Server=10.xxx.19.xxx;Database=Liquidity;Trusted_Connection=No;UID=2052a_RPT_USER;PWD=1633;"

'Loop through the tableDefs andDelete the linked tables

For Each tdf In dbs.TableDefs
    If tdf.Name = strLinkedtableName Then
         dbs.TableDefs.Delete strLinkedtableName
         Exit For
    End If
Next tdf

'Create new Linked table
Set tdf = dbs.CreateTableDef(strLinkedtableName)
 tdf.Connect = strConnectionName
 tdf.SourceTableName = strSQLtableName
 dbs.TableDefs.Append tdf
'Clean up
Set tdf = Nothing
Set dbs = Nothing
MsgBox ("Table linked successfully")
DoCmd.SetWarnings True
'RefreshLinks = True

ExitOut:
       Exit Function
ErrorOut:
        MsgBox ("There was an error refreshing the link(s) for '" & strLinkedtableName & "':  " & vbCrLf & vbCrLf & Err.Description)
        Resume ExitOut

   End Function

Not sure why it doesn't re-link

 

[Gasman]

I have to laugh.
You hide a local IP address?
What good is that going to do for anything?

Here is the IP address for my printer... 192.168.0.129

 

[Pat Hartman]

The names of Access table and SQL server table are same.

No they are not. In SQL Server the dable name is DBO.somename. in access, it is either somename or DBO_somename. The owner name defaults to DBO and SQL Server uses a dot to separate the owner from the name but Access uses an underscore. Your owner name may actually be different.

Also note that in the code I gave you, the ODBC table is used to link the tables. Once you delete the table using the loop,where are you getting the table name to link it back?

 

[prasadgov]

No they are not. In SQL Server the dable name is DBO.somename. in access, it is either somename or DBO_somename. The owner name defaults to DBO and SQL Server uses a dot to separate the owner from the name but Access uses an underscore. Your owner name may actually be different.

Also note that in the code I gave you, the ODBC table is used to link the tables. Once you delete the table using the loop,where are you getting the table name to link it back?

You are correct!
Once I corrected the table name in SQL server, the code worked without issues.
Thanks!