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

Can you CREATE an mdb file without a full version of Access installed?

You can do a fair bit of stuff in VBScript to an mdb file.
You can make an ADO connection and presumably create tables and queries.
You certainly can query the data in it, and do CRUD operations.

Using VBScript or non-Access VBA (Word, Excel, PowerPoint) can you actually create an entire mdb file whole-cloth?
Not copying some existing file and killing all its user-created object, but creating an honest-to-goodness from-scratch mdb file.

.Net and Visual Studio can do this, and ASP editors also -- they DON'T count.
VBScript or VBA -- that's the required ticket.

A working sample file is required for full points
0
Nick67
Asked:
Nick67
  • 6
  • 5
  • 2
  • +3
5 Solutions
 
Bill PrewCommented:
Give this a try, works for me.

Dim Catalog
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Temp\EE28591169.mdb"

Open in new window

~bp
0
 
PatHartmanCommented:
You can create databases  using the Access runtime but only objects that would be accessible in an .accde such as tables/queries/macros.  I have one app that needs to create an archive once an audit is complete.  Included in the archive is a form for control and a few reports.  I was not able to place the form or reports in the archive database.  The solution was to create an archive template that included all the objects.  Then the archive process copied the template and gave it a new name; linked to the tables and ran append queries to archive the data; and the final step deleted the archived data.
This is the procedure that actually creates the archive.  Prior to running this, validation is done to determine if the user has the authority to do the archive since it is a PITA to undo.  The app also copies the BE just in case.
Public Sub CreateExtendedArchive()
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim FromFileName As String
    Dim ToFileName As String
    Dim bePath As String
 '   Dim strFileName As String
    Dim strPath     As String
    Dim fs          As Scripting.FileSystemObject

On Error GoTo Proc_Err

    Set db = CurrentDb()    ' delete links
    db.TableDefs.Delete "TEMPtblReviewStatus"
    db.TableDefs.Delete "TEMPtblStatus"
    db.TableDefs.Delete "TEMPtblRoles"
    db.TableDefs.Delete "TEMPtblListValues"
    db.TableDefs.Delete "TEMPtblAuditParms"
    db.TableDefs.Delete "TEMPtblDocuments"
    db.TableDefs.Delete "TEMPtblMembers"
    db.TableDefs.Delete "TEMPtblRefDocs"
    db.TableDefs.Delete "TEMPtblDependents"
    db.TableDefs.Delete "TEMPtblHelpComments"
    db.TableDefs.Delete "TEMPtblLtrSent"
    db.TableDefs.Delete "TEMPtblVerificationPhase"
    db.TableDefs.Delete "TEMPtblTPA"
    db.TableDefs.Delete "TEMPtblComments"
    
    
    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    If Forms!frmTPAName!txtPathName & "" = "" Then
        MsgBox "Please select destination folder for the archive database.", vbOKOnly + vbInformation
        Forms!frmTPAName!txtPathName.SetFocus
        Exit Sub
    Else
        strPath = Forms!frmTPAName!txtPathName
        If Right(strPath, 1) = "\" Then
        Else
            strPath = strPath & "\"
        End If
    End If

    If Forms!frmTPAName!txtDatabaseName & "" = "" Then
        MsgBox "Please enter a name for the archive database.", vbOKOnly + vbInformation
        Forms!frmTPAName!txtDatabaseName.SetFocus
    Else
        ToFileName = strPath & Forms!frmTPAName!txtDatabaseName
    End If
    
    'Make sure there isn't already a file with the name of the new database
    If Dir(ToFileName) <> "" Then Kill ToFileName
    
    'Create a new accdb file
    bePath = DLookup("JetPath", "UsysConnectionString", "LinkedID = 1")
    FromFileName = bePath & "AuditArchiveTemplateVer49.accdb"
    fs.CopyFile FromFileName, ToFileName, True
    
    Set db = ws.OpenDatabase(ToFileName)
    
    'link tables from archive database
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblReviewStatus", "TEMPtblReviewStatus"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblStatus", "TEMPtblStatus"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblRoles", "TEMPtblRoles"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblListValues", "TEMPtblListValues"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblAuditParms", "TEMPtblAuditParms"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblDocuments", "TEMPtblDocuments"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblMembers", "TEMPtblMembers"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblRefDocs", "TEMPtblRefDocs"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblDependents", "TEMPtblDependents"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblHelpComments", "TEMPtblHelpComments"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblLtrSent", "TEMPtblLtrSent"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblVerificationPhase", "TEMPtblVerificationPhase"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblTPA", "TEMPtblTPA"
    DoCmd.TransferDatabase acLink, "Microsoft Access", ToFileName, acTable, "tblComments", "TEMPtblComments"
    
    db.TableDefs.Refresh        'needed because Access doesn't always "see" the new table quickly
 
    'Using embedded SQL because Access "breaks" queries by changing all solumn names to "ExprX" if the tables they link to do not exist
    DoCmd.RunSQL ("Insert Into TEMPtblAuditParms Select * From tblAuditParms  Where AuditparmsID = " & Forms!frmLogin!cboAuditParmsID)
    DoCmd.RunSQL ("Insert Into TEMPtblReviewStatus Select * From tblReviewStatus")
    DoCmd.RunSQL ("Insert Into TEMPtblStatus Select * From tblStatus")
    DoCmd.RunSQL ("Insert Into TEMPtblRoles Select * From tblRoles")
    DoCmd.RunSQL ("Insert Into TEMPtblListValues Select * From tblListValues  Where AuditparmsID = " & Forms!frmLogin!cboAuditParmsID)
    DoCmd.RunSQL ("Insert Into TEMPtblDocuments Select * From tblDocuments")
    DoCmd.RunSQL ("Insert Into TEMPtblMembers Select * From tblMembers Where AuditparmsID = " & Forms!frmLogin!cboAuditParmsID)
    DoCmd.RunSQL ("Insert Into TEMPtblRefDocs SELECT tblRefDocs.* FROM tblMembers INNER JOIN tblRefDocs ON tblMembers.EmpID = tblRefDocs.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    DoCmd.RunSQL ("Insert Into TEMPtblDependents SELECT tblDependents.* FROM tblMembers INNER JOIN tblDependents ON tblMembers.EmpID = tblDependents.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    DoCmd.RunSQL ("Insert Into TEMPtblHelpComments SELECT tblHelpComments.* FROM tblMembers INNER JOIN tblHelpComments ON tblMembers.EmpID = tblHelpComments.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    DoCmd.RunSQL ("Insert Into TEMPtblLtrSent SELECT tblLtrSent.* FROM tblMembers INNER JOIN tblLtrSent ON tblMembers.EmpID = tblLtrSent.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    DoCmd.RunSQL ("Insert Into TEMPtblVerificationPhase SELECT tblVerificationPhase.* FROM tblMembers INNER JOIN (tblDependents INNER JOIN tblVerificationPhase ON tblDependents.DepID = tblVerificationPhase.DepID) ON tblMembers.EmpID = tblDependents.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    DoCmd.RunSQL ("Insert Into TEMPtblTPA Select * From tblTPA")
    DoCmd.RunSQL ("Insert Into TEMPtblComments Select tblComments.* FROM tblMembers INNER JOIN (tblDependents INNER JOIN tblComments ON tblDependents.DepID = tblComments.DepID) ON tblMembers.EmpID = tblDependents.EmpID WHERE tblMembers.AuditParmsID = " & [Forms]![frmLogin]![cboAuditParmsID])
    
    If Environ("UserName") = "Pat" Then     'give me a chance to cancel delete so I can keep testing with the same data
        If MsgBox("Do you want to finish the archive?", vbYesNo + vbQuestion) = vbNo Then
            Exit Sub
        End If
    End If

    
    'update audit archive date in audit log
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs!qAuditLog
        qd.Parameters![enterauditparmsid] = Forms!frmLogin!cboAuditParmsID

    If Forms!frmLogin!cboAuditParmsID <> 1 Then 'don't update for training audit
        Set rs = qd.OpenRecordset
            rs.Edit
            rs!AuditArchiveDate = Date
            rs!AuditName = Forms!frmLogin!cboAuditParmsID.Column(2)
            rs!CoName = Forms!frmLogin!cboAuditParmsID.Column(3)
            rs!CoAbbr = Forms!frmLogin!cboAuditParmsID.Column(4)
            rs!UpdatedBy = Forms!frmLogin!txtUserID
            rs.Update
            
        rs.Close
    End If
    
    'set properties of archive database to make seeing objects more difficult.  No db is secure.
    Call SetStartupProperties(ToFileName)
    
    'rename new archive to .accdr
    FromFileName = ToFileName
    ToFileName = Left(ToFileName, Len(ToFileName) - 1) & "r"
    'Make sure there isn't already a file with the name of the new database
    If Dir(ToFileName) <> "" Then Kill ToFileName
    Name FromFileName As ToFileName
  

    If Forms!frmLogin!cboAuditParmsID = 1 Then
        MsgBox "The archive was created but the data will not be deleted because it is needed for the correct operation of the DEA application.", vbOKOnly + vbInformation
    Else
        'Delete archived data from database
        DoCmd.OpenQuery "qDeleteArchivedAudit"
    End If
    
    MsgBox "Export Complete." & vbCr & " Your Archive is: " & vbCr & vbCr & ToFileName, vbOKOnly + vbInformation
    
    db.Close
    Set db = Nothing
Proc_Exit:
    Exit Sub
Proc_Err:
    Select Case Err.Number
        Case 53
            MsgBox "DEA audit template database could not be found.", vbOKOnly + vbInformation
        Case 3265
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Proc_Exit
    End Select
End Sub

Open in new window

0
 
Jeffrey CoachmanCommented:
My guess is that what Bill or Pat posted is your ticket here...

But for me, ...can you explain the need for this...?
An explanation may also inspire other Experts to consider other possible options/alternatives.

But If I am understanding your question, ...
I don't know of any other way to create a valid access database file without some version of Access.

So while you can certainly create a "file" (a simple text file), ...and give it an .mdb, or .accdb extension, ..it wont really be a true Access database file.

As you know Access files are binary, ...so unless you had a binary file editor, and you knew how to edit the file as to make it a legit "empty" database file, ...
Then edit it further to the level of creating Access objects inside of it..
...I don't see a way of doing this... (again, without some version of Access installed)
But, as with all things I post, ...I will yield to an expert who may have more insight into this...

Another option would be to simply create a blank database on a remote PC and simply "Copy" this database file, once, to the target PC...
Then simply "Copy" this reference database over and over again for each new database you needed.

Finally, (as you probably already know) you can open an Excel file directly into Access (File-->Open, ..and select .xls as the File type), ...and the "linking wizard" will initiate...

But I am still confused about the need for this...
(Is the DB to be empty, or to contain db objects)
With no version of Access installed, ...what is the purpose for having a database file created that cannot be opened or manipulated on the Host PC?
Again, ...just curious and this is an interesting Question

Thanks

Jeff
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

Using VBScript or non-Access VBA (Word, Excel, PowerPoint) can you actually create an entire mdb file whole-cloth?

 Certainly you can create a MDB file, but what you cannot create is forms, reports, and Macros (all of which are Access objects).  For that, you need Access.

But for the DB file, tables, and querydefs, that's all JET and can be done without Access.   Remember "Access" is really built on top of JET.

Jim.
0
 
Nick67Author Commented:
Again, ...just curious and this is an interesting Question
And that is a fair bit of it.  I can create an ungodly assortment of files with Access VBA (and do, .txt, .csv, .xls, ps1, .vbs, and via WIA you can do a fair bit of stuff to .bmp, .png and .jpg files, and there are good ways of pulling stuff off the clipboard and pounding it into an image file, too.)  I've been Answering and discussing a fair bit of esoteric stuff lately, and it occurred to me that I had never actually tried to create an mdb from absolute scratch.

Why would I want to?  With Snapshot gone, one export facility is to send a client a front-end and then periodically create and ship an updated backend.  And there's mail merges, where if you don't want anyone monkeying with the data, an mdb can be a good choice of data file.  Occasionally, I also kick out everything in SaveAsText and pull all the objects back in, fix the references in the tables and querydefs and get all the cruft out of the system tables.  But that involved manually creating a target, too.  So I was wondering if it could be done, and done from say Excel.

And given that some of the stuff I've been doing has been ripping open various textfiles as textstreams and processing them line by line (or Excel sheets row by row) it occurred to me to wonder whether I could code the entirety of such things without a dependence on Access itself, or copying some pre-existing file.

So, @PatHartman, your code is copying an existing file and therefore doesn't quite meet the test.
@als315, thank you for the scripting guys link, but they cheated up a batch and used an Access.Application object -- which would definitely fail if Access isn't installed in some form.

@Scott McDaniel posted something here
http://www.experts-exchange.com/Database/MS_Access/Q_28591156.html#a40533753
that I found startling.
It isn't necessary from Outlook VBA (or any VBA I guess) to have an Access.Application object on the go to get DAO access the tables in a mdb.  A reference to DAO 3.6 will be enough to get it done.  I've never used OpenDatabase from anywhere but within Access before.

@Bill Prew
I tried your code (adapted for my filesystem) but I get error 80040154 Class not registered.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< whether I could code the entirety of such things without a dependence on Access itself, or copying some pre-existing file.>>

 If you understood the internal structure of an Access object then you could.

<<It isn't necessary from Outlook VBA (or any VBA I guess) to have an Access.Application object on the go to get DAO access the tables in a mdb.  A reference to DAO 3.6 will be enough to get it done.  I've never used OpenDatabase from anywhere but within Access before.>>

 See my last comment.  CreateDatabase() in DAO works fine for creating a DB file.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW, you can call DAO from any number of languages; C++, VB, VBA, etc and work with DB files.  People have been doing that for a long time now.

But what you can't do is create any Access objects.

Jim.
0
 
Nick67Author Commented:
See my last comment.  CreateDatabase()
?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Prior comment was:  http://www.experts-exchange.com/Database/MS_Access/Q_28591169.html#a40533121

But I basically repeated what I had said in that comment again and then added that you can use CreateDatabase() in the DAO lib:

Function CreateDatabase(Name As String, Locale As String, [Option]) As Database

to create a DB file, which you can easily do from Excel, Outlook, C++, or anything that can set a reference to the DAO lib.

Jim.
0
 
PatHartmanCommented:
You never did tell us what you were creating a database for.  If you had said, I want to create a database that contains only tables and queries, you would get one answer - Yes, you can do it because it doesn't require Access.   It only requires Jet/ACE.  So you can do it from pretty much anywhere.

If you had said you wanted to create Access objects, you would get a different answer - No.  I showed you how I handle the problem in a live, production app.  The app is sold to the public and we use the Access runtime engine to run it so we do not have available the Access development environment.  That is why I went with a template.

I see that no one elected to build a working model for you.

I also update databases that are out in the wild by using another database to manipulate its objects.  The updates are done using DDL queries for the most part with a little VBA to control the process.   The FE is always replaced since it is distributed as an .accde and so its objects cannot be updated.  Only the BE can be updated.
0
 
Nick67Author Commented:
You never did tell us what you were creating a database for
And given that some of the stuff I've been doing has been ripping open various textfiles as textstreams and processing them line by line (or Excel sheets row by row) it occurred to me to wonder whether I could code the entirety of such things without a dependence on Access itself, or copying some pre-existing file.
I do a fair bit of creation of csv files that replace a dummy file that is linked in Access.
The tabledef then gets refreshed and it's off to the races.
Doing the same for an mdb file (creation and file operations)  should work as well  -- but to do it entirely from Excel on a machine without Access wasn't something I had tried.

just curious and this is an interesting Question
 And that is a fair bit of it.

VBScript is very much my friend and to execute a VBScript to do the same from some of the Windows Embedded-type units we have would also be interesting, and perhaps useful.

I see that no one elected to build a working model for you.
Very sad, that.
No matter.
I am working with what @Bill Prew had posted.
but it's not playing nice, and then there's that whole 32-bit ODBC vs. 64 ODBC driver fun
(I'm running Win7 64-bit with 32 bit Office)

All-in-all an interesting conundrum.
0
 
Nick67Author Commented:
Ah, a fun bit of legerdemain!
VBScript runs by default as 64 bit on a 64 bit system.
But that blows up the 32 bit providers needed to make it all go.
So, one needs to force the script to execute in 32 bit mode

The batch file
%WINDIR%\SYSWOW64\cmd.exe /c cscript c:\temp\test.vbs
%WINDIR%\SYSWOW64\cmd.exe /c cscript c:\temp\test2.vbs

Open in new window

@Bill Prew's suggestion made into a vbs file creates an accdb
Dim Catalog
Set Catalog = CreateObject("ADOX.Catalog")
dim strConn
strConn =  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\testADOX.accdb"
Catalog.Create strConn
set catalog = nothing
msgbox "Done!"

Open in new window

Flop it back to JET 4.0 though and we get an mdb
Dim Catalog
Set Catalog = CreateObject("ADOX.Catalog")
dim strConn
strConn =  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\testADOX.mdb"
Catalog.Create strConn
set catalog = nothing
msgbox "Done!"

Open in new window

And the legwork on Jim's suggestion is as follows
Dim Engine 
Set Engine = CreateObject("DAO.DBEngine.36")
Engine.CreateDatabase "C:\tempPDF\testDAO.mdb", ";LANGID=0x0409;CP=1252;COUNTRY=0"
set engine = nothing
msgbox "Done!"

Open in new window


So, it can get done without reference to anything installed by Access.

Thanks!

Nick67
0
 
als315Commented:
Nick: there are a lot of sample powershell scripts. Due to new EE rules, we can't put links to most sites with code, but you can find them yourself.
One sample (not sure, allowed it or not):
http://poshcode.org/?show=2928
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<One sample (not sure, allowed it or not):
http://poshcode.org/?show=2928>>

 It's allowed since it is not a Q&A Forum.  However with that said, the preference is to still see the material here, rather than a link to somewhere else.

Here by the way is the help page on the use of links:

http://support.experts-exchange.com/customer/portal/articles/1162518

and if you ever have questions, just ask.

Jim Dettman
MS Access Topic Adviser
0
 
Nick67Author Commented:
And then, while you're looking for other stuff, you can come across the answer laid out quite elegantly
http://support.microsoft.com/kb/283874
LOL
0
 
Nick67Author Commented:
My accepted comment lines out how to get things done in VBScript on a 64 bit platform without Access installed, which is what I wanted.  @Bill Prew suggestion for ADOX was great -- There are some things even now that you can only do with ADOX.  This doesn't happen to be one, but it was a nice reminder that I've poked that ADOX bear with a stick for a reason.  @als315 and @PatHartman's solutions both involved references to stuff that was out of bounds for what -- I -- wanted, but others coming later may find eminently useful.  @Jim Dettman's solution worked too once I worked out the syntax.

Thanks to all for your comments.

Nick67
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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