InfoPath Dev

Hi, I'm trying to use an Access db as a standalone secondary data source, i.e., packaged with the InfoPath template. I'm essentially doing what the author of Chapter 6 in the superb *Developing Solutions with Microsoft InfoPath* has described on p. 174.

When I publish the form to a SharePoint forms library and then attempt to open the form from SharePoint on an XP client and query the Access db I get an ugly "Please enter JET OLE DB Initialization Information" dialog requesting Username, Password, Provider String and Open Mode. I cannot figure out how to get around this problem. Others have had it, but the questions on the Microsoft forum went unanswered: http://groups-beta.google.com/group/microsoft.public.infopath/search?hl=en&group=microsoft.public.infopath&q=%22jet+ole+db+initialization+information%22&qt_g=1

Here's the steps I'm taking, followed by the resulting XML for the adoAdapter in my xsf file:

1. Select the Tools | Data Connections menu command.
2. Click Add, select Receive Data, and select Database.
3. click Select Database and navigate to the mdb file, then click Open.
4. Click Next, uncheck Automatically retrieve data, click Finish, then click Close.
5. Select the Tools | Resource Files menu command and add companies.mdb.
6. Close the form and open the xsf file in Notepad.
7. Search for "adoAdapter". In the connectionString attribute remove the path to "companies.mdb". Delete the path leading to the mdb file, leaving only "companies.mdb" (because it resides locally now in the xsn file).
8. Save and close the xsf file.
9. Republish the form.

<xsf:dataObject name="Companies" schema="Companies.xsd" initOnLoad="no">
<xsf:query>
<xsf:adoAdapter connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=companies.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" commandText="select [Seibel AccountID] as [Seibel_AccountID],[TP Name] as [TP_Name],[Credited District] as [Credited_District],[Region],[ATU Account Manager Alias] as [ATU_Account_Manager_Alias],[x86 PCs] as [x86_PCs],[ID] from [Companies] as [Companies]" queryAllowed="yes" name="Companies"></xsf:adoAdapter>
</xsf:query>
</xsf:dataObject>

MORE INFO (edited after my original post): This may be a clue. If I navigate to the location where InfoPath downloads and unpacks the xsn file (e.g., C:\Documents and Settings\sharepointuser\Local Settings\Application Data\Microsoft\InfoPath\FormCache1\) and double-click the xsn file to run the form *I do not get the error*.

I also get the dialog when I select the Tools | Data Connections menu command in InfoPath and try to modify the existing "Companies" data connection.

Hi PAtrick,
Please ignore my questions in posthttp://www.infopathdev.com/forums/topic.asp?TOPIC_ID=698. We'll look into this today.
Regards

Patrick Halstead [InfoPath MVP]
InfoPathDev

I really appreciate it, Patrick! If you need my template lemme know. I'm trying to check the forum continually but feel free to email me directly if you need something from me quickly.

Patrick, I also tried to use the shared database approach you document on pp. 174-175 in your book. I don't get the same gray dialog, but I can't make it work at all (other gray dialogs). Creation of the data source in InfoPath goes fine and all seems well, but when I run the app it won't connect, not even when I launch the xsf from the user's Application Data folder (which does with with the standalone database approach). I've verified that my client box connected to the domain has access to the share.

UPDATE: The error dialog I'm getting reads in part, "[0x80040E10][Microsoft OLE DB Provider for ODBC Drivers][Microsoft[ODBC Microsoft Access Driver]Too few parameters. Expected 1."

I also tried it using VBScript instead of managed code. Same error.

Hi Patrick,
Sorry for the delay; we'll try to investigate this more tonight.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Thanks Patrick. Just thought I'd give you a little update. I issued a Code Signing cert, put it in the Current User/Personal/Certificates store, signed the form and granted Full Trust. I had high hopes when I saw that I could now read Environment.UserName, but the Access problem is still there. I tried a fully trusted form with both the standalone and share approaches.

Hi again,
You definitely don't need to do Full Trust for username. Domain-level trust with managed code works just fine. We'll look into your access issues.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Hello pmbarnes,
InfoPath throws that dialog box when it cannot connect to the DB whose path is given in the manifest.xsf. In throwing this dialog box it#%92s giving the user the option to map to DB in another location.

What we open the Standalone DB solution from sharepoint -> it is searching for the access mdb file on your desktop and that is the reason its throwing the error. So the better option when you are publishing to sharepoint is to use the Shared DB approach. The only difference in the dataconnection for Shared DB approach is change the connectionString. The “Data Source” value will change from companies.mdb to something like \\system10\e\companies.mdb which is the shared path to the mdb file.

So what I would suggest is
1. Copy the mdb file to a shared location
2. Open manifest.xsf of your standalone DB solution.
3. Change the Data source value to map to the DB in shared location
4. Publish this solution to sharepoint

This should work. Please let us know if you see any issues.

Agni
InfoPath Dev India

Agni,

Thanks for looking into this, but as my postings state, I tried all of this. For the standalone scenario I removed the path and used just the name of the db after adding it to the template's Resources. This is as the author explains on p. 174 of *Developing Solutions with MS InfoPath.* Then I tried the shared approach explained on p. 175. I checked the path in the manifest.xsf file after adding the shared db via the InfoPath UI. It had the proper network path.

Neither approaches work with a SharePoint form library on a domain controller. Have you tried to reproduce my problem? I could send you the database file, template, whatever you need.

Patrick

P.S. Others have been having this problem, e.g., http://groups-beta.google.com/group/microsoft.public.infopath/browse_thread/thread/89ebfe2337ddf268/6b26edf02445b506?lnk=st&q=access+%22jet+ole+db+initialization%22&rnum=6&hl=en#6b26edf02445b506

Patrick, just a quick note to say that this is a Microsoft project. I just tried deploying the template using the standalone db on an MS internal site http://team/sites/<name>) and got the same error. So it's not just my VPC domain configuration. We're running up against a deadline so we're just going to have to jettison any use of Access at this point and go with something like 26 separate xml data docs (one for each letter of the alphabet) to break up the 4200 company records (so that performance doesn't suffer). Too bad. Access should work and would have been a better data source, I think. I still hope I can learn why Access won't work.

OK, well, time to send e-mail to MS. Please be patient. Could take a day or two.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Great! We can be patient. I'm just glad you're even willing to help. It is much appreciated. I hope that MS will also find some value in helping us figure this out. I don't think what we're trying to do is that out of the ordinary. It should "just work".

Hi again. I sent e-mail to MS. The general consensus over here is that the Standalone problems you are seeing on your SharePoint site are a strange permissions issue. I'm assuming that your site is in the Local Intranet zone. Please confirm. I hope to have more info in a day or so.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Yes, Local Intranet. I also tried it as a Trusted site. Thanks!

OK. I hope to get a definitive answer today, but might be tomorrow. Anyway, if you have an MS internal alias, e-mail that to me and I'll cc you on the thread.

Patrick Halstead [InfoPath MVP]
InfoPathDev