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("AppConfi
rs.MoveFirst
rs.Edit
rs!EmptyDatabaseBLOB = ReadAllFile("C:\test\NewDa
rs.Update
End Sub
Private Function ReadAllFile(filename) As String
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
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("AppConfi
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
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("AppConfi
rs.MoveFirst
rs.Edit
rs!EmptyDatabaseBLOB = ReadAllFile("C:\test\NewDa
rs.Update
End Sub
Private Function ReadAllFile(filename) As String
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
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("AppConfi
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
>> 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
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°)
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°)
Cheers!
(°v°)