Get a Jumpstart with our Sample Apps
Results 1 to 6 of 6

Thread: Converting MS Access to Alpha and SQL

  1. #1
    Member
    Real Name
    Darren Parsons
    Join Date
    Aug 2015
    Posts
    146

    Default Converting MS Access to Alpha and SQL

    I hope this is the right place to post this.

    I am in the process of moving a full suite of management software from MS Access to Alpha. In the current Access environment we are running a back end MS Access DB on an internal server. In the new Alpha/SQL environment the data will reside on a ZebraHost SQL Server.

    This is a large project that will probably take about a year to convert completely to Alpha - so we're hoping to do it in stages. Our first task is to provide mobile access via Alpha to our field staff. I have written and deployed our fist app. We need to establish an environment where our office staff and field staff can share data on the SQL server with our office still using the MS Access Front End and our field staff using Alpha. To prepare for this I have started migrating our access backend to the SQL server. I have used the Microsoft SQL Migration Assistant for Access to create the tables on the server and migrate the data. The plan was then to link the new SQL files into the Access front end.

    The big issue we're having is DATE fields. When we export the data from Access to SQL and then link it back the dates are now in TEXT format. If I look into the field on the SQL server is says the format is datetime(2). Any suggestions on how I can fix this?

  2. #2
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,464

    Default Re: Converting MS Access to Alpha and SQL

    I don't think Access supports Datetime(2). I think if you modify the table to change them to just datetime, or date - if there is no time portion - it will work better when you try to open it in Access.

    I recommend you setup a test table with all of the different date/time formats to see which one(s) work best for you.

  3. #3
    Member
    Real Name
    Darren Parsons
    Join Date
    Aug 2015
    Posts
    146

    Default Re: Converting MS Access to Alpha and SQL

    Thanks - I did try and found that datetime works.

  4. #4
    Member
    Real Name
    Bruce Jonson
    Join Date
    May 2010
    Location
    New Zealand
    Posts
    376

    Default Re: Converting MS Access to Alpha and SQL

    Hi Darran,
    What tool are you using to convert your Access data to SQL?
    I have run into this exact issue when using the Microsoft SQL Server Migration Assistant (SSMA) for Access
    All you have to do is change the data conversion mappings to use DateTime not DateTime(2) Unless you are concerned about tracking time down to 2 nano seconds then DateTime will serve most people :)
    http://stackoverflow.com/questions/1...e2-vs-datetime
    I have done quite a few Access to SQL conversions, not all for conversion to Alpha, some just because we needed SQL as a backend.

    Bruce

  5. #5
    Member
    Real Name
    Darren Parsons
    Join Date
    Aug 2015
    Posts
    146

    Default Re: Converting MS Access to Alpha and SQL

    Bruce

    Thanks for the response. I did figure that out and made the change and it works well now. I do have some junk dates in my database that I am cleaning up, but my testing so far is very promising.

    Again thanks for taking the time to help out.

    Darren

  6. #6
    Member
    Real Name
    Wadim Korneev
    Join Date
    Dec 2016
    Posts
    4

    Default Re: Converting MS Access to Alpha and SQL

    while there is increased precision with datetime2, some clients doesn't support date, time, or datetime2 and force you to convert to a string literal. Specifically Microsoft mentions "down level" ODBC, OLE DB, JDBC, and SqlClient issues with these data types and has a chart showing how each can map the type.

    If value compatability over precision, use datetime

Similar Threads

  1. Converting MS Access 2007 database to Alpha Five Ver 9
    By chandran in forum Alpha Five Version 9 - Desktop Applications
    Replies: 15
    Last Post: 06-24-2008, 09:33 PM
  2. Replies: 0
    Last Post: 11-17-2006, 06:31 PM
  3. converting access files to alpha 5 v1
    By carie gray in forum Alpha Five Version 1
    Replies: 6
    Last Post: 06-27-2003, 07:33 AM
  4. Converting Alpha 5 v1 to Access 97
    By Hugh Francis in forum Alpha Five Versions 2 and 3
    Replies: 9
    Last Post: 06-16-2003, 06:33 PM
  5. Converting Alpha 5 v1 to Access 97
    By Hugh Francis in forum Alpha Five Version 1
    Replies: 1
    Last Post: 06-24-2000, 03:02 PM

Posting Permissions

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