Re: Linking to an SQL Database

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 00:30:55 -0500

I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
  MsgBox "You must supply a DSN in order to link tables."
  Exit Sub
Else
  strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
  Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
  tdfAccess.Connect = dbsODBC.Connect
  tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
  dbs.TableDefs.Append tdfAccess
  rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
    SysCmd acSysCmdClearStatus
    Exit Sub

Err_LinkSQLServerTables:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_LinkSQLServerTables

End Sub

'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

    Dim dbs As Database, tdf As TableDef, I As Integer
    Set dbs = CurrentDb
    For I = dbs.TableDefs.Count - 1 To 0 Step -1
      Set tdf = dbs.TableDefs(I)
      If (tdf.Attributes And dbAttachedODBC) Then
        dbs.TableDefs.Delete (tdf.Name)
      End If
    Next I

    dbs.Close
    Set dbs = Nothing

Exit_DeleteODBCTableNames:
    Exit Sub

Err_DeleteODBCTableNames:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_DeleteODBCTableNames

End Sub 

-- 
Joe Fallon
Access MVP
"Wayne" <wbouwman@csbsystems.com> wrote in message
news:deda01c3eff6$e6038710$a401280a@phx.gbl...
> When linking to a SQL database through MS Access (97,
> 2000, 2002), all the SQL database tables names are
> prefixed with a "dbo_". When i look at the same database
> through Enterprise manager there is no prefix on any of
> the table names. When I link at the datbase through MS
> Query Excel), no prefix is displayed. Why is Access
> showing me this prefix throught the Linked table dialog?
>
> Problem is I have existing applications where the
> database it connects to has been upgrade to an SQL
> database, but was linked using a different driver, but
> ALL tables names are exactly the same in the SQL database
> and the prior database. I was hoping to just re-link the
> tables in the application to the new SQL DSN and go.
>
> Is there a way to modify how Access sees the SQL Database
> tables to not prefix the table names with the dbo_?

Relevant Pages
Loading