dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Create Table SQL in Access

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Create Table SQL in Access

    [Originally posted by Thaha]

    Will it work in MS-Access?

    CREATE TABLE tempCustomer as
    SELECT DISTINCT *
    FROM customer

    If so what is the possible alternative?

    Thanx.

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Create Table SQL in Access

    [Originally posted by Jim]

    Create a new table
    SELECT * INTO tempCustomer FROM customer

    Insert into an existing table
    INSERT INTO tempCustomer SELECT * FROM customer

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Create Table SQL in Access

    [Originally posted by Mahdieh]

    'First create a table in Ms Access by the name of
    '"customer" in the path you copy program and
    'define fields in every type you want.fill it.
    'This program will copy your table in new one
    '(tempcustomer) by clicking "cmdCREATETABLE"
    'then copy the content of "customer" by omitting
    'dublicate records by clicking "cmdCOPYFILD"
    'button.
    'I will be happy to learn more

    Public DB1 As DAO.Database
    Public TBL1 As DAO.TableDef
    Public TBL2 As DAO.TableDef
    Public FLD1 As DAO.Field
    Public RS1 As DAO.Recordset
    Public RS2 As DAO.Recordset

    Private Sub cmdCREATETABLE_Click(Index As Integer)
    Dim str As String
    On Error GoTo errhandler
    Set DB1 = DBEngine.OpenDatabase(App.Path & "\dbCUSTOMER.mdb")
    Set TBL1 = DB1.TableDefs("customer")
    Set TBL2 = DB1.CreateTableDef("tempcustomer")
    For i = 0 To TBL1.Fields.Count - 1
    Set FLD1 = TBL2.CreateField(TBL1.Fields(i).Name, TBL1.Fields(i).Type, TBL1.Fields(i).Size)
    TBL2.Fields.Append FLD1
    Next
    DB1.TableDefs.Append TBL2
    DB1.Close
    Exit Sub
    errhandler:
    If Err.Number = 3010 Then
    str = MsgBox(" 'Tempcustomer' Table exist", vbCritical)
    End If
    Exit Sub
    End Sub

    Private Sub cmdCOPYFILD_Click(Index As Integer)
    Set DB1 = DBEngine.OpenDatabase(App.Path & "\dbCUSTOMER.mdb")
    Set RS1 = DB1.OpenRecordset("SELECT DISTINCT* FROM customer", dbOpenDynaset)
    Set RS2 = DB1.OpenRecordset("tempcustomer", dbOpenDynaset)
    While Not RS1.EOF
    RS2.AddNew
    For i = 0 To RS1.Fields.Count - 1
    RS2.Fields(i).Value = RS1.Fields(i).Value
    Next
    RS2.Update
    RS1.MoveNext
    Wend
    RS1.Close
    RS2.Close
    DB1.Close
    End Sub

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Create Table SQL in Access

    [Originally posted by Thaha]

    That's great. Thanx. But the first statement fetches data along with the operation. Why?

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Create Table SQL in Access

    [Originally posted by Mike Lyons]

    >>But the first statement fetches data along with the operation. Why? <<

    I think he was just turning around your original SQL you posted which also selected data.

    Mike

  6. #6
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Create Table SQL in Access

    [Originally posted by Jim]

    I was assuming you wanted to move data from one table into a new table. Sort of an adhoc archiving.

    You could just add a criteria that returns no data.

    eg WHERE true = false

  7. #7
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Create Table SQL in Access

    [Originally posted by Thaha]

    Simply Great!

  8. #8
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Create Table SQL in Access

    [Originally posted by Thaha]

    Jim,

    Seems you are an expert in Access SQL. Can you suggest me a query where only duplicate records are fetched?

    Thnx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center