Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    42

    Question Unanswered: Create table, fields with SQL

    How do I Create a table with an autonumber field in an external database using SQL?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE TABLE Table1
    ( Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY
    , MyText TEXT (10)
    )

    ACC2000: Create and Drop Tables and Relationships Using SQL DDL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out the below link (and the other two articles it references) for more in depth details. Some data types, for example, can only be created using combination of SQL and ADO.

    http://msdn.microsoft.com/library/de...l/acintsql.asp

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2002
    Posts
    42
    Thanks I'll read and give them a go.

  5. #5
    Join Date
    Dec 2002
    Posts
    42

    Unhappy Creating table and fields in external database

    I couldn't get the following to work.

    I have an external database test1.mdb to which I want to add a table and then some fields.

    When I run the following I get an error that reads "Query must have at least one destination field"


    Dim db As Database
    Dim qdf As QueryDef
    Dim strsql As String

    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\temp\test1 .mdb")
    Set qdf = db.CreateQueryDef("", strsql)

    strsql = "CREATE TABLE Tbltest ( Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, MyText TEXT (10))"
    qdf.Execute (dbFailOnError)

    db.Close

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Skip the querydef. Since you are creating a temp one anyway it is unnecessary,

    Code:
    Dim db As Database
    Dim strsql As String
     
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\temp\test1.mdb")
     
    strsql = "CREATE TABLE Tbltest (Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, MyText TEXT (10))"
     
    db.Execute strsql, dbFailOnError
     
    db.Close
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2002
    Posts
    42
    Thanks it works fine.

Posting Permissions

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