• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Store an .MDB inside an .MDB -- interesting challenge

I want my application to be as self-contained as possible. To that end, I want to store an empty data .mdb in a table in the application .mde, so the user can select a menu option to "create new database" and presto there will be a new database file created and ready for use. I do not want to literally create a blank .mdb and run DDL statements to create tables, indexes and relations. There are many dozens of tables with configurartion data already loaded. It's far more reliable to simply have a ready-made database, ready to go.

I have tried loading the contents of an existing .mdb file into both a Text data type and an OLE Object data type. That seems to work OK and the length of the data in the column matches the .mdb file that I loaded. The problem comes when I try to extract the .mdb from the column and store it into the file system. Access says it has an invalid file format.

What I need:

A matched pair of VBA routines that can reliably load an .mdb from the file system, store into a field in a table; then extract the .mdb from the table and write it to the file system resulting in an .mdb that Access will accept.

Below are the routines I've been using for my unsuccessful attempts.

Private Sub SetEmptyDatabaseBLOB()
  Dim db As DAO.Database
  Set db = CurrentDb()
  Dim rs As DAO.Recordset
  Set rs = db.OpenRecordset("AppConfig")
  rs.MoveFirst
  rs.Edit
  rs!EmptyDatabaseBLOB = ReadAllFile("C:\test\NewDatabase.mdb")
  rs.Update
End Sub

Private Function ReadAllFile(filename) As String
  Dim fs As Object
  Set fs = CreateObject("Scripting.FileSystemObject")
  Dim t As Object
  Set t = fs.opentextfile(filename)
  ReadAllFile = t.ReadAll
  t.Close
End Function

Private Sub GetEmptyDatabase()
  Dim db As DAO.Database
  Set db = CurrentDb()
  Dim rs As DAO.Recordset
  Set rs = db.OpenRecordset("AppConfig")
  rs.MoveFirst
  Open "c:\sumgr\zztest.mdb" For Output As #1
  Write #1, rs!EmptyDatabaseBLOB
  Close #1
End Sub

Note shown are attempts to load the target .mdb into the field directly with the OLE Object "insert from file" menu choice.

-- Craig
0
Craig Yellick
Asked:
Craig Yellick
  • 3
1 Solution
 
harfangCommented:
Hello CraigYellick

You basically want to store a file. There are many good examples using images, but the nature of the content is of little importance. OLE is not the way... use  BLOBS ;)

ACC: Reading, Storing, & Writing Binary Large Objects (BLOBs)
http://support.microsoft.com/?kbid=103257

Sample Access 2000 mdb demonstrating appending bulk jpegs to a Jet mdb as blobs
http://www26.brinkster.com/alzowze/home.asp

Jamie's Software - Access Articles — Handling Images with Microsoft® Access
http://www.jamiessoftware.tk/articles/handlingimages.html

etc...

Good luck!
(°v°)
0
 
harfangCommented:
This being said, in a similar case, I chose to store the template file under a "safe" name in the application folder. The "creation of a new database" was then just a file copy... Easier to maintain and it keeps the size of the main application down, for a feature your users will need quite rarely...
Cheers!
(°v°)
0
 
John Mc HaleCommented:
An mdb is only a binary file.

Use a OLE Object field in your table to store the MDB file. Provide some Common Dialog File Open/Save functionality to your database application, and use ADO to transfer the actual blank .mdb to the user's hard disk.

0
 
Craig YellickDatabase ArchitectAuthor Commented:
Harfang, thanks a bunch. "AppendChunk", I forgot that is the method name. Tough to find help on a topic when you don't know the name. The quality of Access help seems to have gone down over time, for VBA programming anyway.

>> OLE is not the way... use  BLOBS

For table column data types, OLE is BLOB. But I do get your point.

>> Save template in folder

That's how it currently works. Based on past experience with technical support, it's better for everyone if the application .MDE can function on it's own. People install the app all over the place and move stuff around. The app file is already 12 Mb in size so adding another 5 Mb isn't going to be a significant issue.

-- Craig
0
 
harfangCommented:
Hello CraigYellick

I realised that right after posting. Indeed, the following data types are synonyms:
IMAGE, LONGBINARY, GENERAL, OLEOBJECT

In all cases, it's Access' "insert object" that generates to "OLE object" wrapper around the raw file. This is not  part of the data type.

Anyway, glad to help and good luck with your project!
(°v°)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now