[AccessD] Friday's Puzzler - Table Def "Connection"Info(Database Name)

Brad,

Following is how I did this so I could move my FE back and forth between my
dev. machine and a client. I have two routines one for connecting at the
client and the other for my machine. Could have made this more elegant but
as it stand now when I ship the FE to the client their admin runs the
rountine from a hidden form and all is good. I run my sql server on a
virtual machine and it askes for my login when I first hit it. I guess I
could have put the password in the connection string but this works for what
I needed.

CODE FOLLOWS
=====================================================================

Public Sub LinkOnDougsMachine()
    Dim td As TableDef
     On Error GoTo LinkOnDougsMachine_Error

    For Each td In CurrentDb.TableDefs
        If Not IsNull(td.Connect) And td.Connect <> "" Then
            td.Connect = "ODBC;DSN=RLSD-SQ1;APP=2007 Microsoft Office
system;DATABASE=ProjectDatabase_beSQL;Network=DBMSSOCN"
            td.RefreshLink
        End If
    Next
LinkOnDougsMachine_Exit:
   On Error GoTo 0
   Exit Sub

LinkOnDougsMachine_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & " at Line Number
= " & Erl & ") in procedure LinkOnDougsMachine of Module basUtilities"
    Resume LinkOnDougsMachine_Exit:
    Resume
End Sub

'---------------------------------------------------------------------------
------------
' Procedure : LinkOnRLDB
' DateTime  : 3/11/2010 11:42
' Author    : Doug
' Purpose   :This will update the table links to the production SQL server.
'---------------------------------------------------------------------------
------------
'
Public Sub LinkOnRLDB()
    Dim td As TableDef
     On Error GoTo LinkOnRLDB_Error

    For Each td In CurrentDb.TableDefs
        If Not IsNull(td.Connect) And td.Connect <> "" Then
            td.Connect = "ODBC;DRIVER=SQL
Server;SERVER=RLSD-SQ1;APP=MicrosoftR WindowsR Operating
System;DATABASE=ProjectDatabase_beSQL;Trusted_Connection=Yes"
            td.RefreshLink
        End If
    Next
LinkOnRLDB_Exit:
   On Error GoTo 0
   Exit Sub

LinkOnRLDB_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & " at Line Number
= " & Erl & ") in procedure LinkOnRLDB of Module basUtilities"
    Resume LinkOnRLDB_Exit:
    Resume
End Sub 

===========================================================

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, May 28, 2010 11:59 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Friday's Puzzler - Table Def
"Connection"Info(Database Name)

Jim,

Thanks for the advice.

Yes, the authorizations are different.

The one that uses Windows Authorization in the ODBC connection allows us to
see the database name in the tableDef.Connect.

The one that uses SQL Server Authorization in the ODBC connection does NOT
allow us to see the database name in the tableDef.Connect.

This seems strange and we would like to understand why this is happening.
The irony is that the one field that we would really like to see is the one
that is not available to us.

Thanks again,
Brad

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Friday, May 28, 2010 1:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Friday's Puzzler - Table Def "Connection"
Info(Database Name)

If you are using identical code to connect to the different SQL databases,
of course with different names, and you have access to different
information, I would have to assume that the privileges and rights are set
differently on each server; OS and SQL.

You should do your testing with ADO-OLE as it eliminates all the extra
potential padding and related errors. I think of ADO like Ping. If it
doesn't work the cable disconnected or the remote PC is down.
 
Jim

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, May 28, 2010 8:54 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Friday's Puzzler - Table Def "Connection" Info (Database
Name)

We have just started to explore the information contained in Access Table
Def  ".connect"



We would specifically like to see the Database Name because we are building
a system that will have a TEST database and a PROD database and we plan to
dynamically switch between the two.



For a test, we linked to one SQL Server table via a User-ID (Windows
Authentication)



We then linked to a second SQL Server table via a SQL Server "Login"
(SQL
Server Authentication).  We want to use this approach for the long term.



Here is the part that we don't understand.



When we look at the table def connect info for the first table, we can see
the database name.



When we look at the table def connect info for the second table (SQL Server
Authentication) we CANNOT see the database name.  (This is the piece of data
that we really want to see).



Any ideas on how we can fix this?



Thanks,

Brad
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com