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

Brad Marks BradM at blackforestltd.com
Sat May 29 14:25:05 CDT 2010


Jim,

Thanks again for the help.

We will move forward with our current plan, even though we cannot see the database name in the .connect info.

We will probably use the name "TEST" and "PROD" as part of our DSN (which is visible in the .connect info)

We will know that behind the scenes in the ODBC connection the TEST-DSN points at the TEST-Database and the PROD-DSN points at the PROD-Database.

I would guess that there are several ways to accomplish what we are trying to do (ensure that the Access Application only has READ access and enable an easy way to switch between PROD and TEST).

We are planning to deliver the application to the end-users as ACCDR files, so we are not too concerned about having the User-ID and Password in the connect string.

Thanks,
Brad
  
    

-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman
Sent: Sat 5/29/2010 12:06 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Friday's Puzzler - Table Def	"Connection"	Info(Database Name)
 
Brad,

  What your doing will work fine.  I do that all the time, although I do it
by switching the DSNs.

  You can have two DSN's just as easily and switch between in the .Connect
property.

  Only problem you have is that your going to need to specify the user name
and password in the connect string.  If you use Trusted Connection logings
for SQL, it's going to try and use the users Network login to login to SQL.

Jim. 

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

Jim, Doug, and Jim,


Thanks for the help, I appreciate it.  I am fairly new to SQL Server
security and it seems a bit overwhelming at times.

We have one SQL-Server "server" that contains our TEST Database and our PROD
Database. 

Our main goal is to ensure that the Access Application (Reporting only) can
never update any data (TEST or PROD). Therefore we are employing a SQL
Server "Role" called dbDataReader.  

Here is what we have done so far.

Set up a new SQL Server Login (at the Server level).

Set up two new SQL Server Users (at the Database level).  One for PROD and
one for TEST.  Each of these two new SQL Server Users were given the role of
db_datareader.

Set up two new ODBC connections.  With the PROD database as the default
database and the other with our TEST database as the default database.

In our Access application, we are planning to switch the "DSN" info in table
def .connect to switch between TEST and PROD, in order to point at the TEST
database or the PROD database (depending on other info that we have
available in order to make the decision on whether we want to look at TEST
or PROD data).

Because we are new to this, we wanted to be sure that everything was working
properly by looking at the database name in the .connect info.  This is when
we discovered that the database name was not in the .connect info.

I suppose this is because of the approach that we are taking.

I think that our approach is going to work, and we can live without seeing
the database name in the .connect info.  I am still curious, however, as to
why we can't see it.

Please let me know if we are heading down the wrong path.

Thanks,

Brad


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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman
Sent: Fri 5/28/2010 3:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Friday's Puzzler - Table Def "Connection"
Info(Database Name)
 

  If your using a DSN, the database name may be contained in there.

  With an ODBC connection, having everything in the connect property is not
a requirement.  It's only at the point that ODBC uses the info that it needs
it and if it doesn't have it, will prompt for it.

  Where the info comes from can be other places besides the connect property
in the tabledef.

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 2:59 PM
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

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

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




More information about the AccessD mailing list