June 25, 2015 at 9:31 am
Hello all,
I've been doing a good bit of research and came across this topic dealing with the subject at hand:
http://www.sqlservercentral.com/Forums/Topic383628-131-1.aspx
I have a good feeling this is where I need to be going, I just don't know how to input my specifics into the code to get it to work for my situation. Below is a list of hypothetical locations and pdf form names and what their purposes are:
"\\g1r-tr-871abg1\DBMan\frm2096\AF2096.pdf" = PDF doc I'm trying to auto fill. This doc already has corresponding field names to the accdb table field names.
"\\g1r-tr-871abg1\DBMan\frm2096\" = file path to which this doc will save to.
"\\g1r-tr-871abg1\DBMan\frm2096\DB\" = file path to accdb
"\\gts-tr-871abg1\DBMan\frm2096\DB\801ManningDB.accdb" = name of DB where the table to be used to import data.
"tbl_2096" = table in DB that I'm getting the fields from.
"TO, FROM, FULL_NAME, MEMBER GRADE, MEMBER SSAN, UNIT, AWARDED AFSC, AS, E_DT2, PAFSC, FROM, TO1, ENTER/CONTINUE AFSC, CAFSC FROM, TO2, DAFSC, E_DT, POSITION NO, SUPINFO, CC"
= list of fields to be populated into the PDF.
I would like it to "SaveAs" with the prompt to change the file name so the original template PDF doesn't get messed up. I've just taken over this DB and I've got a decent handle on what is already there but my leadership wants to automate a lot of forms to save time and they've tasked me to do it. I am decent with SQL statements but am dumber than a box of rocks with VBA. Any help would be most appreciated.
Thank you for your time,
James
June 25, 2015 at 1:04 pm
The only way to do things in something which isn't capable of working with Automation is to do so with the Shell command as described in the sample code you found. The commands beyond that part are specific to the PDF generation product that you are using, and that will have a significant learning curve if you don't know that object model for that product, and aren't familiar with VBA.
As noted in that same thread, the more common approach is to create a report in Access and save it as a PDF, or to use a Word template and populate it from Access using Automation, and then save the document in PDF format. In any case, I would suggest that you hire a consultant to give you a hand with it.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
June 26, 2015 at 6:58 am
That's what I was afraid of, my unit will not pay for a consultant and I hardly make enough money to pay for one out of pocket.
The main reason for not going the route of print to PDF was because my CoC is mandating Digital Certificate signing from our CAC's. This would create the requirement to print the form to PDF, convert it to editable field form and add 6 signature blocks every time we process a skill or career change. That process would happen daily and multiple times. You can see that automating it would save a load of time, personnel and money. Thank you for your insight though, if I do find a resolution I will definitely post up a VBA snapshot with the annotations to make it easy for the next person.
June 26, 2015 at 7:05 am
that link you posted has a nice code example by bledu; that's the sort of code i'd put in an SSIS package's script task, so you can query SQL, and have the script task populate the form and save it.
that sort of SSIS package can be automated by an event or a reguarly timed job.
is SSIS an option as far as your process and skillset goes?
Lowell
June 26, 2015 at 7:22 am
Truthfully, My experience only takes me as far as building queries in SQL view. I'm pretty knowledgeable with that and the process of creating databases that are standardized and uniform.
Right now the DB that I am tasked to fix, update and further expand has a DB Back End to a parent DB and then 3 child DB's that link to it. All the DB's have VBA script protection and are password protected to avoid sensitive info from being mishandled. Then the servers are tightly controlled to only allow access to a very minimal group of people. I've already corrected all the broken server, table and import/export links & have already updated to access 2010, it runs in a compatibility version 2007-2010. I will research SSIS and see if I can learn if I can and how I would with my current setup. Thanks for hopefully pointing me in the right direction.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply