Upsize Wizard ODBC Connection string

[gmead7]

When you use the Upsize wizard and elect to "Link SQL server tables to
existing application", the Upsize Wizard creates an ODBC connection to a
linked table in your front end. Where is this connection string information
stored? Say you move your SQL tables to a different server - where can you
go to update this information?

I DO NOT want to use the Linked Table Manager - when you use the Linked
Table Manager, you can create a link to the tables, but Access names these
new linked tables by adding the Object Owner to the name of your table like
this dbo_NameOfTable. (Or if you use the linked table manager is there a way
to prevent this behavior... this makes all your standing queries inoperable
as these queries are looking for "NameOfTable" not "dbo_NameOfTable).

thanks, George

 

[Bill Mosca, MS Access MVP]

Connection string info is stored in MSysObjects table, a hidden system
table. To view it, click on Tools>Options>View tab and select system tables.

You can easily rename the linked tables so the owner part is removed. The
name is only what Access calls it. It does not change in the SQL database
and will have no affect on the link.Once the owner name is removed,
relinking will not change it back.

 

[gmead7]

Bill,

Thank you - found the connection string data. Also, appreciated the tip on
renaming the tables - I had thought that Access would rename them all back to
include the owner, but it was good to hear that this is not the case.

George

 

[Bill Mosca, MS Access MVP]

George

Glad to be of help.

I use SQL Server tables in most of the projects I'm working on so I wrote a
procedure to do the renaming for me. All it does is drop the "dbo" in front
of the table names.

Public Function RenameDBOTables()
'Purpose : Remove SQL Server "dbo_" from all table names.
'DateTime : 10/28/2003 15:17
'Author : Bill Mosca
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strPrefix As String

On Error GoTo err_RenameDBOTables

strPrefix = "dbo_"
Set cnn = CurrentProject.Connection
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn
For Each tbl In cat.Tables
If tbl.name Like strPrefix & "*" Then
tbl.name = Mid(tbl.name, Len(strPrefix) + 1)
End If
Next

Application.RefreshDatabaseWindow
MsgBox "Tables successfully renamed.", _
vbInformation, "dbo_ Dropped"

exit_RenameDBOTables:
On Error Resume Next
Set cat = Nothing
Set cnn = Nothing
Exit Function

err_RenameDBOTables:
Select Case Err.Number

Case Else
MsgBox "An error (" & Err.Description & ") occurred in
RenameDBOTables.", vbCritical, "Error!"
End Select

Resume exit_RenameDBOTables


End Function