Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: CREATE TABLE - statement

    I need to create a new table in a temporary DB.
    This table should contain 2 fields (ID (autonummeric), Name (string)).
    ID is Primary key.

    I want this done by using VBA.


    I have 2 questions :
    1) How to declare a autonummeric field?
    2) How create a table in an other DB?


    Can anyone help me on this?
    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: CREATE TABLE - statement

    Originally posted by artemide
    I need to create a new table in a temporary DB.
    This table should contain 2 fields (ID (autonummeric), Name (string)).
    ID is Primary key.

    I want this done by using VBA.


    I have 2 questions :
    1) How to declare a autonummeric field?
    2) How create a table in an other DB?


    Can anyone help me on this?
    Thanks
    Set dbs = "C:\data\yourDB.mdb"

      Set tdf = dbs.CreateTableDef("yournewTable")
      ' Neues Feld in der Tabelle definieren.

      Set fld = tdf.CreateField("Kontaktperson", dbText, 40)
      tdf.Fields.Append fld
      tdf.Fields.Refresh ' TableDef-Objekt an TableDefs-
    Auflistung der Datenbank anfügen.
      dbs.TableDefs.Append tdf
      dbs.TableDefs.Refresh
      Set dbs = Nothing
    End Sub

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: CREATE TABLE - statement

    Originally posted by hammbakka
    Set dbs = "C:\data\yourDB.mdb"

      Set tdf = dbs.CreateTableDef("yournewTable")
      ' Neues Feld in der Tabelle definieren.

      Set fld = tdf.CreateField("Kontaktperson", dbText, 40)
      tdf.Fields.Append fld
      tdf.Fields.Refresh ' TableDef-Objekt an TableDefs-
    Auflistung der Datenbank anfügen.
      dbs.TableDefs.Append tdf
      dbs.TableDefs.Refresh
      Set dbs = Nothing
    End Sub
    I tried following :

    Code:
    Private Sub Form_Load()
        Dim DB As database
        Dim tabel As tableDef
        Dim veld As Field
    
        Set DB = "C:\OpleidingsDB\personal.mdb"
    
        Set tabel = DB.CreateTableDef("tblLISTBOX_BeschikbareVelden")
        
        With tabel
            .Fields.Append .CreateField("ID", DB_AUTOINCRFIELD)
            .Fields.Append .CreateField("Veldnaam", dbText, 50)
        End With
        
        tabel.Fields.Refresh
        
        DB.TableDefs.Append tabel
        DB.TableDefs.Refresh
      
        Set DB = Nothing
        
    End Sub
    But it gives an error on the line :
      Set DB = modConstants.PERSONAL_DB
    It says the types does not match.

    So I tried this :
      Set DB = OpenDatabase("C:\opleidingsDB\Personal.mdb")
    but it gave me an unrecognized database format.

    What do I need to get it working?


    Thanks
    Last edited by artemide; 12-01-03 at 06:05.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Question Re: CREATE TABLE - statement

    Originally posted by artemide
    I tried following :

    Code:
    Private Sub Form_Load()
        Dim DB As database
        Dim tabel As tableDef
        Dim veld As Field
    
        Set DB = "C:\OpleidingsDB\personal.mdb"
    
        Set tabel = DB.CreateTableDef("tblLISTBOX_BeschikbareVelden")
        
        With tabel
            .Fields.Append .CreateField("ID", DB_AUTOINCRFIELD)
            .Fields.Append .CreateField("Veldnaam", dbText, 50)
        End With
        
        tabel.Fields.Refresh
        
        DB.TableDefs.Append tabel
        DB.TableDefs.Refresh
      
        Set DB = Nothing
        
    End Sub
    But it gives an error on the line :
      Set DB = modConstants.PERSONAL_DB
    It says the types does not match.

    So I tried this :
      Set DB = OpenDatabase("C:\opleidingsDB\Personal.mdb")
    but it gave me an unrecognized database format.

    What do I need to get it working?


    Thanks
    Do both Access mdb files have the same version, I mean Access97 or 2000?
    You should obtain the message error Unrecognized database format when you try to open Access 2000 file from Access 97 but not vice versa
    I don't know

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: CREATE TABLE - statement

    Originally posted by hammbakka
    Do both Access mdb files have the same version, I mean Access97 or 2000?
    You should obtain the message error Unrecognized database format when you try to open Access 2000 file from Access 97 but not vice versa
    I don't know
    Both the same.

    Is there an other workarround for this?

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: CREATE TABLE - statement

    Originally posted by artemide
    Both the same.

    Is there an other workarround for this?
    Sorry I don't know why does this message come, did you try to open your c:\OpleidingsDB\Personal.mdb just by double clicking it? Do u get the same message?

  7. #7
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: CREATE TABLE - statement

    Originally posted by hammbakka
    Sorry I don't know why does this message come, did you try to open your c:\OpleidingsDB\Personal.mdb just by double clicking it? Do u get the same message?
    No messages.

    At the moment I'm trying this one :

    Set DB = DBEngine.Workspaces(0).OpenDatabase("C:\Opleidings DB\Personal.mdb")

    I don't know what the result will be, but it's worth a try.

  8. #8
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: CREATE TABLE - statement

    Originally posted by artemide
    No messages.

    At the moment I'm trying this one :

    Set DB = DBEngine.Workspaces(0).OpenDatabase("C:\Opleidings DB\Personal.mdb")

    I don't know what the result will be, but it's worth a try.
    THIS WORKS !
    THANKS FOR YOU REACTION.

Posting Permissions

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