Solved

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

Posted on 2015-01-05
17
1,203 Views
Last Modified: 2015-01-11
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
Comment
Question by:Nick67
  • 6
  • 5
  • 2
  • +3
17 Comments
 
LVL 51

Assisted Solution

by:Bill Prew
Bill Prew earned 300 total points
ID: 40532698
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 40532827
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40532857
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
 
LVL 39

Assisted Solution

by:als315
als315 earned 50 total points
ID: 40533050
0
 
LVL 57
ID: 40533121

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
 
LVL 26

Author Comment

by:Nick67
ID: 40534246
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
 
LVL 57
ID: 40534291
<< 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
 
LVL 57
ID: 40534294
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Author Comment

by:Nick67
ID: 40534310
See my last comment.  CreateDatabase()
?
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 40534333
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40534386
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
 
LVL 26

Author Comment

by:Nick67
ID: 40534553
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
ID: 40534696
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
 
LVL 39

Expert Comment

by:als315
ID: 40535061
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
 
LVL 57
ID: 40535431
<<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
 
LVL 26

Author Comment

by:Nick67
ID: 40539338
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
 
LVL 26

Author Closing Comment

by:Nick67
ID: 40542936
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now