Sending data from Access



  • My school stores student data using Access (I think 2007). It's not a lot of data, and a couple of hundred students. The secretary enters new students into the database and then draws up a contract. Nothing too onerous but still boring, so my boss asked if I could automate it somehow.

    I don't use Access but my rough idea was to have some sort of trigger (finished the row, or however data is entered) or a Ribbon button which exports the student to an app which will fill the details into the contract and print it.

    While it would possibly be easier to write the app to handle the data input and have it send the data to both Access and the contract at the same time, I would prefer to handle it through Access if possible.

    The contract will either be in Word or Excel, and I've done automation with those before.

    Some quick Googling tells me something called DDE exists, which sounds like it might be what I need. Basically I want to run a script from Access which sends the current record to my app. My app will receive a string list, shove the values into boxes, and print it.

    So, am I going about this the right way? Can macros handle this or only VBA?



  • @coldandtired Seems like a classic Mail Merge-type problem; Word has done this natively since the mid-90s.

    Try Googling for a tutorial on "mail merge" and see if you can find what you need.

    EDIT: I should add that while you probably can do this with macros and VBA, you'll avoid a lot of headache if you can accomplish what you need using the Mail Merge feature already built-in to the product.

    EDIT EDIT: In recent versions of Word, there's a "Mailings" header in the ribbon, which is where the Mail Merge functionality exists now. There's also a "step by step mail merge wizard" which might be helpful.



  • @blakeyrat Cheers. After a few minutes' reading it seems to do what I need but it looks like a more mass-mailing thing.

    Possibly it can be combined with a small macro that selects only one row before starting the merge?



  • @coldandtired It's a "populate this document template with values" thing. It's only called "Mail Merge" because it was designed to print envelopes back in the day, but you can fill in any kind of templated document you want.

    It depends on how your Access database is structured, but unless it's REALLY weird, it should be no problem to tell Word how to fill in the template with data in Access.



  • Off the top of my head (but based on experience)... Most likely... Add a column to the DB "Contract Generated" then use this to query for the Mail Merge, then set the value back in the DB. Single user is easy, multi-user watch for race conditions....



  • After a day of experimenting it seems I might be able to get away with just using Access.

    The contract form isn't too complicated so Access's form designer looks like it might be enough.

    My last problem is sending the selected record to the form, but hopefully that's not too complicated.


  • Notification Spam Recipient

    @coldandtired said in Sending data from Access:

    My last problem is sending the selected record to the form, but hopefully that's not too complicated.

    I could have sworn you just set the data source on the form properties, but I might be misremembering...



  • @coldandtired said in Sending data from Access:

    My last problem is sending the selected record to the form, but hopefully that's not too complicated.

    The easiest would probably just be to have the second form refer to the student's ID in a text box on the first form. Basically set the second form's data source to something like

    SELECT * FROM Students WHERE StudentID = [Forms]![FormName]![TextObjectName];
    

    Then the second form's data source will consist of just the single record that you want.

    If the first form isn't open when the second form is opened, the text box won't exist for it to refer to, and it'll pop up a prompt asking for the user to type it in.