InfoPath Dev

I need to connect an infopath form to an access database that has been secured using the Security Wizard. I have been able to set up the data connection by choosing the Microsoft Jet 4.0 OLE DB Provider and specified the path to the workgroup file by editing the value of Jet OLEDB:System database on the All tab. I also wish to not save the username or password which I strip out on the same tab.
When I try to save the connection it prompts me for a username and password and saves the username with the connection. Is there a way around this?
I need each user to have to supply their own username and password when they submit the infopath form, but since the username is saved, the user is only prompted for a password.
I do grab the network username of the user and populate a field on the form with it, but cannot figure out how to use this in place of the username that infopath is trying to use to submit since I cannot write code that runs when the submit event occurs. (Or can I?)

Thanks....Beth

Hi Beth and welcome to our forum!
Sorry for the delay in responding. I'm guessing that you are in the InfoPath designer. Is that true? Have you tried exporting the form to form files and manually removing the username from the XSF file before publish? There's probably an easier way, but I'd try that first. I don't think you can populate the network username in the challenge dialog that pops up because that is internal to InfoPath and there's no way to access it. Well, maybe there is. If you define the data connection in code (as opposed to the data connections) you can get the username in the OnLoad code and then set up the OLE connection using that. In your submit rule you can call some code that will allow you to do the submit to that data connection. That may or may not popup the challenge dialog with the username but it's worth a try. If you need help, let us know. Once again, sorry for the delay in responding. Cheers!

Thanks for your reply.

I was able to create the connection with a username, then delete the username from the database so it does not exist. I then use the following code to submit.
Set oNetwork = CreateObject("WScript.Network")
sUserName = oNetwork.UserName
Password = Inputbox("Password","Ferguson Password","")
strConn = XDocument.DataAdapters("Main connection").connection
strFind = "UID=Ferguson"
strConn = replace(strConn, strFind, "User ID=" & sUserName)
strFind = "PWD=Ferguson"
strConn = replace(strConn, strFind, "PWD=" & Password)
XDocument.DataAdapters("Main connection").connection = strConn
XDocument.DataAdapters("Main connection").Submit

My problem is prompting them for the password when the characters being typed are viewable.

I had also tried submitting without the code with a user that has been deleted from the database, but they get an error box stating that the user does not exist and then a login window - I would be fine with the login window, but the error box has to go. Any suggestions???

Beth