RE: Linking to an SQL Database

From: Wayne (wayne_at_discussions.microsoft.com)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 08:51:47 -0800

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!

>-----Original Message-----
>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" <wbouwman.deletethis@csbsystems.com>
>| Sender: "wayne" <wbouwman.deletethis@csbsystems.com>
>| References: <deda01c3eff6$e6038710$a401280a@phx.gbl>
><f2ddxoE8DHA.2992@cpmsftngxa07.phx.gbl>
>| Subject: RE: Linking to an SQL Database
>| Date: Wed, 11 Feb 2004 11:43:30 -0800
>| Lines: 94
>| Message-ID: <eea301c3f0d7$537f9190$a601280a@phx.gbl>
>| 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" <wbouwman@csbsystems.com>
>| >| Sender: "Wayne" <wbouwman@csbsystems.com>
>| >| Subject: Linking to an SQL Database
>| >| Date: Tue, 10 Feb 2004 08:56:59 -0800
>| >| Lines: 17
>| >| Message-ID: <deda01c3eff6$e6038710$a401280a@phx.gbl>
>| >| 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_?
>| >|
>| >
>| >.
>| >
>|
>
>.
>