Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

The Mystery of Connecting to an Access Database

Avatar

Level 2
I am just starting to use designer and with some stumbles along the way have perfected the layout of the form I am trying to create. The next step is connecting it to a Microsoft Access database. However, I am having some problems.



I have looked everywhere imaginable for a "how-to" on connecting an Access database to my form in Designer. I have gone through setting up data connections several times, all with the same result: "Connection for Source NPIF failed because the environment is not trusted". NPIF is the name of the data connection I've been trying to set up.



I realize this is probably a very boring problem to solve, but I am desperate! I have not seen any answers to other posts asking this same question, so I'm worried this one will be overlooked too. Please, there are many of us out there who cannot figure this out! Help us?



Other people seem to have set this up with no problems. Maybe one of you can post the steps you took to get it to work - share the love!!



Thank you, Elizabeth
76 Replies

Avatar

Former Community Member
Ok, here's some simple steps:



- In the Data View tab, right click and choose New Data Connection

- Choose OLEDB Database and click Next

- Click Build

- Click Next

- Open the topmost drop down and select the DSN name

- Click OK

- Select Table and choose the table, or SQL Query and type in a SQL query.

- Click Next

- Click Finish

- Drag fields from the Data View tab onto the form.



You should not get that error message.



Chris

Adobe Enterprise Developer Support

Avatar

Level 2
Thank you for replying! Ok, I'm following your instructions.



- In the Data View tab, right click and choose New Data Connection

[I named it NPIF]

- Choose OLEDB Database and click Next

- Click Build

[Selected "Microsoft OLE DB Provider for ODBC Drivers"]

- Click Next

- Open the topmost drop down and select the DSN name

[In the "Use data source name" drop down: I selected "MS Access Database"]

- Click OK



Here's where the window I get is different. It is asking me to select a database, not a table.

[I browsed to the network drive and selected my database from it's folder]



Then it is back on track with your instructions.



- Select Table and choose the table, or SQL Query and type in a SQL query.

[I selected the "Clients" table]

- Click Next

- Click Finish

- Drag fields from the Data View tab onto the form.

[I dragged all the field onto my form]



Ok, I don't get that error message anymore. Yes! But now when I click on the arrow on the drop down box it doesn't drop down. Is there a script I have to put in it?



Thank you so much for your help!

Avatar

Former Community Member
Probably. What is supposed to be in it? Was it one of the fields you dragged from the data connection? If so, what kind of field is in your Access DB?



Chris

Adobe Enterprise Developer Support

Avatar

Former Community Member
Elizabeth,



I too have been struggling with this. There is an article "Providing Interactive Database Lookup From Forms" on this page -- http://www.adobe.com/devnet/livecycle/designing_forms.html

This article provides a scripting sample for a drop down list populated from an Access database.



I'm getting a few syntax errors which I'm trying to locate, but this is a pretty good how-to.



Dan

Los Angeles Firemen's Credit Union

Avatar

Level 2
Sorry it took me a while, but I was out of town yesterday. Hopefully, we can get this working today though!



Chris:

The drop down box is supposed to have the Clients from my database in it. Yes, it is one of the fields I dragged over from the data connection. In the Access DB it is a regular text field. When I click on the arrow in the PDF nothing happens. Nothing drops down. Is there a problem with the connection?



Dan:

Thank you for your help too. I checked out that article and went though the steps listed and I am also getting a sytax error which says there is an illegal character in the SelectField Initialize script. Is that the same one that you're getting? Have you figured out how to fix it?



I have made a copy of the form and am attempting both ways you guys have suggested. So far neither of them are working yet. Where am I going wrong??

Avatar

Former Community Member
Hmm.. I'm surprised that dragging a field from the data view would create a drop down list instead of a text field since that will never work without script. Try replacing that drop down list with a Data Drop Down LIst (from the custom tab). Modify the script so that:



sDataConnectionName holds the name of the data connection

and sColHiddenValue and sColDisplayText hold the name of the column in the db.



Chris

Adobe Enterprise Developer Support

Avatar

Level 2
Ok, I replaced the drop down box. And put in the connection name and the column name. Now when I click on the drop down the debugger gives me the following message:



TypeError: oTextNode has no properties

68:XFA:ProjectInformationForm[0].Page1ClientBillingProject[0]DClient[0].DataDropDownList[0]:initialize

Avatar

Former Community Member
Elizabeth,



I'm still encountering "1-warnings/errors reported" and have yet to find the syntax error in the "Providing interactive databae lookup from forms" sample.



I'll update you once I have some success.



Dan Riggs

LAFCU

Avatar

Former Community Member
Elizabeth, can you paste your script here?



Dan, what does the error in the warning/errors reported section say?



Chris

Adobe Enterprise Developer Support

Avatar

Level 2
Chris, here's my script. It's in the initialize event.<br /><br />----- ProjectInformationForm.Page1ClientBillingProject.Client.DataDropDownList::initialize - (JavaScript, client) <br /><br />/* This dropdown list object will populate two columns with data from a data connection.<br /><br /> sDataConnectionName - name of the data connection to get the data from. Note the data connection will appear in the Data View.<br /> sColHiddenValue - this is the hidden value column of the dropdown. Specify the table column name used for populating.<br /> sColDisplayText - this is the display text column of the dropdown. Specify the table column name used for populating.<br /><br /> These variables must be assigned for this script to run correctly. Replace <value> with the correct value.<br />*/ <br /><br />var sDataConnectionName = "NPIF"; // example - var sDataConnectionName = "MyDataConnection";<br />var sColHiddenValue = "ClientName"; // example - var sColHiddenValue = "MyIndexValue";<br />var sColDisplayText = "ClientName"; // example - var sColDisplayText = "MyDescription"<br /><br />// Search for sourceSet node which matchs the DataConnection name<br />var nIndex = 0;<br />while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)<br />{<br /> nIndex++;<br />}<br /><br />var oDB = xfa.sourceSet.nodes.item(nIndex);<br />oDB.open();<br />oDB.first();<br /><br />// Search node with the class name "command"<br />var nDBIndex = 0;<br />while(oDB.nodes.item(nDBIndex).className != "command")<br />{<br /> nDBIndex++;<br />}<br /><br />// Backup the original settings before assigning BOF and EOF to stay<br />var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");<br />var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");<br /><br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");<br /><br />// Clear the list<br />this.clearItems();<br /><br />// Search for the record node with the matching Data Connection name<br />nIndex = 0;<br />while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)<br />{<br /> nIndex++;<br />}<br />var oRecord = xfa.record.nodes.item(nIndex);<br /><br />// Find the value node<br />var oValueNode = null;<br />var oTextNode = null;<br />for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)<br />{<br /> if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)<br /> {<br /> oValueNode = oRecord.nodes.item(nColIndex);<br /> }<br /> else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)<br /> {<br /> oTextNode = oRecord.nodes.item(nColIndex);<br /> }<br />}<br /><br />while(!oDB.isEOF())<br />{<br /> this.addItem(oTextNode.value, oValueNode.value);<br /> oDB.next();<br />}<br /><br />// Restore the original settings<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");<br /><br />// Close connection<br />oDB.close();

Avatar

Former Community Member
Ok, try this instead:



var sDataConnectionName = "NPIF"; // example - var sDataConnectionName = "MyDataConnection";

var sColHiddenValue = "ClientName"; // example - var sColHiddenValue = "MyIndexValue";



// Search for sourceSet node which matchs the DataConnection name

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}



var oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

oDB.first();



// Search node with the class name "command"

var nDBIndex = 0;

while(oDB.nodes.item(nDBIndex).className != "command")

{

nDBIndex++;

}



// Backup the original settings before assigning BOF and EOF to stay

var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");

var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");



oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");



// Clear the list

this.clearItems();



// Search for the record node with the matching Data Connection name

nIndex = 0;

while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}

var oRecord = xfa.record.nodes.item(nIndex);



// Find the value node

var oValueNode = null;

var oTextNode = null;

for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++) {

if(oRecord.nodes.item(nColIndex).name == sColHiddenValue) {

oValueNode = oRecord.nodes.item(nColIndex);

}

}



while(!oDB.isEOF())

{

this.addItem(oValueNode.value);

oDB.next();

}



// Restore the original settings

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");



// Close connection

oDB.close();



Chris

Adobe Enteprise Developer Support

Avatar

Level 2
Ok, I copied that into the drop down initialize event and I'm not getting the JavaScript error anymore - good! But the text boxes are not being filled in after I select a client. Is there another script for that?

Avatar

Former Community Member
Chris,



Unfortunately, this is all that is reported in the Warnings tab when I access the PDF Preview tab:



Error: Syntax Error

PDF generated successfully



In the Warnings tab, above this message is a complete string of the code, yet I do not see any notation as to where is the syntax error.



Dan Riggs

LAFCU

Avatar

Former Community Member
Elizabeth, yes, you need a script to make to get the proper results based on the selection in the drop down. Take a look at the form that Dan links to above, the script on the refresh button does exactly that.



Dan, can you post the complete string of code. Maybe I can decipher it. Or can you put your form online somewhere? I didn't see this when I downloaded the form earlier.



Chris

Adobe Enterprise Developer Support

Avatar

Level 2
I copied out the button and formcalc from the link and changed it to my field names. When I click the refresh button the mouse changes to an hourglass for 30sec - 1min, but then nothing gets filled in. If I click the button a second time, the button turns blue but still nothing gets filled in.



This is the formcalc on the click event of the refresh button:



if (Len(Ltrim(Rtrim(ClientName.rawValue))) > 0) then

$sourceSet.DataConnection.#command.query.commandType = text

$sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(Select * from Clients Where ID = , Ltrim(Rtrim(ClientName.rawValue)) ,)

//Reopen the Dataconnection

$sourceSet.DataConnection.open()

endif

Avatar

Former Community Member
Chris,



I updated my script from the sample you posted for Elizabeth, with a few minor differences--I'm using the MS Access database file purchase.mdb.



The error I'm encountering is not from the Initialize event for the Select ID drop down as far as I can tell, but from the Click event for the Refresh button. Here's the error:



error: accessor '$sourceSet.dataConnection.#command.query.commandType' is unkown.



Here's the code from the Click event:



IF (Len(Ltrim(Rtrim(SelectProdID.rawValue))) > 0) then

$sourceSet.DataConnection.#command.query.commandType = "text"



$sourceSet.DataConnection.#command.query.select.nodes.

item(0).value = Concat("Select * from OfficeSupplies Where ID = '

Ltrim(Rtrim(SelectProdID.rawValue))'")



//Reopen the Dataconnection

$sourceSet.nodes.item.open()



endif



The SelectID field still does not display any values in the drop down, so I added a query button with the following script:



var ProdID = xfa.host.response("Enter the Product ID");

SelectProdID = ProdID;



The SelectProdID field value on the form is used to query the specific Access record using "...WHERE ID=..." in the above script for the Refresh button.



Hopefully, that makes sense.



Dan Riggs

LAFCU

Avatar

Former Community Member
Chris,



I saw your request after my last posting, so here is the full script from the Initialize event of the Select ID drop down field:



var sDataConnectionName = "DataConnection2";

var sCol1 = "ID";

var sCol2 = "PART_NO";

var sCol3 = "DESCRIPTION";

var sCol4 = "UNITPRICE";



//Search for sourceSet node which matches the DataConnection name

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

oDB.first();



//Search node with the class name "command"

var nDBIndex = 0;

while(oDB.nodes.item(nDBIndex).className != "command")

{

nIndex++;

}

// Backup the original settings before assigning BOF and EOF to stay

var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");

var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");



// Need to set BOF and EOF to stay

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF","bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF","eofAction");



//Clear the list

this.clearItems();



//Search for the record node with the matching Data Connection name

nIndex = 0;

while(xfa.record.nodes.item.(nIndex).name != sDataConnectionName)

{

nIndex++;

}



var oRecord = xfa.record.nodes.item(nIndex);



// Find the values of the record

// Find the value node

var oValueCol1 = null;

var oValueCol2 = null;

var oValueCol3 = null;

var oValueCol4 = null;

for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)

{

if(oRecord.nodes.item(nColIndex).name == sCol1)

{

oValueCol1 = oRecord.nodes.item(nColIndex);

}

else if(oRecord.nodes.item(nColIndex).name == sCol2)

{

oValueCol2 = oRecord.nodes.item(nColIndex).value;

}

else if(oRecord.nodes.item(nColIndex).name == sCol3)

{

oValueCol3 = oRecord.nodes.item(nColIndex).value;

}

else if(oRecord.nodes.item(nColIndex).name == sCol4)

{

oValueCol4 = oRecord.nodes.item(nColIndex).value;

}

}



while(!oDB.isEOF())

{

this.addItem(oValueCol1.value, oValueCol2.value, oValueCol3.value, oValueCol4.value);

oDB.next();

}



// Resotre the original settings

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");



// Close connection

oDB.close();

Avatar

Former Community Member
Dan:



> error: accessor '$sourceSet.dataConnection.#command.query.commandType' > is unkown.



Are you using Reader or Acrobat? DB connection does not work in Reader unless the PDF has additional rights given to it using Reader Extensions Server.



Elizabeth:



I think I've hit the limit of the advice I can give without sitting down and looking through your form. If you can post it somewhere online I'll try to take a look as soon as I can.



Chris

Adobe Enteprise Developer Support

Avatar

Level 2
Chris, I've uploaded the form to http://home.comcast.net/~italianprincess2/NPIF-Data_Connection.pdf



I would really appreciate it if you could look over the form and tell me what I'm doing wrong. Thank you so much for your help!

Avatar

Former Community Member
Chris,



The form I'm using is in .xdp format. I'm testing in the PDF Review tab of Adobe Designer, neither Reader or Acrobat, unless the PDF Review tab utilizes one of the two.



Eventually, the .xdp forms I'm creating will be part of a workflow(s) and accessed with Adobe Form Manager. I wasn't aware that DB connections do not work in Reader, so that is good to know.



Dan Riggs

LAFCU