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.
Thanks very much for your timely response,
I have used the Sql Native Driver option and heres what keeps happening. I continually get this error..."Connection for Source DataConnection failed because the environment is not trusted". It will then populate my form dropdowns with the first record form the DB and nothing else. Any suggestions?
Check out the following blog entry:
(Ignore the fact that it talks about ODBC connections; it should work for the native SQL connection too). It explains how to read through records in a database. There is also an answer to your issue of trusted connections; scroll down the page to the User Responses section and look at 9/10/11.
Stefan's blog is a great resource for learning about some of the more complicated interactions with forms. Also take a look at this entry: http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/
It talks about adding/deleting/updating database records.
I'll look into that and see what I can do. Thanks again for your help with theses issues.