5 Replies - 1767 Views - Last Post: 15 April 2010 - 06:28 PM Rate Topic: -----

#1 bosha10  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 14-April 10

User creating a table in MS Access

Posted 14 April 2010 - 02:20 PM

Hey, I'm trying to create a button that will allow the user of my system to create their own table within my database. The title of this database needs to be variable depending on what's entered into the text box on the form that the button is on and there needs to be 2 columns; First Name, Last Name and Date of Birth. This is what I have so far but it doesn't work, I would appreciate any help thanks :)

Private Sub Command4_Click()

    Dim dbs As Database

    Set dbs = OpenDatabase("C:\Users\David\Documents\My Dropbox\ICT Coursework - David Jones\The Explorers Database.accdb")

    dbs.Execute "CREATE TABLE "Me.Text0" " _
        & "(First Name CHAR, Last Name CHAR, " _
        & "Date Of Birth DATETIME, " _
        & "CONSTRAINT "Me.Text0" Constraint UNIQUE "
        & "(First Name, Last Name, Date Of Birth));"

    dbs.Close

End Sub


Thanks again

Is This A Good Question/Topic? 0
  • +

Replies To: User creating a table in MS Access

#2 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: User creating a table in MS Access

Posted 14 April 2010 - 06:04 PM

To create a table in access with dao, you will find that it is a whole lot more complicated than that... Also I believe your string is improperly formatted.

To find the code you need to use, start a new project and add a reference to dao 2.5. Declare your variables and press F1 and follow the links to createdatabase and createtable.

You may also find some examples on the web with your friends (yahoo, google, ask, answers, bing) but I'm betting you will find more ADO examples than DAO...



Good Luck
Was This Post Helpful? 0
  • +
  • -

#3 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: User creating a table in MS Access

Posted 15 April 2010 - 02:30 AM

error is

 dbs.Execute "CREATE TABLE " & Me.Text0.Text &" " _
        & "(First Name CHAR, Last Name CHAR, " _
        & "Date Of Birth DATETIME, " _
        & "CONSTRAINT "& Me.Text0.Text &" Constraint UNIQUE "
        & "(First Name, Last Name, Date Of Birth));"



try to use the tabledef object in Dao this will help to create a table at run time easily
Was This Post Helpful? 0
  • +
  • -

#4 bosha10  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 14-April 10

Re: User creating a table in MS Access

Posted 15 April 2010 - 07:23 AM

I tried the code with the error corrections you gave me however I got the message Compile Error: Syntax Error. Any ideas why this might be? http://grab.by/3MiK

Thanks again :)
Was This Post Helpful? 0
  • +
  • -

#5 bosha10  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 14-April 10

Re: User creating a table in MS Access

Posted 15 April 2010 - 07:55 AM

With help from 'my friend' Google, I have changed the code to the following, however I am getting a runtime error every time the code tries to run now http://grab.by/3MlG
This is the code

Private Sub Command4_Click()
    Dim dbTheExplorersDatabase As DAO.Database
    Dim tblEvents As DAO.TableDef
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
    Dim fldDateofBirth As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEvents = dbExercise.CreateTableDef("&Me.Text0.Text&")

    Set fldFirstName = tblEvents.CreateField("First Name", DB_TEXT)
    tblEvents.Fields.Append fldFirstName

    Set fldLastName = tblEvents.CreateField("Last Name", DB_TEXT)
    tblEvents.Fields.Append fldLastName
    
    Set fldDateofBirth = tblEvents.CreateField("Date of Birth", DB_TEXT)
    tblEvents.Fields.Append fldDateofBirth

   ' Add the new table to the database.
   dbTheExplorersDatabase.TableDefs.Append tblEvents
   dbTheExplorersDatabase.Close
End Sub


Again all hope is greatly appreciated :)
Was This Post Helpful? 0
  • +
  • -

#6 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: User creating a table in MS Access

Posted 15 April 2010 - 06:28 PM

things to change your code are little bit
    Dim dbTheExplorersDatabase As DAO.Database
    Dim tblEvents As DAO.TableDef
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
    Dim fldDateofBirth As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = OpenDatabase("E:\Microsoft Visual Studio\VB98\BIBLIO.MDB")

    If Len(Me.Text1.Text) = 0 Then
        MsgBox "please enter the name of the table"
        Exit Sub
    End If
    ' Create a new TableDef object.
    Set tblEvents = dbExercise.CreateTableDef(Me.Text1.Text)
    

    Set fldFirstName = tblEvents.CreateField("First Name", dbText)
    tblEvents.Fields.Append fldFirstName

    Set fldLastName = tblEvents.CreateField("Last Name", dbText)
    tblEvents.Fields.Append fldLastName
    
    Set fldDateofBirth = tblEvents.CreateField("Date of Birth", dbText)
    tblEvents.Fields.Append fldDateofBirth

   ' Add the new table to the database.
   '***************************************
   dbExercise.TableDefs.Append tblEvents
   dbExercise.Close
   '***************************************


here i have locate my BIBLIO database instead of that path use it yours

This post has been edited by thava: 15 April 2010 - 06:30 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1