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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
https://www.experts-exchange.com/questions/28591156/Send-Email-subject-line-to-MS-Access-table.html?anchorAnswerId=40533753#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.
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
https://www.experts-exchange.com/questions/28591156/Send-Email-subject-line-to-MS-Access-table.html?anchorAnswerId=40533753#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.
<< 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.
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.
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.
But what you can't do is create any Access objects.
Jim.
ASKER
See my last comment. CreateDatabase()
?
?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
One sample (not sure, allowed it or not):
http://poshcode.org/?show=2928
<<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
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
ASKER
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
http://support.microsoft.com/kb/283874
LOL
ASKER
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
Thanks to all for your comments.
Nick67
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