Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Programatically create Access db and table

    i have an application that is looking for database that contains information required to run the app. If this database does not exist I want to prompt the user for some info and create the db. How would I create a database in a given directory? Assume the directory is "C:\myDir". The fields are: myfield1 and myfield2. Myfield 1 is the PK and myfield2 has a default value of "default".

  2. #2
    Join Date
    Feb 2005
    Posts
    333
    Here's the first part

    Code:
    Dim ws As Workspace
            Dim db As Database
            Dim LFilename As String
            
            'Get default Workspace
            Set ws = DBEngine.Workspaces(0)
    
            'Path and file name for new mdb file
            LFilename = "c:\myDB.mdb"
            
            'Make sure there isn't already a file with the name of the new database
            If Dir(LFilename) <> "" Then Kill LFilename
    
            'Create a new mdb file
            Set db = ws.CreateDatabase(LFilename, dbLangGeneral)

  3. #3
    Join Date
    Feb 2005
    Posts
    333
    Ok here's the entire thing
    Dim ws As Workspace
    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim LFilename As String

    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)

    'Path and file name for new mdb file
    LFilename = "c:\myDB.mdb"

    'Make sure there isn't already a file with the name of the new database
    If Dir(LFilename) <> "" Then Kill LFilename

    'Create a new mdb file
    Set db = ws.CreateDatabase(LFilename, dbLangGeneral)

    Set tbl = db.CreateTableDef("myTable")

    Set fld = tbl.CreateField("myField1", dbText, 8)
    tbl.Fields.Append fld

    Set fld = tbl.CreateField("myField2", dbText, 100)
    fld.DefaultValue = "default"
    tbl.Fields.Append fld

    Set idx = tbl.CreateIndex("PrimaryKey")
    Set fld = idx.CreateField("myField1", dbLong)
    idx.Fields.Append fld
    idx.Primary = True
    tbl.Indexes.Append idx

    db.TableDefs.Append tbl

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by campster
    Ok here's the entire thing
    Hey Campster,

    I was just wondering, where do you go to learn how to write code as such you just did and know where everything goes? It still amazes me how you and others seem to just pop it out there on the fly. Are there any really good books that actually teach you step-by-step? Or did you go to school? Just a curious mind wanting to know.

    BUD

  5. #5
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    hi Bud,

    well personally i think : Inside Out, author J.L. Viescas, ISBN 07356-1513-6
    is a good book to start with. It explains a lot, with a explorable Dbase sample.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    judging by the style and the syntax the variable names I'd guess the code is taken from a book or web source - it looks almost "too" neat to be hand developed code, but I'm very happy to be proved wrong.


    Personally I reckon its difficult to beat the Access Developer Books (Getwin, Letz et al) - comes with a hefty price tag around 50 but from what I have learned from that worth every penny and more (but please don't tell the publishers that in case they jack up the price yet again).
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2005
    Posts
    50
    Personally, i PREFER to write with well-formed, neat code. It helps in trouble-shooting, transitioning code to others and let's others that see your code know that you have a handle on what you are doing.

    I've written Code Conventions documentation and try to follow the standards I researched.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Code to clone a db with the user's login name

    You can try this code out. Use notepad and save it as a *.vbs. Modify it as you need to.

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing

    LUName = "\\SQLServer\Databases\Databases\TimeSheet2003\Hou rs2003.mde"
    'Call Shell(LUName, 1)
    'LUName = GetLocation(11, False)

    oldname = LUName
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'DoCmd.OpenForm "ProgressBar", acNormal, , , acFormReadOnly, acWindowNormal
    'Forms("ProgressBar").SetFocus

    'wscript.echo oldname, newname

    retval = objFSO.CopyFile(oldname, newName, True)

    'DoCmd.Close acForm, "ProgressBar"

    Set objFSO = Nothing

    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")

    'objShell.Run "MSAccess.exe " & newName & " /compact", 1, True
    objShell.Run "MSAccess.exe " & newName, 1

    Set objShell = Nothing

    'Access.SetOption "Auto Compact", True
    'Call Shell("MSAccess.exe " & newName & " /compact", 1)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Mary
    judging by the style and the syntax the variable names I'd guess the code is taken from a book or web source - it looks almost "too" neat to be hand developed code, but I'm very happy to be proved wrong.
    Quote Originally Posted by mlbuie
    I've written Code Conventions documentation and try to follow the standards I researched.
    You need to bear in mind that Mary likes to call error traps things like "Oh_Crap" and other wonderfully eccentric stuff. Also - anyone else noticed Rudy's recent Ali G naming convention (daTable etc)?

    I'm always on the look out for cleaner code guides. Bugger everyone else - I want to be able to read my own code one year on.

    Would you be prepared to post your docs (or at least sources and references)? Not for tearing apart - just for info I am more than happy to see if I can dig up my stuff if anyone wants.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    You need to bear in mind that Mary likes to call error traps things like "Oh_Crap" and other wonderfully eccentric stuff. Also - anyone else noticed Rudy's recent Ali G naming convention (daTable etc)?

    I'm always on the look out for cleaner code guides. Bugger everyone else - I want to be able to read my own code one year on.

    Would you be prepared to post your docs (or at least sources and references)? Not for tearing apart - just for info I am more than happy to see if I can dig up my stuff if anyone wants.
    thats a vicious roumour, some people believe error trapping is for wimps (but i'm not one of them).

    mind you the suggesttion to put up your coding style & naming conventions is a good one, it could be a contender for Teddy's Access FAQ thread.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2005
    Posts
    333
    judging by the style and the syntax the variable names I'd guess the code is taken from a book or web source - it looks almost "too" neat to be hand developed code, but I'm very happy to be proved wrong
    I get a little credit. I wrote everything that's not commented. One day I'll learn to comment stuff so that it makes sense a week later.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by healdem
    judging by the style and the syntax the variable names I'd guess the code is taken from a book or web source - it looks almost "too" neat to be hand developed code, but I'm very happy to be proved wrong.


    Personally I reckon its difficult to beat the Access Developer Books (Getwin, Letz et al) - comes with a hefty price tag around 50 but from what I have learned from that worth every penny and more (but please don't tell the publishers that in case they jack up the price yet again).
    Healdem,

    I do have that volume but didn't actually find it being like a real tutorial. But believe me though, it has helped me a lot on many things and I do promote that volume to all others as well. Guess I just need a bit more hand holding at times. Especially when I see peeps spit out what seems like a whole page of code. I can't figure out what goes where and when and why. Guess it just comes with time. The most code I have written which was very simple in fact was only about 12-15 lines, but hey it did work. I noticed how also Paul wrote some nice looking (to me) code as well.....go Stormy. Guess I'll practice more. And thanks to you too Emiel, I will check into that.
    Oh, btw, I did buy the Vol. 1&2 by Getz, Litwin and all for only $65.00 brand spanking new from: www.bookpool.com
    you all have a great day,
    BUD

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Often, at least for myself, if I see something I have already done, I'll open that particular project, strip out the specifics and then copy and paste.

    I found this the other day, back on your topic, that folks may find helpful. It's "old school" because it uses DAO, but it teaches the concepts. It's not mine and I do not know who to give credit to for it, but here it is:

    http://www.atchoo.org/vb/simpledb.php

  14. #14
    Join Date
    Jul 2005
    Posts
    50
    Quote Originally Posted by pootle flump
    ...
    Would you be prepared to post your docs (or at least sources and references)? Not for tearing apart - just for info I am more than happy to see if I can dig up my stuff if anyone wants.

    Sure ... I'll dig it up from home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •