Borge Hansen
pcs.accessd at gmail.com
Thu Feb 10 01:01:00 CST 2011
Hi John, I hope the stuff I am sending you may be useful to you. I spent a bit of time today putting together a demo app that demonstrates: Linked tables to any SQL Server Db or mdb with temp tables are established only as part of running a startup procedure (form sfrmSplash) When closing the app the linked tables are removed (on close event of a hidden form called sfrmSystem; this form also prevents User from accidentally closing the app by clicking on the red cross of the Access Window - User will be alerted and have to confirm they really want to close the application). Consequently if a User is able to open the mde with the shift key down there will be no linked tables on display. It is assumed that when the app is opened via the startup Form the application is locked down as per your requirements (access window removed; application menu structure in place etc.) Linking to SQL Server is via ODBC systems DSN .... I created an ODBC systems DSN by clicking File - Get Extenal Data - Link Tables. On the resulting Link dialog window I choose Files of Type: ODBC Databases(). On the resulting Select Data Structure dialog window I click the Machine Data Source tab and then click New.... Following the prompts I create a native client SQL 10 dsn - I called it jcnc10_JColby_DATA and set it up against a small SQL2008 Db I have called JColby_DATA. I use SQL Server authentication. You need to create your own ODBC machine DSN - call it jcnc10_JColby_DATA and use SQL Server authentication. In the table called *usysSQLdb *(mdb and mde) change the parameters to fit your username and password. See below for more details. Your circumstances may dictate that these parameters are hardcoded. Some observations and questions here: I am currently running a Windows7 Ultimate 64bit machine with 4Gb RAM - Office 2003 and SQL Server 2008 R2 On this laptop I can from the start menu get up the ODBC Data Source Administrator. The funny thing is* NONE *of the System DSN data sources I create from here are visible from the File - Get External Data - Link Tables route within Access.... That is very strange, as on other machines / laptop I could create the ODBC source data in the ODBC Data Source Administrator, and access from within Access when linking tables. Could someone throw light on what is missing here: The configuration is as described above. I thought that perhaps I needed to install MDAC2.8 or something like that, I even think I tried .... I'll have to re-visit that - at the time I just had to press on.... Ok, back to the demo app: The odbc connection string parameters are kept in a hidden sys table called *usysSQLdb* and required for setting up the linking of the sql tables of course. If you place the parameters hardcoded in the mde users should not be able to get to the user name and password. And even if the User should be able to look at the sql linked table in the table object window, the odbc connection string will not show the user name and password information. General question: Is there a way to keep using the usysSQLdb table and preventing the ordinary user from accessing the table?? Without using .mdw security?? The code also sets and clears the odbc connection string on any pass through queries you may have. I didn't create any pass through query - so you will have to check that out yourself. Some of the code for checking connection to the sql server and setting up the connection strings appears to be running twice.... Without having stepped through the code in fine detail, I think that is because we can have tables linked to several sql dbs - when the code is stepping through the linked sql table information in usysLinkTablesSQL and comes across a table from another SQL Db, the code will set up the connection string against this new SQL db from the usysSQLdb (or hardcoded if you choose to do so). Finally, there are procedures in place in the demo that are not necessary for the demo. Just disregard those.... I am sending you separately a zipped folder called JColby with JColby.mdb JColby.mde JColby_TEMP.mdb (mdb holding temporary tables - if required) JColby_DATA.BAK (SQL backup of Db) Regards, borge The connection parameters If the .mde is opening with the shift key down - there will be n On Tue, Feb 8, 2011 at 11:00 PM, jwcolby <jwcolby at colbyconsulting.com>wrote: > Borge, > > I need your help. I will open my system to you, I will teach you about > Hamachi and you can teach me about authentication in a run time world. I > cannot give you the keys in AccessD though so contact me off line. Anyone > else too who knows any part of this and wants to learn any part. We will > keep all discussions other than actual user name / passwords on this list. > > Thanks, > > > John W. Colby > www.ColbyConsulting.com <http://www.colbyconsulting.com/> > > On 2/7/2011 8:35 PM, Borge Hansen wrote: > >> John, >> >> We use a system / machine ODBC dsn with SQL Server Authentication Login >> ID and password >> >> We drop and relink tables on opening of the runtime using hardcoded Login >> ID >> and password >> >> Even if user can access properties of linked table, the Login ID and >> password will not be shown, see sample below: >> >> ODBC;DSN=bhnc10_RMS_DATA;Description=RMS_DATA;APP=Microsoft Office >> >> 2003;WSID=GODWIT;DATABASE=RMS_DATA;QueryLog_On=Yes;StatsLog_On=Yes;;TABLE=dbo.tblBookingRequest >> I am curious about the hamachi, please provide link for more information >> and >> instrucitons for how to set up >> >> regards, >> borge >> >> >> >> On Tue, Feb 8, 2011 at 3:05 AM, jwcolby<jwcolby at colbyconsulting.com> >> wrote: >> >> I sent a demo SQL dB to you off-list. Did you get it? >>>> >>> >>> AFAICT I did not get that offline. >>> >>> I am trying to build an access application which installs on a user's >>> system somewhere out in the world. >>> >>> 1) They are not local to my machine, they come in to my SQL Server >>> instance >>> over Hamachi. >>> 1a) They have no means to login directly to the server instance with a >>> username / password. >>> 2) They are going to run on a 2007 run-time. >>> 3) The runtime is intentionally unhelpful. >>> 4) In testing it just gives me a "there was an error executing the >>> command" >>> when I try to open a form. When I try to open the same thing under the >>> full >>> version Access it is asking for a logon as it attempts to open the first >>> form. >>> >>> My guess is that the runtime is attempting to open this SQl Server login >>> form and isn't allowed to by the runtime. Thus I have to set up the >>> linked >>> tables to just use a correct username / password. I think I can embed >>> this >>> directly into the sql connect string that is part of the tabledef.connect >>> but was attempting to avoid that. That will be my next test just to >>> ensure >>> that it is in fact the issue here. >>> >>> >>> John W. Colby >>> www.ColbyConsulting.com <http://www.colbyconsulting.com/>< >>> http://www.colbyconsulting.com/> >>> >>> >>> On 1/31/2011 7:31 PM, Darren - Active Billing wrote: >>> >>> Hi JC >>>> >>>> I sent a demo SQL dB to you off-list. Did you get it? >>>> It shows how to use Pass through queries >>>> It has a sample to store your username and password and pass it in your >>>> SQL >>>> connection requests >>>> There is also the option to set the "Trusted Connection" to yes in the >>>> SQL >>>> connections strings >>>> >>>> Many thanks >>>> >>>> Darren >>>> >>>> -- >>> 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 >