How to change link from one external database to another

[Marla]

I've got an Access 2010 application that currently links to a SQL Server 2000 database. I need to change the link to a SQL Server 2008 database on a different server. Where do I go to change the link? When I click on External Data, ODBC Database, I only get an option to import source data. I don't want to import any data. I just want the application to link to a different external database. If I go ahead and click OK, I choose my ODBC connection and log in to the 2008 database. Now I've got a list of "import objects" to choose from. I choose one table (just for testing; I actually want to link with all tables), it says it imported successfully, but when I view the data in that table from Access, it's the same old 2000 database data.

 

[Solo712]

I've got an Access 2010 application that currently links to a SQL Server 2000 database. I need to change the link to a SQL Server 2008 database on a different server. Where do I go to change the link? When I click on External Data, ODBC Database, I only get an option to import source data. I don't want to import any data. I just want the application to link to a different external database. If I go ahead and click OK, I choose my ODBC connection and log in to the 2008 database. Now I've got a list of "import objects" to choose from. I choose one table (just for testing; I actually want to link with all tables), it says it imported successfully, but when I view the data in that table from Access, it's the same old 2000 database data.

Try External Data, Linked Table Manager !

Best,
Jiri

 

[DavidAtWork]

Marla, I'm not familiar with A2010, but when you choose to get external data, do you not get the option to Import OR Link the selected table(s)
Another method would be to use vba and define a connection string and use the Docmd.TransferDatabase method, look it up in the VBA help

David

 

[Marla]

With my existing application, I do not get the Link Tables choice when I got to External Data. I only get the import table option. When I create a new application, I do get the choice to link tables and it works perfectly. I need to be able to do it in my existing application.

 

[DavidAtWork]

Marla, here is the code I use to re-link tables to a sql BE, it does NOT require any ODBC set up on local machine. It requires 2 tables, one a list of all tables to link and the other of all to delete. You need to set the connection string variables.

Sub Relink()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String
Dim sqlStr, varLinkedTableName As String
'assign your sql server logon details to variables used in the connection string
strServer = "yourServer"
strDatabase = "yourDatabase"
strUID = "yourPassword"
strPWD = "yourUserID"
Set db = CurrentDb()
strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"Database=" & strDatabase & ";" & _
"Uid=" & strUID & ";" & _
"Pwd=" & strPWD
'first delete tables
deleteTables

sqlStr = "SELECT * FROM [tablesTOLink]"
Set rs = db.OpenRecordset(sqlStr)
Do While Not rs.EOF
varLinkedTableName = rs.Fields(0)

DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectionString, acTable, varLinkedTableName, varLinkedTableName
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Sub

Sub deleteTables()

Dim rs As DAO.Recordset
Dim sqlStr, tableForDelete As String
Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next
DoCmd.SetWarnings False

sqlStr = "SELECT * FROM tablesToDelete"
Set rs = db.OpenRecordset(sqlStr)
Do While Not rs.EOF
tableForDelete = rs.Fields(0)
'no need to check if table exists as the error handler will just skip on to the next using the resume next command
DoCmd.DeleteObject acTable, tableForDelete
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Sub

 

[Pat Hartman]

The linked tables manager is available as an option when you right-click on a table name. Choose the "always prompt for new location" option to get the dialog to choose a new BE rather than to refresh the links to the current BE.

Use the code David posted if you want to offer the user the opportunity to do this without having them actually used the built-in dialog. You just need to prompt for the new "location" and perhaps a password and user ID.