#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11

    Difference Access - SQL Server


    Hi,

    i have a (probably very dumb) question:
    I have an Access DB connected to an IIS via ODBC, and use ASP pages to get info to/from the DB.
    My guys in IT now want to migrate the Access DB to SQL Server. Can anybody tell me what this implies in terms of
    a) syntax of my ASP pages
    b) syntax of my SQL statements
    c) other problems I may not have thought of.

    I am told it is really easy, however, I just cannot get is to work.
    BTW, I have activated the "SQL Syntax" option in Access, in case that helps any.

    Any comments are well appreciated!!!

    Thank you.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0
    There are no dumb questions ... I'm going through this myself with 9 databases. Some are easy. Used the Access 2002 Upsizing Wizard to convert the DB, changed the ASP Connection String to SQL Server and voila! (I have MSDE 2000 without Enterprise Manager, so data transfer is sometimes problematic.)

    About half the databases have more difficult conversion issues. The Upsizing Wizard doesn't properly create the SQL Server tables, ASP code to insert dates needs to be changed ...

    What exactly are you having trouble with? Is the SQL DB created? Trouble connecting? Problems inserting data? If I can help, I will.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11

    Further info


    Hi,

    the problem I have is that my IT guys told me "no problem" when they said they want to transfer the DB.
    The first problem arose when I tried to connect to the DB - the ODBC changed, so the Conn.Open wouldn´t work any more.
    Also, the one time I got around that, it seemed to have a problem with my SQL strings.
    The problem is not migrating the DB, that they managed rather well and I think the DB is ok. It is not big anyway.
    The problems are - besides the one above:
    1. Authentification: MY access DB did not have it (no need), but the SQL thingy does, and no one can tell me how to re-write the statements so that it includes User and PW.
    2. Any other changes in how I handle the ODBC connection. I use the .Execute quite often, i.e. Always, actually.
    3. Changes in the SQL statements.

    Any help is appreciated!

    If you would like code samples, let me know.

    Thanks, guys, thanks.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0
    Here's an excellent source for connection strings (you should consider using OLEDB instead of ODBC).

    http://www.able-consulting.com/ADO_Conn.htm

    I assume you're using SQL authentication?

    ODBC with DSN:

    oConn.Open "DSN=mySystemDSN;" & _
    "Uid=myUsername;" & _
    "Pwd=myPassword"

    ODBC without DSN:

    oConn.Open "Driver={SQL Server};" & _
    "Server=MyServerName;" & _
    "Database=myDatabaseName;" & _
    "Uid=myUsername;" & _
    "Pwd=myPassword"

    If the SQL Server is on the same box as your web site, use "Server=(local);"

    As far as SQL statements go, I'm still fixing my own code, so I can't offer too much advice. I do know that SQL Server handles dates differently than Access. If you're using # when inserting a date into an Access field, replace it with an apostrophe.

    Access:

    objCommand.CommandText = "UPDATE TableName SET DateField = #" & Request.Form("Date") & "# WHERE ID = '" & intRecordID & "'"
    objCommand.Execute

    SQL Server: change # to apostrophe symbol

    I'm still tracking down other necessary changes in my own code...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11

    Thank you


    Sorry, I was buried in other work the last days....

    Thanks for all your help! I am going to attack the problem tomorrow with the SQL server guy.
    So far the thing still runs in Access (and runs well, so I do not hope we are solving a problem we do not really have...)


    Merry Christmas and a happy new year!

IMN logo majestic logo threadwatch logo seochat tools logo