Linking to an SQL Database

[Wayne]

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_?

 

[prabha]

Hi Wayne,

Let me research that one. I think it's going to come down to changing a
registry entry.

If you have additional questions on this topic, please respond back to this
posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Content-Class: urn:content-classes:message
| From: "Wayne" <[email protected]>
| Sender: "Wayne" <[email protected]>
| Subject: Linking to an SQL Database
| Date: Tue, 10 Feb 2004 08:56:59 -0800
| Lines: 17
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcPv9uYDj+f3LjRvTkO2giUvBNZeDA==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.externaldata
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.externaldata:48800
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.externaldata
|
| 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_?
|

 

[wayne]

THanks for your response Eric.
I tried looking through the registry entries and through
some of the ini files (ODBC.ini and WIN.ini) based on
some info i found on the web, but couldn't make heads or
tales out of it. Any assistance on this would be greatly
appreciated!

I'll keep any eye out for your reponse.
Regards,

 

[prabha]

Hi Wayne,

This is how JET handles ODBC linked tables (adds an identifier). As a
workaround you can implement the following code after linking your ODBC
tables

Function ChangeLinkTableNames()

Dim tdf As DAO.TableDef

For Each tdf In CurrentDb.TableDefs
' check to see if table is a linked table
IF Len(tdf.Connect) > 1 THEN
' check to see if linked table has already removed dbo_
identifier
IF Left$(tdf.Name,4) = "dbo_" THEN
' remove dbo_ from linked table name
tdf.Name = Mid$(tdf.Name, 5)
END IF
END IF
Next tdf
MsgBox "done changing links"
End Function



--------------------
| Content-Class: urn:content-classes:message
| From: "wayne" <[email protected]>
| Sender: "wayne" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: Linking to an SQL Database
| Date: Wed, 11 Feb 2004 11:43:30 -0800
| Lines: 94
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcPw11N/IrUP3UCnRYeDy1KzKnb00g==
| Newsgroups: microsoft.public.access.externaldata
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.externaldata:48852
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.access.externaldata
|
| THanks for your response Eric.
| I tried looking through the registry entries and through
| some of the ini files (ODBC.ini and WIN.ini) based on
| some info i found on the web, but couldn't make heads or
| tales out of it. Any assistance on this would be greatly
| appreciated!
|
| I'll keep any eye out for your reponse.
| Regards,
|
| >-----Original Message-----
| >Hi Wayne,
| >
| >Let me research that one. I think it's going to come
| down to changing a
| >registry entry.
| >
| >If you have additional questions on this topic, please
| respond back to this
| >posting.
| >
| >
| >Regards,
| >
| >Eric Butts
| >Microsoft Access Support
| >
| >"Microsoft Security Announcement: Have you installed the
| patch for
| >Microsoft Security Bulletin MS03-026? If not Microsoft
| strongly advises
| >you to review the information at the following link
| regarding Microsoft
| >Security Bulletin MS03-026
| ><http://www.microsoft.com/security/security_bulletins/ms0
| 3-026.asp> and/or
| >to visit Windows Update at
| <http://windowsupdate.microsoft.com/> to install
| >the patch. Running the SCAN program from the Windows
| Update site will help
| >to insure you are current with all security patches, not
| just MS03-026."
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Wayne" <[email protected]>
| >| Sender: "Wayne" <[email protected]>
| >| Subject: Linking to an SQL Database
| >| Date: Tue, 10 Feb 2004 08:56:59 -0800
| >| Lines: 17
| >| Message-ID: <[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcPv9uYDj+f3LjRvTkO2giUvBNZeDA==
| >| X-MimeOLE: Produced By Microsoft MimeOLE
| V5.50.4910.0300
| >| Newsgroups: microsoft.public.access.externaldata
| >| Path: cpmsftngxa07.phx.gbl
| >| Xref: cpmsftngxa07.phx.gbl
| microsoft.public.access.externaldata:48800
| >| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.access.externaldata
| >|
| >| 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_?
| >|
| >
| >.
| >
|

 

[Joe Fallon]

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

 

[Wayne]

THanks for your response! DO you know if there is there a
registry entry to change the JET engine ODBC Table
identifier?

I'm not an "real" Access programmer. How would i execute
this Function? Through a form command? I should then only
have to do this once when i itially link the table to the
Access app, correct?

THanks again for any assistance!