jwcolby
jwcolby at colbyconsulting.com
Thu Feb 10 10:21:07 CST 2011
Borge, Wow, thanks! I am right in the middle of rush work but this is my immediate need for my unpaid work so I will work on it in the next day or so, probably after work tonight and the next few days. In the meantime, to start boning up on Hamachi we are going to make you your own network just for you to learn how Hamachi works. 1) Go to https://secure.logmein.com/login.asp 2) Click the "New user? Create an account" link *under* the login button. 3) Create yourself an account and log in to that account 4) Once logged in, click the "NETWORK" link in the left hand margin 5) Click the "Add Network" under the big bold My Networks in the center of the page 6) Fill in the network name and description. I suggest that you use a prefix such as BHNetworkName (BH for Borge Hansen) for your own personal networks. Not necessary but it may help you organize. 7) Fill in the network type radio button. You can come back here later to change the network type. for now select mesh. 8) Click the Continue button at the bottom of the page 9) On the next page you set up how new users can be added to the network and a password for joining the network. For now click "Accept automatically" and create a password. You can change these parameters later if you wish by revisiting this page. 10) Click continue. I believe that you will end up in a "finish dialog". I created a hub and spoke network for this discussion to get in to my machine so I am on a different path through the page now. So you should now have your first personal mesh network. A mesh network allows every computer to see every other computer, much like a router / switch does on a normal network on your system. At this instant you do not have any computer with Hamachi installed so you need to get Hamachi installed on at least two machines so that they can join this new network of yours and can talk to each other. There are two ways to do this. The first is to email an invitation to join your network from this HAMACHI web page you are on. This will send an email to an email address with a hotlink which when clicked will apply to join your network. Whether or not it is instantly allowed in to the network depends on whether you selected "immediate acceptance" as you were setting up the network. Let us use this method now so that you can see how it works and what the recipient will experience. 1) On the left hand side of the web page, click the "NETWORK" hotlink. You will be back at the page showing "MY NETWORKS" and you will now have a network, named whatever you chose as your name. 2) Click the "Add client" hot link next to the green +. Click Deploy LogMeIn Hamachi to a remote computer. Click continue. 3) The next page will be used to send an email. It allows you to send a hotlink which can be used N times within a time period to join the network. Select 1 time if it is just going to be used once, select N times if it is going to be shared by several individuals which are all in an office but you may not have all of their emails. 4) Select the network(s) that you want them to be allowed to join. 5) Click continue LogmeIn's web page apparently has a bug. For me it did *not* take me to the page I am about to describe I had to poke around and find the page. It is *supposed* to take to this next page where... 6) You will see a big bold "ADD CLIENT" in the middle of the page and three buttons - Copy, Send and test. Click the SEND button and (in my case) Thunderbird opens with a hotlink in the body for your user to click on. 7) Fill in the person that you want to join your network and the subject (join my network!) and send the email. I'll send more later. John W. Colby www.ColbyConsulting.com On 2/10/2011 2:01 AM, Borge Hansen wrote: > 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 >>