Advice Regarding DB Differences

[ldiuf]

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

[allida77]

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.

[ReyN]

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.

[ReyN]

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.

[ldiuf]

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.

[ReyN]

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.

[ldiuf]

Sounds Like I'm going to have an interesting time.

So whith the SQL compared to my DSN what I have is this...

'--- 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...

	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?

con = "driver={SQL Server};server=SERVERNAME;uid=USERNAME;pwd=PASSWORD;database=DATABASENAME"

then in the page it's just the same??

Thanks,
Larry

[ReyN]

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