Brad Marks
BradM at blackforestltd.com
Sat May 29 21:06:32 CDT 2010
Drew, Thanks for the advice. Just tonight, I discovered why I wasn't seeing the database name in the table def .connect info. The TEST database is the default database and it is not shown in the .connect info. The PROD database is NOT the default database and it is shown. In the ODBC screens to test the connection I see the same thing. With our TEST DB, I simply see "default" With our PROD DB, I see the actual name of the database. I ran a bunch of tests tonight and I can tell by the data content that the code that I use to switch between TEST and PROD is working as expected. Could you post your "Class"? I would like to see how it differs from the code that I am using. Thanks Again, Brad ~~~~~~~~~~~~~~~~~~~~~ -----Original Message----- From: accessd-bounces at databaseadvisors.com on behalf of Drew Wutka Sent: Sat 5/29/2010 6:36 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Friday's Puzzler - Table Def "Connection" Info(Database Name) Ok, going to throw my two cents in here. First, and idea of why you aren't getting database info with the SQL server account. With an NT account, you have access to the entire SQL database, and you can assign/remove permissions. However, SQL accounts can be set to a specific schema/database in SQL Server, so it may not be listing DB info because it's only connecting to one database. Not sure if that makes sense. The other thing I wanted to toss out there, is that typically I create a class (used to be a plain module) for a connection object. So instead of having individual processes creating their connection, with their own code, all connection goes through the same code contained within the connection class. There are many advantages to this. First, just manually switching the data source is changing one line of code. You can also setup dual environments, like what you are talking about, and allow the class to handle which database it is using, and also allow any reference to which one you are using. Another use is keeping connections open. One large system I have connects to the database quite frequently. Instead of opening and closing the connection over and over, in separate processes throughout the application, the class opens the connection, and then maintains the connection for 15 seconds...if no other process needs the connection, the connection is closed...and then reopened when it is needed. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: Friday, May 28, 2010 10: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 The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. -- 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.