Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Access 2007 - Using INET_NTOA() from external DB

    I'm trying to set up a query in MS Access 2007 against an external data source (a MySQL database via ODBC).

    The query editor is barfing however when I use the MySQL function INET_NTOA(). It seems to think the function doesn't exist.

    When I make Access operate in Pass-Through mode, I get garbage in that column. I guess it's trying to convert the string data returned as an unsigned int?

    I imagine the INET_INTOA() function may not be presented via ODBC, but is there any other way to do this? I need to convert the data in that column to an IP address...

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're correct, ODBC probably doesn't recognize such a command as INET_NTOA() (or really it's MSAccess which doesn't recognize the command). Why not simply manipulate/convert the data using an expression (ie "Format" the field) in the MSAccess query or formatting of the field on the form. It won't be any command though like INET_NTOA but instead will be more like a Format([MyField],"###.###.###.###") or something like that in the query (as an expression) or as the Format (or inputmask) property of the field on the form.

    How you handle that in code (ie. when someone wants to go to that IP address), can be done in different ways depending on what you want to do (ie. you can use the "Shell" command or create a web object and pass it as a URL address, etc...) but simple manipulation of how the field is displayed should be done in the MSAccess query as an expression or using the Format or Inputmask property on the form.
    Last edited by pkstormy; 09-17-09 at 21:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    Sounds like a good idea. Disclaimer: I'm not an Access guys -- I was originally thinking I'd create some VIEWs in MySQL and present my data to Access that way.

    Could something similar be done with Excel? We're basically wanting to generate reports based on a lot of joined tables that higher level users can just fire up in Excel and have the data be pulled automagically from Excel.

    Thanks for the reply!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    hmm...been a long time since I've done any macro programming with excel.

    I might go the route of "linking" the excel sheet into MSAccess and then do some vba coding or design a form/report against the linked table.

    Keep in mind that VIEWS in MySQL can easily be replicated in a simple MSAccess query.

    I was designing VIEWS in SQL Server for a large (5 million recs) database but it was more difficult writing the vba code to access that view than it was to simply work with the tables linked into the mdb and using a query for the report/form.

    Linked tables will make your life much easier. For MySQL, you'll just have to setup the ODBC DSN to make the "connection" from the server to the mdb. Once you start working with the power of MSAccess queries against linked tables, you'll probably abandon designing views like I did.
    Last edited by pkstormy; 09-19-09 at 02:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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