MS Access 2003 - Importing a Text File into MS Access Database Table

Question

At work we have this reporting tool. It is distributed to everyone as a MDE tool that is locked up pretty good (VBA is unviewable, cannot import any of the table, query, forms, etc). When each person fills out their applicable portion there is a button that conslidates it into a text file, and then sends it to Outlook. Then everyone emails it to one person.

So I have come up with need to use this in my own database:

Remote Employees fill out a form that creates a power point presentation for them, and this part I think I have nailed down. This helps us track metrics on these presentations, rather than the employee manually creating a the ppt, and then we coming behind and manully entering the data from the brief into a form. Makes sense right.

Here is my problem, at the office, this is solved, but for those out in the field I need a similiar tool like the one mentioned above; where they get the benefit of the autogenerated ppt, and then I can have them send me the text file through email I can add to the db.

Here are my questions because I am just getting into the beginning of this:

-The form is pretty long because there is A LOT of info going into a ppt, so I use one form with tabs for different sections, but it all becomes on record in the table, and one ppt. How do I turn all this information, this one record, into a text file, and how do I use the Send to Outlook, all with one button click??

-When the user emails em the text file, how do I import it into the database table?

-How do you lock up a MDE so that the VB is unviewable, and the object cannot be imported into another application?

any other advice, tips, "your crazy man!"s, are welcome! thanks as always!

Answer 1

Have you considered replication rather that a text file? The data would be stored in a replicated back-end file with Access Security, which could be returned to you. CDO should suit for emailing.

Text

Access has DoCmd.TransferText, which will allow you to both export and import a text file.

CDO

   Private Sub SendEmailCDO()
   'Requires reference to Microsoft CDO for Windows 2000
   Dim cdoConfig As Object
   Dim strSubject As String
   Dim strBody As String
   Dim strFile As String
   Dim cdoMessage As Object

       'Set up detail of the mail server
       Set cdoConfig = CreateObject("CDO.Configuration")
       With cdoConfig.Fields
           .Item(cdoSendUsingMethod) = 2 ''cdoSendUsingPort
           .Item(cdoSMTPServerPort) = 25
           .Item(cdoSMTPServer) = "smpt.themailserver.com"
           .Item(cdoSendUserName) = "[email protected]"
           .Item(cdoSendPassword) = "password"
           .Update
       End With

       ''This is the subject line for the email.
       strSubject = "Membership List"

       ''This is the message with a little HTML.
       strBody = "<P>Here is the membership list for <FONT color=#ff0000>" _
        & Format(Date, "mmmm yyyy") & "</FONT>.</P><P>Regards, LTD</P>"

       ''Location of Attachment
       strFile = "C:\Docs\MembershipList.rtf"

       ''Set up the email message
       Set cdoMessage = CreateObject("CDO.Message")
       With cdoMessage
           .Configuration = cdoConfig
           .Subject = strSubject
           .From = "[email protected]"
           .To = "[email protected]"
           .HTMLBody = strBody
           .AddAttachment strFile
           .Send
       End With

   End Sub

Further information: http://wiki.lessthandot.com/index.php/Access_and_Email

Answer 2

@Justin asks:

-How do you lock up a MDE so that the VB is unviewable, and the object cannot be imported into another application?

The question makes no sense, unless the person asking it has failed to grasp what an MDE is. THERE IS NO VIEWABLE CODE LEFT IN AN MDE. It has been stripped out and all that remains is the compiled p-code. For an a helpful article on VBA compilation in Access that incidentally explains the relationship between canonical code and compiled p-code, see Michael Kaplan's "The real deal on the /Decompile switch."

Keep in mind that this applies only to code-bearing objects (forms/reports/modules) and not to tables and queries.