Jim Lawrence
accessd at shaw.ca
Fri May 28 15:06:04 CDT 2010
Hi Brad: Just a thought, check on the privileges/security section on the faulting SQL server. You may have missed given full permissions to yourself or to a table or two... I have done similar things when going fast and not checking off everything as I go. You know the technique to getting all the requirements into workable text format, create a text file on your desktop, change the extension to 'udl', setup the new ODBC file and when you get it working perfectly, change the extension back to 'txt' and edit the file in notepad. Cut and paste the coded and tested connection string... If you know it ignore it but if not; it is one of the most useful tricks ever. 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 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