'm currently running the Access version of 6.34, and i want to upgrade to v7 in SQL. so i got a couple questions (prob repeats but it's the start of a new era...so to speak)

1. should i get the current WWForum v7 in SQL Server, or wait til the final one, and when will the final one be out (best guesstimate)?

2. is there a script or sumthin to convert my Access db to the SQL server db? if not it is possible to write my own...right...?

3. which is better MySQL or MSSQL? (I have access to both)

4. what are good db managers for either?

ok that's all. maybe i should have posted them seperatley...

just to let you know if you wanted to use MySQL you would have to rewrite must of the code cause MSSQL and MySQL are completly different.

As for an upgrade script im not sure anyone has made one for access to sql yet.

the code to create the tables is different? or the code to access the DB is different? or is it something mroe than that?

Ill probably stick with MSSQL then.

as for the upgrade script, i'm guessing all i'd have to do is read in the data from the Access DB, and write that to the corresponding table in the SQL Server DB. sounds simple enough...in theory...any one see any probs with that?

He's wrong. You wouldn't have to rewrite most of the code at all.

There are a few minor changes in the SQL syntax used between the two (such as TOP and LIMIT)

Additionally, MySQL doesn't support Stored Procedures (yet) so you would have to port the queries used in the Access version.

Apart from that, a connection string or two, and that's pretty much it!

As for un upgrade script, thats a different process altogether. You would want to ensure the correct indexes are maintained, equivalent data-types, keys etc. all must be functional.

There are certain existing Wizards / Routines that can do this, however there always seems to be a couple of changes that aren't correctly made.

Good luck!

Cheers,
Andrew

Bunce,

Ive been moding the forum for almost a year now. I have tryed to convert the forum to MySQL, you would have to rewrite ALL the SQL in the forum, even ask borg.

It doesn't really bother me what you've done. No offence, but I've browsed your own personal site and some of your 'articles' haven't given me absolute faith in your ability ( http://www.maddogs-asp.com/forum/display_topic_threads.asp?ForumID=26&TopicID=434&ReturnPage=&PagePosition=1&ThreadPage=2 - http://www.maddogs-asp.com/forum/display_topic_threads.asp?ForumID=26&TopicID=434&ReturnPage=&PagePosition=1&ThreadPage=2 )

Yes the SQL code needs to be altered for syntax that is not compatible wth both. I'm a DBA and have worked on both database systems and am aware of the differences.

Your comment above was:
"if you wanted to use MySQL you would have to rewrite must of the code cause MSSQL and MySQL are completly different."

..this is just plain wrong.  Firstly its primarily only the SQL statements, and secondly, they're not completely different database systems at all.

Sorry if that sounded harsh but I don't believe in people posting overarching comments like these that aren't correct.

Cheers,
Andrew

In answer to the original questions:

1) Wait until the final version of V7 is out. (Its still in Beta)

2) I believe there is (was) another thread on a mod being used for a database upgrade. Someone else may be able to help on that one.

3) MS SQL is more robust and has more features. However MySQL is free / open source and quite powerful given its source.

4) MSSQL has Enterprise Manager which comes with purchase, however there are a number of other (free) utilities out there that can be used. Additionally, Access 2000+ can be used to administer a MS SQL database through an ADP (Access Data Project)

MySQL has a couple of admin tools. Check out their site http://www.mysql.com - http://www.mysql.com for the latest.

Cheers,
Andrew

I have been working on a mySQL version and so far probally spent around 6 hours and still not got anywhere with it.

There are quite a few problems, including, changing field names as mySQL doesn't like many feild names, mySQL doesn't have a true/false or bit datatype, can't create relationships between tables, these are just a few of the problems I have incounted so far and thats before starting on changing any of the SQL queries, as version 7 includes things like sub quiries which are not supported by mySQL and also stored procedures are also not supported.

Just a random FYI.

MySQL 5 (which isn't out yet) will be able to handle stored procs and triggers.

But still MySQL isn't as good as MSSQL, The problem is, that i can't work with access db's since it's very slow, and now i gotta find a new host, which will be affordable, fast,and with SQL 2000 support...
I can't find one which is inexpensive....

I found another big problem with converting the forum to use mySQL, it seems that the mySQL/ODBC driver has big problems with ADO updates, instead you have to use SQL insert statement then execute it.

99% of the data in the forum is infact using ADO to update the database, this would mean that all these would have to be changed to make the forum compatible with mySQL.

-boRg- wrote: I found another big problem with converting the forum to use mySQL, it seems that the mySQL/ODBC driver has big problems with ADO updates, instead you have to use SQL insert statement then execute it. 99% of the data in the forum is infact using ADO to update the database, this would mean that all these would have to be changed to make the forum compatible with mySQL.

Yeah, i also met this problem in the past...
You DO have to use SQL now, This sucks..
If you want, please contact me and i'll help you with the ADO -> SQL converting...
Please PM me if you wantme to...

When using ADO it's much more difficult to use malcious code with SQL injections, also ADO can handle single quote marks ' which when using SQL would have to be turned into two quote marks to escape, otherwise the SQL statement would crash.

I think mySQL will have to be forgotten about at the moment, as to make the forum compatible almost every page needs to be re-written, not just ADO updates, but also mySQL can't handle sub quiries, theres no bit or true/false datatype, you can't create raltionships between tables, you can't use TOP # to limit the number or returned records, no record paging, and these are just a few of the problems I have encountered so far.

Just another simple look on this would be:  This forum is using ASP, which obviously runs on IIS only (ChiliSoft ASP is a pointless nightmare so don't bring that one up...   ).  So it would make perfect since to keep it a MSSQL site or access.  If it were written in PHP or perl or something to that nature, it would make sense to write it for MySQL.  Probably most of the people who download and use this forum are not going to have a need for a MySQL version.  Just the few people who have the MS/Linux mix setup could see a need for this.  Just my thoughts. 

You'd think after this HUGE argument someone would mention DSN.  If you can use DSN for Access, you can use it for mySQL.  Use an Access to SQL converter to dump the table structure and setup myODBC.  Admittedly DSN is slower than a direct connect, but at least you can use mySQL.

This is an ASP board though... using mySQL goes beyond me.  if you need mySQL, go use PHP (which sucks by the way).  I've some to dislike mySQL after years of use...

I have already written a script that will create the database tables and structure on mySQL, but it's not as simple as that as many, many pages would have to be re-written.

If a mySQL server version is made it might have to be a compltly different code base as it would be a shame to lose performance and security in other versions just to accomodate mySQL.

Wait a bit for a newer version of MySQL Borg. I'm getting back into at work shortly so will have a stab at converting the SQL with you if you like Bruce.  The Sub-Queries are gonna be the killer!

I wish everyone would stop this ASP-SQL Server versus PHP-MySQL cr*p.  One is a web server, the othe other is a database. The aim of web applications should be a multi-tier approach which attempts to keep each tier independant of the other. 

Yes, there are issues with the data layer between these two data sources, however it doesn't meet it shouldn't be attempted. And especially not because of some religion which says PHP should go with MySQL or ASP should go with SQL Server.

Again, Bruce if you would like some help with the SQL conversion let me know. I've got to study for my MCDBA exam the next few weeks but after that I'm free.

Cheers,
Andrew

OK, i started using MSSQL and am using MS Access to manage it using ADP. is there anything about using ADP to manage a MSSQL DB that i should be wary about? and i'm thinking i can use this to convert my database into MSSQL, witout losing any information.

Also i was wondering if there was anychance of Borg releasing some sort of ADP file or something to create the tables and procedures using ADP? just wdonering...

gr8indianbear wrote: OK, i started using MSSQL and am using MS Access to manage it using ADP. is there anything about using ADP to manage a MSSQL DB that i should be wary about? and i'm thinking i can use this to convert my database into MSSQL, witout losing any information.

Well, if you are using an ADP then the data you are working with is already stored in a SQL Server database.  An ADP does not store data locally - ie. it doesn't store data in the ADP file. It's just a container for SQL Server objects (plus the forms / reports / modules).

Access is fine to administer your SQL database, although it doesn't allow access to the more advanced managemental utilities such as jobs / dts etc.

Good luck!
Andrew

Str8Dog wrote: Borg, A simple solution to this would be a different architecture for the app. Maybe for version 8.... If you took a object oriented approach to data access it might simplify things a bit. Creating a data access object and using the methods of that object to retreve your recordsets for you would allow you to build your app on top of that object independant of the database it was using. That way everyone who wants to use MySQL can write a new data access object and plug it into the app. Seems like a pain in the ass, but in the end it makes the app more flexible.. To get a idea if what I mean, check out diggersolutions.com's Intranet Open Source. There is one file with all the sql calls. Very easy for someone to port that.

I wouldn't say *simple* , but yes it would be an ideal methedology.

ASP does allow classes however they're a bit limited. If you were going to create a data access layer using components, which would involve rearranging/rewriting quite a bit of the code, then I'd take the plunge and do it proprely in .NET

That said, I guess we could make a couple of include files with the DB-specific queries and do it that way..

Cheers,
Andrew