SQL Server Performance Forum – Threads Archive

DSNLess connection to SQL Server

One of my developers have sent the following to the team. … Starting today, the Inv application is using DSNless connections to the database. This means that you must now specify an override server if you want it to connect to something other than the default server which is hardcoded as a constant. (Currently the TestServ) This is done using a command line argument when Access is opened in the format /cmd server=<server address>. Once the application’s name stops changing, this will be most easily accomplished by creating a shortcut that automatically opens Access with Inv. The target for the shortcut would look something like this: "C

rogram FilesMicrosoft OfficeOfficeMSACCESS.EXE" "C

rogram FilesAToolsInv.mdb" /cmd server=abcd12340 In the previous example, the server was overriden to my local machines name. For another external server, such as the TestServ, you would use it’s IP address like this: "C

rogram FilesMicrosoft OfficeOfficeMSACCESS.EXE" "C

rogram FilesAToolsInv.mdb" /cmd server=100.200.300.400
… I know this way we are not going to use ODBC connection. Do you think her suggestion is good and modifies the process? Is it the best? Any idea? As a DBA, what can I do to improve the process? The way that the Access application connects and uses tables and SQL resources? Thanks, Farhad R
[email protected]

Store the variable parameters in a local table in your Access file (so assuming it’s not an MDB or an MDE). From the start-up form you then allow changes to these parameters, and if any change is made you change the connect strings for the table links. Make sense?

quote:Originally posted by Adriaan Store the variable parameters in a local table in your Access file (so assuming it’s not an MDB or an MDE). From the start-up form you then allow changes to these parameters, and if any change is made you change the connect strings for the table links. Make sense?

Thank you for your prompt reply. You mean she create a table to keep the server’s name. This table would have only one line, Am I correct? Then what is the benefit? Because based on her suggestion, they are doing the process by arguments and pasing parameters. "C

rogram FilesMicrosoft OfficeOfficeMSACCESS.EXE" "C

rogram FilesAToolsInv.mdb" /cmd server=abcd12340 Sorry, I couldn’t get it.[V] Would you explain more? Thanks,
Farhad R
[email protected]

Our developers have created an application. It is an MS-Access connected to SQL Server. Their previous version was using ODBC DNS. But the new one as they say is DNSless. This means that they have hard coded the production server’s IP in the application!! I just recieved a cc email indicating to our test and development guys that be carful when you want to use the application and use it as below, to point the test server, not production! "C

rogram FilesMicrosoft OfficeOfficeMSACCESS.EXE" "C

rogram FilesAToolsInv.mdb" /cmd server=100.200.300.400 I suggested to use a table to keep the variable parameters. But need more information to give with my suggestion. Do you have any recommendation? How should be the data connection?
CanadaDBA

Under no circumstances should you allow hardcoded IP’s in the application. Not even a hardcoded DNS name. Put the connectionstring in a config or ini file or in the registry and then code the application to read it’s connection properties from there.

Farhad, Changing switches on the commandline means you’re putting the control outside of the application. Also, editing the commandline on a shortcut is often a problem because you have move the cursor around a lot in box that’s just too narrow for comfort. Going to the registry may be considered ideal by some, but this probably requires an advanced sort of installation/maintenance routine to deal with user permissions on the workstation and so on – fine if you can use it, but personally I wouldn’t bother. If you use a .ini file then you have to rely that the additional file is in place. You also need to retrieve the information, which is a bit more programming than opening a recordset against the table. If you use a local table then it is absolutely sure that the information is available. Given the right user permissions, it is also very easy to make changes if necessary. Plus you can support multiple configurations … One note about my original post: I meant to say "assuming that you use an MDB or MDE file", not the opposite.

Thanks for sharing information. I’ll use them. CanadaDBA

]]>