Linking to an SQL Database

  • Thread starter Thread starter Wayne
  • Start date Start date
W

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_?
 
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_?
|
 
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,
 
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_?
| >|
| >
| >.
| >
|
 
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
 
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!
 
Back
Top