Data types when using Access with MySQL

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • zaphod
    #1

    Data types when using Access with MySQL

    I want to use MS Accesss as a front end for an existing MySQL database.
    Since Acceess doesn't have the same data types, eg. ENUM, am I tied to the
    smaller subset of types supported by Access? I'm assuming I have to
    duplicate the MySQL schema within Access then link the resulting tables.

    Garry
    Tags: None
    • zaphod
      #2
      Re: Data types when using Access with MySQL

      zaphod wrote:
      [color=blue]
      > I want to use MS Accesss as a front end for an existing MySQL database.
      > Since Acceess doesn't have the same data types, eg. ENUM, am I tied to the
      > smaller subset of types supported by Access? I'm assuming I have to
      > duplicate the MySQL schema within Access then link the resulting tables.
      >
      > Garry[/color]

      Further to my question, if I wish to execute SQL queries from within Access
      am I constrained by the subset of SQL supported by Access?

      Garry

        Comment

        • Thomas Bartkus
          #3
          Re: Data types when using Access with MySQL


          "zaphod" <none@none.co m> wrote in message
          news:42f612bf$0 $91536$ed2e19e4 @ptn-nntp-reader04.plus.n et...[color=blue]
          > zaphod wrote:
          >[color=green]
          > > I want to use MS Accesss as a front end for an existing MySQL database.
          > > Since Acceess doesn't have the same data types, eg. ENUM, am I tied to[/color][/color]
          the[color=blue][color=green]
          > > smaller subset of types supported by Access? I'm assuming I have to
          > > duplicate the MySQL schema within Access then link the resulting tables.
          > >
          > > Garry[/color][/color]

          Since we ise MS Office heavily in front of a MySQL/Linux server, we tend to
          shy away from incompatible data types that have no Microsoft equivalent.
          E.G. you *might* be able to use something like BIGINT, but there will be
          gotchas so we just practice avoidance. I *think* enums will come across (to
          Office) as a simple text field.

          Given the caveat about incompatible types (and why would you choose to use
          them anyway?), you generally don't worry about duplicating MySQL schemas
          within Access. Just use the MySQL ODBC driver and create linked tables. The
          ODBC driver will do the translating. The only potential nuisance is when
          you change the underlying (linked) table. In this case you *must* use the
          Access linked table manager to update the link or subtle errors can creep in
          without warning.

          [color=blue]
          > Further to my question, if I wish to execute SQL queries from within[/color]
          Access[color=blue]
          > am I constrained by the subset of SQL supported by Access?[/color]

          Not if you use a Pass Through query! You can elect to define an Access
          query as a "Pass Through" query in which case the SQL statement you enter
          will go directly to MySQL to be parsed by the server. This, of course, can
          be anything MySQL understands. Very cool!

          Thomas Bartkus


            Comment

            • Thomas Bartkus
              #4
              Re: Data types when using Access with MySQL


              "zaphod" <none@none.co m> wrote in message
              news:42f612bf$0 $91536$ed2e19e4 @ptn-nntp-reader04.plus.n et...[color=blue]
              > zaphod wrote:
              >[color=green]
              > > I want to use MS Accesss as a front end for an existing MySQL database.
              > > Since Acceess doesn't have the same data types, eg. ENUM, am I tied to[/color][/color]
              the[color=blue][color=green]
              > > smaller subset of types supported by Access? I'm assuming I have to
              > > duplicate the MySQL schema within Access then link the resulting tables.
              > >
              > > Garry[/color][/color]

              Since we ise MS Office heavily in front of a MySQL/Linux server, we tend to
              shy away from incompatible data types that have no Microsoft equivalent.
              E.G. you *might* be able to use something like BIGINT, but there will be
              gotchas so we just practice avoidance. I *think* enums will come across (to
              Office) as a simple text field.

              Given the caveat about incompatible types (and why would you choose to use
              them anyway?), you generally don't worry about duplicating MySQL schemas
              within Access. Just use the MySQL ODBC driver and create linked tables. The
              ODBC driver will do the translating. The only potential nuisance is when
              you change the underlying (linked) table. In this case you *must* use the
              Access linked table manager to update the link or subtle errors can creep in
              without warning.

              [color=blue]
              > Further to my question, if I wish to execute SQL queries from within[/color]
              Access[color=blue]
              > am I constrained by the subset of SQL supported by Access?[/color]

              Not if you use a Pass Through query! You can elect to define an Access
              query as a "Pass Through" query in which case the SQL statement you enter
              will go directly to MySQL to be parsed by the server. This, of course, can
              be anything MySQL understands. Very cool!

              Thomas Bartkus


                Comment

                Previous template Next
                Working...