How different is MSSQL to MSAccess?

Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: How different is MSSQL to MSAccess?

  1. #1
    Join Date
    Dec 1969
    Posts
    511

    Default How different is MSSQL to MSAccess?

    So far I&#039ve only worked with access when codin my asp pages. I want to move up to MSSQL server though, how much different is it from Access? Is it quick to pick up? Does my ASP code need to be change in any way?<BR><BR>Pete

  2. #2
    Join Date
    Dec 1969
    Posts
    134

    Default RE: How different is MSSQL to MSAccess?

    That depends how your ASP code is written. It should be an easy transformation from Access to SQL, especially if all your Access database activity has been through the use of SQL strings, like:<BR><BR>SELECT * FROM Table1 WHERE something = somethingelse<BR><BR>

  3. #3
    Brad Murray Guest

    Default RE: How different is MSSQL to MSAccess?

    Moving from Access to SQL is rarely painless. Make sure you setup your AutoNumber fields after you have moved your data. That doesn&#039t get picked up in the transformation untility. Also, dates don&#039t work the same syntactically. Access uses the #MM/DD/YYYY# format, and SQL uses something else.

  4. #4
    Jeff Woodie Guest

    Default RE: How different is MSSQL to MSAccess?

    My experience was generally pretty good. The previous poster is correct to say that it didn&#039t always pick up my autonumber fields and convert them to identity fields which is the SQL server equivalent. But other than that, it was pretty painless for me. We were using tables with very standardized field names (ie. no spaces, etc.) and I didn&#039t have to change much of my SQL syntax at all. The "Delete * from table" is changed to "Delete From Table" in SQL, but that was about the only syntax thing I found, and we run about 60 different data-driven websites. <BR><BR>Once I got the hang of the upsizing wizard (after just a few attempts) I was upsizing my sites at a rate of 1 every 20-30 minutes, with an average of about 1 DB per site. It took about 2 days total to transfer everything, so I was pretty happy with that. And before this experience, I had NEVER used SQL Server at all (this was 6.5 about a year ago..)<BR><BR>Just my .02<BR><BR>Jeff Woodie


  5. #5
    Join Date
    Dec 1969
    Posts
    14

    Default RE: How different is MSSQL to MSAccess?

    What about dates? I remember in 6.5 I had to rewrite all my #date# (for access) to &#039date&#039 (for sql server). Does 7.0 now support # symbol for dates?<BR>

  6. #6
    Shawn Clabough Guest

    Default RE: How different is MSSQL to MSAccess?

    No, you will still need to change the syntax for dates.

  7. #7
    Eric Kaufman Guest

    Default RE: How different is MSSQL to MSAccess?

    Without responding to the technical differences, which are really not that big of a deal providing you&#039re on ADO 2.5. In fact, here&#039s the simple version...<BR><BR>&#060;%<BR><BR>Dim objConn<BR>Set objConn = Server.CreateObject(ADODB.Connection")<BR>objConn. ConnectionString = "Provider=SQLOLEDB; Data Source =VOODOO; Initial Catalog=Dust; User ID=Webboy; password=Webboy"<BR>%&#062;<BR><BR>MS Sql is a hugely scalable solution. It&#039s like comparing apples to elephants. No one makes a living as an access guru (maybe a few), but there are tons of people who do noting but sit on a sql box. <BR><BR>You&#039ll love the change!

  8. #8
    Darwen Guest

    Default RE: How different is MSSQL to MSAccess?

    will the following asp be changed when moved from access to SQL server7.0??<BR><BR> set Boardtitle=server.createobject("adodb.recordset")< BR> Boardsql="select user.username,user.useremail,boardname,boardimg from board left join user on user.userid=board.boardmaster where board.boardID=cstr(boardID)"<BR> Boardtitle.open Boardsql,myconn<BR><BR>

  9. #9
    Frank Venegas Guest

    Default RE: How different is MSSQL to MSAccess?

    There will be a few differences:<BR><BR>Like statement delimiters (% vs. *), format() functions, in fact any function call that is VSScript will not work.<BR><BR>You may have to learn a bit about client-side vs. server-side cursors.<BR><BR>If you work with memo fields that you would like to update, you will have to chunk them into pieces.<BR><BR><BR>

  10. #10
    Join Date
    Dec 1969
    Posts
    730

    Default RE: How different is MSSQL to MSAccess?

    Beware that some the functions inherent in the Access version of the SQL language may not exist in the Version of SQL that SQL server uses. <BR>For instance the Access SQL function <BR>`instr([fieldname,"string")` <BR>will not work in SQL Server

Posting Permissions

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