Embed Files into MS Access? (1 Viewer)

[gblack]

I have an MS Access tool that pushes out Excel Spreadsheets... The VBA references an Excel Chart Template to format the graphs which get created via. the VBA code.

My issue is that I have to send the user about 4 chart templates for them to load, manually, to a specified Folder/Directory.

I'd like to bypass that initial (manual) end user loading task by having VBA load the files programmatically. This... I know how to do.

The problem is with the files themselves. In that I have to send these to the user along with the tool. So no matter what, the end user will have to do SOMETHING with them. I'd like to make it so the end users don't have to even see these files and that MS Access/VBA is able to load these chart templates behind the scenes.


That said... What I would like, is to somehow have these templates embedded within Access somewhere/in some way vs. having them as separate files outside the tool that the end user will have to detach from an email and place somewhere, specifically.

Is there anyway embed/attach these files into the MS Access application, so that my VBA code will always be able to access them? Or some way of packaging these files along with the tool, to arrive at the outcome I have described, here?

Maybe this is beyond the scope of Access... but I just wanted to ask to see if anyone else has run into this type of issue before and come to some resolution.

Thank You for any helps, tips, advice...

Gary

 

[CJ_London]

you can use the package and deployment wizard (or something like inno) to package the chart templates with access in a .exe file which your user would then run to setup the files as required.

Alternatively, for 2007 or later, you could add the files as attachments in a table and then have some code to export them to wherever when the file is opened or the user clicks a button

 

[gblack]

Hmm... I can add file attachments in a table? I didn't know that... seems promising...

Very cool... just googled it... seems like that will work, thanks. I may come back with VBA questions as to how to detach files from a table... but hopefully the internet will have the answers!

Thanks so much,
-G

 

[The_Doc_Man]

Just remember that embedding the files inside Access takes away from the maximum size of an .MDB or .ACCDB file - 2 Gb per file for the total of everything in that file, whether it is system tables, your tables, forms, reports, macros, modules, or OLE embedded objects.

 

[gblack]

Thanks Doc Man... I don't think the attachment size will be an issue... But I don't know how to programmatically download/detach the file I've posted within the attachment field of the table.

I basically created a table I call "TEMPLATE ATTACHMENTS" with one field (datatype = Attachment) in it I call "CHART_TEMPLATE"... Here's the code I am using, but it doesn't work...

Dim rsTemplate As DAO.Recordset

  Set rsTemplate = CurrentDb.OpenRecordset("SELECT * FROM TEMPLATE_ATTACHMENTS;")

  rsTemplate.MoveFirst
 
   '  Loop through the attachments.
   While Not rsTemplate.EOF
  
      '  Save current attachment to disk in the "My Documents" folder.
      rsTemplate.Fields("Chart_Template").SaveToFile "C:\Users\garland.black\Desktop"
      rsTemplate.MoveNext
   Wend

I just want the attached file to post to the Path (listed above). It's telling me "invalid datatype" but I have it listed as an attachment and there is an attached file in there... So... What am I doing wrong...?

 

[gblack]

Looks like this site gets you what you need, as far as the "attachment" data type goes:

http://sourcedaddy.com/ms-access/working-with-attachment-fields.html

I'll mark this solved, if the code in there does what it claims to do...

 

[gblack]

So I managed to attach an Excel Chart Template file into a table, then download that file to a specified place in on my computer using VBA from the site I linked above...but just in case the site goes down, here's the code I used to download the file:

Public Function SaveAttachments(strPath As String, Optional strPattern As
String = "*.*") As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String

    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")

    'Navigate through the table
    Do While Not rst.EOF

	'Get the recordset for the Attachments field
	Set rsA = fld.Value

	'Save all attachments in the field
	Do While Not rsA.EOF
	    If rsA("FileName") Like strPattern Then
	        strFullPath = strPath & "\" & rsA("FileName")

		'Make sure the file does not exist and save
		If Dir(strFullPath) = "" Then
		    rsA("FileData").SaveToFile strFullPath
		End If

		'Increment the number of files saved
		SaveAttachments = SaveAttachments + 1
	    End If

	    'Next attachment
	    rsA.MoveNext
	Loop
	rsA.Close

	'Next record
	rst.MoveNext
    Loop

    rst.Close
    dbs.Close

    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing

There's a lot more on attachment data types on that site, but this is the one that helped me download/detach my file to the place I wanted it to be.

Respectfully,
-Gary