Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Change data type in make table query

    Hi,

    I have a few make table query's to gather data from another database, but I need some of the fields to be in a different data type.

    Is it possible to change the data type within the query, before it creates the new table?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you take a look at the SQL behind the "make table" query?
    Attached Thumbnails Attached Thumbnails SQLView.JPG  
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Well it just looks like a select query to me.

    Code:
    SELECT QUERCUS_PERSON.ID_NUMBER AS StudentRef,
    QUERCUS_COURSE_INSTANCE.INSTANCE_CODE AS CourseRef INTO DLinkCourseLink IN 'O:\MIS\spirALS Import Database\imports.mdb'
    FROM QUERCUS_MODE_OF_STUDY
    INNER JOIN (QUERCUS_PERSON INNER JOIN ((QUERCUS_COURSE
    INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_INSTANCE.COURSE)
    INNER JOIN QUERCUS_STUDENT_COURSE_DETAIL ON QUERCUS_COURSE_INSTANCE.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE)
    ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON)
    ON QUERCUS_MODE_OF_STUDY.OBJECT_ID = QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY
    WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=2007))
    ORDER BY QUERCUS_COURSE_INSTANCE.INSTANCE_CODE;
    <- Hides behind a rock.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That is a SELECT INTO statement...

    I've never personally used "make table" queries.
    Instead I use the VBA command DoCmd.RunSQL along with CREATE TABLE syntax.
    Code:
    Dim SQL As String
    
    SQL = ""
    SQL = SQL & "CREATE TABLE gvExample ("
    SQL = SQL & "    Field1 int"
    SQL = SQL & "  , Field2 datetime"
    SQL = SQL & "  )"
    
    DoCmd.RunSQL SQL
    Anyhow, why don't you explain a bit more about your situation and the code you are using and we'll see what we can do to solve your problem
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Hmm, I suppose I could do it in VBA, done it in the past.

    I work for a college and one of our systems requires me to get student details from our Oracle database, and store it in an access db.

    All I did was create the queries and add the criteria that I needed. Then changed them to 'make table' queries, and told the query to create the tables in a new database.
    I created a form with a textbox and button, you click the button and it runs the queries.

    Code:
    Private Sub cmdStart_Click()
        DoCmd.SetWarnings False
        
        DoCmd.OpenQuery "qryDLinkStu"
        Me.txtProgress.SetFocus
        Me.txtProgress.Text = "Created DLinkStu Table..."
        DoCmd.OpenQuery "qryDLinkCourses"
        Me.txtProgress.SetFocus
        Me.txtProgress.Text = Me.txtProgress.Text & vbCrLf & "Created DLinkCourses Table..."
        DoCmd.OpenQuery "qryDLinkStaff"
        Me.txtProgress.SetFocus
        Me.txtProgress.Text = Me.txtProgress.Text & vbCrLf & "Created DLinkStaff Table..."
        DoCmd.OpenQuery "qryDLinkCourseLink"
        Me.txtProgress.SetFocus
        Me.txtProgress.Text = Me.txtProgress.Text & vbCrLf & "Created DLinkCourseLink Table..."
        Me.txtProgress.Text = Me.txtProgress.Text & vbCrLf & vbCrLf & "spirALS Import Database Created!!!"
        
        DoCmd.SetWarnings True
    End Sub
    Simple db, but today I was asked to change the key fields in some of the tables from number to text.

    If I can't do it through the 'make table' queries, then I'll just have to do it through VBA like you said.
    <- Hides behind a rock.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you changing it from numeric to text?
    I wonder if you could make the db do itself by Convert() ing the datatype in the SQL...
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, when did Access add the Convert() function? i can't find it...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    JET supports Convert().

    Go to the help section in the VBA editor > Microsoft JET SQL Reference > Overview > ODBC Scalar Functions
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, that's my problem, no vba editor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2005
    Posts
    240
    I tried but it comes back with the error:

    Undefined function 'CONVERT' in expression.
    Quote Originally Posted by georgev
    Why are you changing it from numeric to text?
    I need to change it because I have to send the db off to an external company, who then convert it into a file that the system can read to import the data.

    VBA it is then.
    <- Hides behind a rock.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can export numbers in a flat file...
    I fail to see why this is a problem.
    George
    Home | Blog

Posting Permissions

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