Access database connection

[#1]

Hello:



I noticed your response on the Adobe web site. I am working with Lifecycle and I am trying to connect to an access database.



I step thru the setup and I get a Connection Successful message.



When I open the form it finds the first record in the table but that is all I get.



I want to be able to put in an employee number on the form and it will auto fill the employee information like First Name and Last Name.



Just like a lookup does.



Is there a way to get this done?



Please help.



Thank you



Jeff Wilson

South Dakota

[pguerett]

Yes but it involves programming .....post an email address and I will send you a sample.

[Jstaab]

Hey Paul:  Could you email the sample to me on selecting a specific record from an Access database.  Thanks.  Tracy

Tstaab@spokanecity.org

[pguerett]

Better yet here is a link to a blog entry that explains it as well.....

http://blogs.adobe.com/formbuilder/2006/09/selecting_specific_database_records.html

Paul

[#5]

Thank you Paul:



I have done several forms using java scripting and Livecycle ES

I was wondering if I was missing some code that needed to be done.



Thanks again in advance



jeff.wilson@amdacorp.com

[ibwaheemi]

hi paul can you post on to the forum this is really useful info for us newbies

[pguerett]

There is no place to attach a file so I have no choice but to mail it out as people request it.



Jeff I just sent it

[pguerett]

ibwaheemi here is the useful code from the sample (this is FormCalc script):



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

$sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat("Select * from Cust_Table where EMPID = '", this.rawValue, "'")

$sourceSet.DataConnection.open()

//xfa.host.messageBox("DataConnection opened")



$sourceSet.DataConnection.close()

//xfa.host.messageBox("DataConnection closed")

[_Jenn_Dickens_]

Good Afternoon Paul,



I was wondering if you would be so kind as to send me the sample file also. I was also wondering if, in this sample file, it offers the solution to populate other fields depending on the value in a drop down...



Example...



I have a part name Widget1. The part number for Widget1 is ABC123.



On my order form, I want to be able to pick the part name and have the part number automatically fill in.



Thank you in advance,



Jenn D.



jdickens@dse.net

[pguerett]

Is the Part number coming from the DB ....so based on the Part Number picked we have to go back the DB and get the part number?



Is the dropdown being populated by the DB as well?

[_Jenn_Dickens_]

Good Morning Paul,



Yes and Yes. I have an Access database that I have all the part numbers, names, rev #s, process owner, etc.



This is a document control (DC) project. I need my DC coordinator to be able to Email Engineering Change Forms to the engineers, have them fill them out and return them. My goal is, if it can be done, to have DC be able to pick a part number and have the corresponding information filled in for them. This ensures consistancy with the names and such. Then, DC will Email it to the Engineer and they will type in additional information specific to the change and submit back to DC. I would like all the field content to go back and get stored in the database.



I have the trial version of Adobe 9 with Livecycle 8.2 and have 28 days left to figure out if I can do this or not before I can put in the purchase req. to have the company buy the upgrade (currently using Adobe 8 with Livecycle 8.0, which doesn't play with Office 2007).



I just need to know if this can all be done or not. I usually can find a snippet, of code, here or there that I can get to work, but I don't have the luxury of time right now. Any guidance you can offer, even if to tell me that it can't be done, would be most appreciated.



Thank you and have yourself a wonderful day!



Jenn D.

[_Jenn_Dickens_]

I think I might have found a site that explains how to do what I need to do. I am going to play with it today and see if I can make any headway.



http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/



My biggest problem so far is that I need it to open with the fields blank until I pick the part number. My second biggest problem is having it add a new record to the database and calling the new form number (ECRNumber). This tutorial mentions to pay special attention to the auto-increment field so I'm keeping my fingers crossed. I'll have to let you know how it goes.



Have a great day!



Jenn D.

[_Jenn_Dickens_]

OK, I got creative with part of it. I only have one person that needs to fill in the doc number and corresponding information. Then she will distribute it to the engineer who needs it. They will fill out their portion and submit back to her.



What I did... I created a form (in Access 2007) with all the information that will need to go on the pdf form. I placed a hyperlink in my access db form that opened the pdf form. The pdf form is told to populate the fields with the last record of the sourceset. She will distribute and then it will get submitted back to her when engineer finishes filling it in.



The next step... getting the engineer's info into the database in the correct location... hopefully I can figure that out too!



Jenn D.

[pguerett]

So yes you can do what you want,,,,,,BUT - by using database connections on the form you will have to setup a Data source on each persons machine. The form needs to make use of this DSN to be able to acces the DB. Also the DB needs to be in a shared loacation so that each person can get at it and lastly if you intend to use Reader you will have to Reader Extend the form to allow Reader to do Database access (this can only be set by LiveCycle Reader Extensions not by Acrobat). Do you still want to proceed?

[_Jenn_Dickens_]

Not the way I had originally thought I wanted to. I have come up with a creative work-around though. (See previous posts in this discussion).



I'm almost to a point that I can start experimenting with how DC can send the engineer's info to the database once they submit it back to her.



I really appreciate you taking the time to respond to my posts. I hope you have a great weekend!



Jenn D.

[pguerett]

Getting the data from the form into the Access form will not be so easy. If it is only one user who is accesing the DB you can put Acrobat on their desktop and have the PDF add the records to the DB directly.

[_Jenn_Dickens_]

That is what I intend to do. (I do not need it to go into an Access form, just the database so that the information is usable for reporting). I haven't actually done a distribute/submit in Adobe 9 yet so I'm not sure how/if it differs from 8.0. If I can collect the 'submitted' forms and 'export' the info into the db in a sensible manner, I think that it will all work the way we need it too and everyone will be much happier than they are with the current system.



Thanx a bunch! Jenn D.

[ibwaheemi]

Paul i set up an ftp link for you to post stuff in



ftp://adobe:livecycle@paulguerette.kicks-ass.net/livecycle



it would help us learn from you



your knowledge and willingness to help is inspiring

[KRM240_]

Hi all

The issue that Jenn was working on is exactly what I need too.

I am looking for a solution that 1 single user changes a few criteria fields, using adobe pro,

Once the criteria is set, download the appropriate data from a database (Acess or Sql)

Then send the form to 1 user for completion,

they will send it back to a user who will update the database.

Jenn's issue sounds just like mine.

Anyone know how this was resolved or suggestions as to where I should look for a solution?