Upsize Wizard ODBC Connection string



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.



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.


Bill Mosca, MS Access MVP


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 Like strPrefix & "*" Then = Mid(, Len(strPrefix) + 1)
End If

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

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

Select Case Err.Number

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

Resume exit_RenameDBOTables

End Function

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question