CreateMDB will analyze and dump the sturture of an Access database into a BAS-module, you can include in your own project.
From your own project you can now create an access database on the fly.
This project is using ADO/ADOX v2.5 but will also work on v2.1.
Updates in v1.0.13 : Minor bug regarding Nullable columns fixed.
Updates in v1.0.12 : Minor bug regarding table description fixed, and found a solution regarding Nullable/Allow Zero lengh.
Updates in v1.0.11 : More tableproperties added and a MRU list added to menu.
Updates in v1.0.10 : Password dialog added, and some minor code updates.
Updates in v1.0.9 : Support for Queries aka Views and Procedures and improved Errorhandling.
Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come
out every day, so no prevention program can catch 100% of them. For your own safety, please:
Re-scan downloaded files using your personal virus checker before using it.
NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
Scan the source code with Minnow's Project Scanner
If you don't have a virus scanner, you can get one at many places on the net
including:McAfee.com
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
Very nice work you have done there! Actually is was looking some something like this only not for Access databases but for Oracle databases... 4 globes for you! (If this comment was disrespectful, please report it.)
Greate code. gave you and excellent. only there was a problem with the call to CAT.Create in Sub CreateMDB not spliting line correctly. other than that it works greate for access97. (If this comment was disrespectful, please report it.)
can some one help me, seems as if i an having problems with ADOX, cant find the reference needed to create the DB and im not sure what it is i need exactly..
thanx Daniel (If this comment was disrespectful, please report it.)
DANIEL:Select PROJECT, REFERENCES, then put a checkbox next to Microsoft ADO...
Author: I also had a problem with the .BAS file it created. This ADO reference, but also in the CAT line.. It placed ...Password=";" Where it should have used Password=;" in addition to the missing "& _" at the end of the last two CAT lines.
(If this comment was disrespectful, please report it.)
Also, the CreateKeys sub only creates the first key... the rest fail with Err.number 3265 (after attempting to remove the key if it already exists).. err.desciption="Item cannot be found in the collection corresponding to the requested name or ordinal."
(If this comment was disrespectful, please report it.)
Hi everybody, thanks for trying out my program - I knew there could be some bugs in the code and I have fixed the split line error reported by 2 of you. Chat Kovac, is it possible to mail the MDB file you have discovered the error '3265' - I would like to see why you have the problem, I have some MDB's where the tables some 2 or more key and worked nicely.
regards, Niels Krogh (If this comment was disrespectful, please report it.)
Is it possible to create AutoNumber field, i have field in my access that is autonumber fields and the program created it as a Number filed. Other than that it's greate program (If this comment was disrespectful, please report it.)
When I try to run your code I get this error. The MSCOMCTL.OCX file appears to be registered ok Can you give me some help? See your code below where the error occurs.
Private Sub LV_LoadDATABASE(ByVal Node As MSComctlLib.Node) (If this comment was disrespectful, please report it.)
After a lot of researc I found the proble. Thank Heavens for the internet searc capability. The problem was that my comcat.dll file was corrupted. Thanks for your kind assistance.
(If this comment was disrespectful, please report it.)
Is there anyway you can import all queries too from the database. That would make this application realy awesome (If this comment was disrespectful, please report it.)
To Afshadd. I will check it in nearest fureture, and then you will hear from me. regards, Niels K. (If this comment was disrespectful, please report it.)
This application ROCKS !! i hardly vote on PlanetSourceCode but i think i have never seen such a professional and yet powerful application out in the market that can do this. I give it 5 globes (If this comment was disrespectful, please report it.)
I have to report a problem that I had and the solution for it. My MDB file has tables with autonumber fields, and the .bas file created would not create these in the new MDB. I had to set the parent catalog in order to make it work.
SAMPLE from CreateTables - with fix (line 3) ' ===[Create Table 'AuditLogs']=== Set TBL = New ADOX.Table Set TBL.ParentCatalog = CAT 'fix
I am also having trouble with UniqueKeys. The code encounters a STOP in CreateKeys. What should really happen here?
Keep up the great work! Brett W. (If this comment was disrespectful, please report it.)
Excellent! I also enjoyed (and imported into my code library) the simple splitter implementation. I will probably rewrite the "Create" portion to save SQL statements. (Thinking that I can then reverse engineer an Access database and rebuild it in SQL Server or ORACLE.
Again, Fine Code, Sir!!! (If this comment was disrespectful, please report it.)
I'm having the same problem as Daniel; can't find a reference to ADOX. I've included ADO 2.1 and 2.6, neither worked for me. Help? (If this comment was disrespectful, please report it.)
I guess your code does not work with ADOX 2.6? I remember with previous was ok, now it just doesnt work. (If this comment was disrespectful, please report it.)
Hi there, I've just checked CreatedMDB with ADO/ADOX 2.7 but found no problems. I know you've tried ADOX 2.6 - I only have v2.5 and v2.7 on my machines, so I can't test with v2.6. Have you tried to create a new project with ADOX 2.6, and then added som come using the ADOX objects ? - It seems that you ADOX isn't installed properly.
Regards Niels K. (If this comment was disrespectful, please report it.)
i'm having a problem with this code. the sub CreateTables bombs on the first call of CAT.Tables.Append TBL. Where am I going wrong? Generated code follows: --- begin snippet --- Private Sub CreateTables() On Error GoTo ErrTrap Dim TBL As ADOX.Table Set TBL = New ADOX.Table
' ===[Create Table 'Categories']=== Set TBL = New ADOX.Table TBL.Name = (If this comment was disrespectful, please report it.)
Hi Adam, I've just test your code snippet in a project of my own - couldn't see any errors. Do you see an error code/description, when you try to use the code ? - and which version of MDAC are you using ?? - If you mail me the BAS-module I could dig into it if you like.
Regards, Niels K. (If this comment was disrespectful, please report it.)
I am having the same problem as Adam Murry reported. I have MDAC6 the error is: Item cannot be found in the collection corresponding to the requested name or ordinal. Err.num 3265
(If this comment was disrespectful, please report it.)
Was cut off.. 'TBL.Columns("ContactName").Properties("Jet OLEDB:Allow Zero Length") = 1’(Err. 3265) Is there another way to set the ‘Allow Zero Length’ property? (Access 2000)
(If this comment was disrespectful, please report it.)
Normally you have to set the property to True, but due some German and French version of VB, the keyword True is replaced bye a numeric value of 1 (one).
The code 1’ and 1&H8216, is that generated by CreateMDB ? (I hope not...)
Recently I found out that there is a bug in ADO, when using the .Attributes property - see link http://support.microsoft.com/support/kb/articles/Q272/0/01.ASP for more information.
Regards Niels Krogh (If this comment was disrespectful, please report it.)
I (a newbie to ADO...Taking a VB Class, and this caught my eye.) attempted to run your application on an existing Access 97 file, and I got a popup msgbox with "Can't open DB : e:\lab5\employees.mdb"
Does not create a normal error, and wonder if there is something I failed to do. Checked, file is not locked. Not in use by any other app. No errors under PROJECT|REFERENCES .. Basically, I am stymied.
Would appreciate any suggestions. (If this comment was disrespectful, please report it.)
Excellent timesaving program. I am trying to set the "Required" property without any success. I think it might have something to do with "Jet OLEDB:Column Validation Rule", but I cannot find and examples of how to use it. Any ideas of how to read and set the Access "Required" property? (If this comment was disrespectful, please report it.)
Great code, a real masterpiece! I am trying it and I will report you any bugs I find.
BTW Would you please let me know how long it took you to create this code?
Bug: when creating the code, the description is missing a "" this is the fix: Print #fHandle, " TBL.Columns(""" & Col.name & """).Properties(""Description"") = """ & Col.Properties("Description").Value & """" (If this comment was disrespectful, please report it.)
This is a fantastic program! Thank you so much for providing this to the open source community! This has saved me many many hours of digging through MSDN's-less-than-helpful help. 10^10 globes for you! (If this comment was disrespectful, please report it.)
Tried this straight out of the box, so to speak. Generates all the code required, but when I try to invoke the generated "CreateMDB" code I get the following error and it creates the database with no tables. "-2147217887 / Multiple step OLE DB operation generated errors. Check each OLE DB status value if available. No work was done" Have tried a single table with just two text fields and a boolean field. Any suggestions
(If this comment was disrespectful, please report it.)
This is great code but I would like to make a change but I can't seem to make it work. How do you create a text field that is not required and allows zero length strings? I can get one or the other but not both. Any help would be appreciated. (If this comment was disrespectful, please report it.)
Hi. Im sorry to say I've encountered the same problem as Greg Boot. Any soloution/eplonation near? (If this comment was disrespectful, please report it.)
Hi. Doesn't seem like the Nullable columns has been fixed like you stated. This might be why: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q272001& Also it seems like the "default" propertie gets double double quotes ;-) like so: "" "" If you manage to solve this please let me know. (If this comment was disrespectful, please report it.)
I have been using this code happyily on mdac2.5 but when I installed sql server 2000 it updated my mdac to 2.6 the code now fails on the CAT.Tables.Append TBL with error (If this comment was disrespectful, please report it.)
Well, there are few fatal errors on code. And one big improvement must be done. Errors: TBL.Columns("Notes").Properties("NullAble") = True -- will lead to error like stated in previous post. Property "Seed" must not be set too.
The improvement required - do not create one long procedure. On a quite big database it will lead to procedure bigger then 65Kb and as result - you'll have no chance to debug it, VB will state "Procedure is too big" and refuse even to run. And when the compiled EXE will give you an error, will be hard to determine where it occured.
So I give this code 5 globes, because all you need is to cut off some useless code and change the main procedure.
By the way, dont know how for others, for me it is much easier that CreateMDB procedure would be a function, taking as parameter the fully qualified filename, and return TRUE on success.
Have a good time. (If this comment was disrespectful, please report it.)
-2147217887 / Multiple-step OLE DB operation generated errors.
Any solutions ?
tks,rg
(If this comment was disrespectful, please report it.)
5/20/2003 5:09:50 AM:
I recently tried out your codes, it is great! I appreciate your hardwork!
But here I was having a little problem with AutoIncrement column, and have found the fix to it:
For AutoIncrement column,
Properties( (If this comment was disrespectful, please report it.)
6/24/2003 10:33:11 AM:
This code is excellent! I give you 5!
It's a great utility. (If this comment was disrespectful, please report it.)
6/24/2003 10:36:40 AM:
The only thing that I have to change is a bit of code to replace True and False values because in Spanish versions of Access, that values are written as "Verdadero" and "Falso" and when running app, it show and variable not defined error. (If this comment was disrespectful, please report it.)
10/3/2003 5:24:59 PM:
Great program, however I read through the previous posts and see that others have had similar problems but I don't see solutions... any ideas? At this line (If this comment was disrespectful, please report it.)
10/3/2003 5:27:07 PM:
Message was cut off! This line... CAT.Tables.Append TBL This Erro... Multiple-step OLE DB operation generated errors (If this comment was disrespectful, please report it.)
1/3/2004 4:18:53 PM:
We I launch the creat.bas procedure (the create.bas was generated perfectly) I have an error : "-2147217887 : a multiple steps OLE DB operation ...". Do you know what it could be ? (If this comment was disrespectful, please report it.)
1/8/2004 4:54:46 AM:
This made my crazy to resolve it ... In sub "Writetable", for an autoincrement column, the original code is:
There are other minor bug but anyway ... useful code
Alberto Italy (If this comment was disrespectful, please report it.)
4/5/2004 12:26:23 AM:
I'm a complete newbie when it comes to working with databases, so please be gentle. ;) I had no problems creating the .bas file, but that's as far as I can get. I just tried to test it out by creating a dummy application with the bas, and a 1 button form. The code for the button is simply:
Private Sub Command1_Click() CreateMDB App.Path End Sub
but when i try to run the application i get a "User-defined type not defined" error on:
Private CAT As ADOX.Catalog
I've checked the references... tried using Microsoft ActiveX Data Objects 2.1 Library... 2.5, 2.6. and 2.7. Same error each time... what am I doing wrong? Thanks in advance.
VB 6.0 Ent. SP 5 (If this comment was disrespectful, please report it.)
4/5/2004 12:28:16 AM:
Oh, and I'm using version 1.00.0013 - July 2002... j.i.c. it matters. (If this comment was disrespectful, please report it.)
I think most of the errors generated here results from the application's assignment of the autoincrement property.
Error occurs when you use createMDB with a database having tables with autonumber fields.
A possible fix would be, with the author's permission, is to change the part of the code that generates autoincrement setting. The current coding sets it to -1. Try changing it to True instead and i found that it worked fine. But undeniably this is a great program! Nice job and 5 globes. Hope my idea helped those with problems here. (If this comment was disrespectful, please report it.)
I am using the latest ado ( 2.8 ) and when i execute the created code it blows up. So i read the feedback area here and tried the fic in there (2/23/2006) but it still blows up. Can you help me out? I can send you a copy of the app (uses infragistics 2005 controls) thanks (If this comment was disrespectful, please report it.)
To stop the blowing up of the code using ado2.8 remove the Properties("Seed") lines for the tables. (If this comment was disrespectful, please report it.)
For those with error (-2147217887) Please look up this page for the solution. Thanks to Niels for sharing with us this program.
http://www.siteexperts.com/forums/viewConverse.asp?d_id=18022 (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 80 Users
Download code
4/2/2001 10:09:56 AM: 
