I was able to do this using the SQL Native driver. I did the following:
Create a new database connection, and select OLEDB database. Click Next.
For Connection String, click the Build button.
From the provider list, select the SQL Native Client. Click Next.
For the data source, enter the SQL server name or IP address.
Select the security setting that will get you logged on, based on your SQL server settings.
Click Test Connection. If it is successful, you are good to go. You can then select the initial catalog (database) to use.
Click OK, you should be done.
I copied this form to another computer and it worked OK, so there is no need to set up a DSN with this method. However, this may not entirely solve your problem since you will probably need the SQL native driver on any computer that uses the form. There is no way around this; one way or another the computer needs a hook to communicate with the database.
My preferred method for this sort of thing would be to use web services rather than direct database connections. You will need to Reader Extend your form to enable web services, but this will solve a bunch of problems with security and access from multiple user machines. Provided you have the ability to create web services, of course.
