Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Advice Regarding DB Differences

    I currently use an Access 2000 DB on a NT4.0 Server for an intranet website that I currently have and it's gotten to where the DB is quite large and is getting alot of Client Task Errors from DB Accessing. I am thinking of going to a MS SQL Server 7 to resolve this. The problem is that I know nothing, at this point, about it and I just finally gotten the hang of coding for Access DB and now it seems that I may have to give that up.

    I've considered splitting the DB into multiple DBs but those will too grow and sooner or later I can forsee having to do the change anyway. Can someone out there give me some solid advise or even opinions on this.

    Thanks,
    Larry

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    MS SQL Server 7 is pretty expensive but you will not have any more problems if you upgrade. MS SQL has an option that allows you to import a Access DB. So the actual amount of time it will take to migrate will be very small. Some people may say to go with mySQL but in a buisness environment I think it is to much of a learning curve and not enough support since it is open source.

  • #3
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    and I just finally gotten the hang of coding for Access DB and now it seems that I may have to give that up.
    not really, if you meant coding in ASP for Access DB, which is not much different when working with SQL Server data.

    Except for some differences in SQL statements (Access uses ANSI-SQL whereas SQL Server uses a slightly different version of ANSI- as well as Transact-SQL) and of course your connection string, the rest is pretty much the same.

  • #4
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    and btw . . . one main difference is on the positive side. With SQL Server, you can do most common tasks, such as selects and updates, via stored procedures, which lessens code redundancy, and more importantly, is definitely highly more efficient, throughput-wise.

  • #5
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I like the inport that was mentioned, That was one big fear that I had, but I suppose not anymore. How much more different is the connection strings? So I'd be going through all my pages that has them and change those too. Currently I have my db connecting through a DSN connection.

  • #6
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    basically the connection strings are:

    for Microsoft Access:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=physical path to .mdb file

    for SQL Server:

    Provider=SQLOLEDB.1;Data Source=path to database on server

    and, depending on your security settings, you may have to pass userid= and pwd= parameters as well.

    This is another difference, though at the db management level, not really on the Web development side. SQL Server offers more security features, also meaning more settings to mess up if not done right. With SQL db you can restrict users' access just to certain tables, or even columns, or even certain tasks such as selects, inserts, updates, create table, modify table, etc.

  • #7
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sounds Like I'm going to have an interesting time.

    So whith the SQL compared to my DSN what I have is this...
    Code:
    '--- establish access connection
    Sub OpenDB (ByRef con, d)
    	DSN ="MasterDB_DSN"
    	Set con = Server.CreateObject("ADODB.Connection")
    	con.Open DSN
    End Sub
    then in the page I do this...
    Code:
    	OpenDB con, "User"
    	   sSql = "Select * FROM tblUsers WHERE User_Id = '" & Request.Cookies("ForumNum")("UsersID") & "'"
    	   set rs = con.Execute(sSQL)
    How would that look for the SQL 7?
    Code:
    con = "driver={SQL Server};server=SERVERNAME;uid=USERNAME;pwd=PASSWORD;database=DATABASENAME"
    then in the page it's just the same??

    Thanks,
    Larry

  • #8
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes, for the most part, you'd just do the same for each page. you'd have to make the changes in the dsn file, though. and note that :

    strConn = "Driver={SQL Server};Server=path to server ..."

    is ODBC and is provided only for backward compatibility. For MS SQL Server 7 and later, use the OLEDB provider instead.

    strConn = "Provider=SQLOLEDB.1;Data Source=path to database on server "

    it's getting late here in my country now. i'll probably be back tomorrow


  •  

    Posting Permissions

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