I have several asp - vb script sites that will shortly need their databases upgraded to sql server due to large visitor numbers.

Whats involved in doing this?
do I just need to upsize the db to sql server and not need to ammend any code?

Also is their any tutorials on how you go about uploading an sql db to your server with connection setup etc - is it simialr to using access?

Ryan

for the most part, you wont have to change any code unless you have extremely complex queries, or deal with #dates# a lot.

I believe that there is an Access upsize wizard somewhere, but i've never used it. I always use DTS to import the access database objects. Then all you should have to do is change the connection string.

Other than that, you'll have to wing it. import the DB, change the string, and test test test.

Joel Martinez
MX inSite Magazine
The Complete Resource for Macromedia designers & developers
Sign up for your free electronic Preview Issue
www.mxinsite.com

I'm in the same boat. My server is no longer supporting Access databases and is upgrading all existing users to MySQL. I'm kinda dreading the whole idea, because it took me so long to create my UD pages for Access. I find it hard to believe that I won't have to re-write any code. What about queries, aren't they different in MySQL?

My server sent me an email today with the links below to help convert an Access database to a MySQL database:

This is an actual program:

www.dmsofttech.com/downloads.html

These are steps you can follow:

servers.digitaldaze.com/extensions/mysql/access.shtml

regardless of the different platform, the fact still remains that you are dealing with ansi92 SQL. This is a standard that all major database platforms conform to (except for minor differences).

The only things that mysql doesn't support is subqueries and stored procedures.

As I mentioned before, one of the only things that access sql differs from, is the use of dates, one must reference dates like this
select * from thetable where thedate = #1/1/02#

a regular database query would look like this
select * from thetable where thedate= '1/1/02'

the same goes for upgrading to sql server from access. aside from that, any differences are pretty small as long as your database structure remains the same.

I would love to hear how both of your stories end... please post to this thread once you complete the migration and let us know <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Joel Martinez
MX inSite Magazine
The Complete Resource for Macromedia designers & developers
Sign up for your free electronic Preview Issue
www.mxinsite.com

Hey! Have not tried this yet but this is from the latest UD learning manual by Tom Muck and Ray West which is very informative and explains the upsizing very well.

For Access 2000 and 2002 users just select Tools &gt; Database Utilities &gt; and Upsizing Wizard while in the database that you want to move.

Follow and answer the questions according to your criteria and make sure you have the proper permissions on your web host.

They have a few things to look out for to make it easier to perform this process. First just create the table structure and check the don't upsize any data option to make sure the table structure is created properly. They recommend this if you have a large database to move.

The wildcard in Access is an * whereas in SQL Server it is the % so code changes may need to be made.

For today's date Access uses Date() while SQL Server uses GetDate() which gives you both the current date and time.

The connections strings in your code will need to be changed to your new database location.

There are a few more points they have but the last and main one they make is to test fully after the upsizing is complete.

Would recommend you go to their site at www.basic-ultradev.com and order the book UltraDev and SQL Server: Next Steps for only 15.00 since it is well worth the money for all the information they have.

Hope this helps. Thanks!

Thanks for the feedback. I was reading a little bit more about the software that does the conversion and it say is does not convert table relationships or queries. This sounds like a big problem, because my Access database is based on Owen's tutorial which relies heavily on queries.

Any advice on this? I'm having a hard time getting my head around this whole conversion thing. <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

Andrew

Well I got started on the conversion tonight and so far I'm not too happy. Exporting the database went fine, and my search and results pages seem to be working all right, but my insert and update pages are not working.

I have an update page based on the "Multiple insert/update" from charon.co.uk. It has worked flawlessly with Access, but now it won't even load. This is the error I get:

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near 'select Category_ID FROM ContactCategories WHERE Contact_ID=1) ORDER BY Category' at line 1


/canphotodirect/update.asp, line 212 <hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Here is the code:

<pre id=code><font face=courier size=2 id=code>
set rsCategories = Server.CreateObject("ADODB.Recordset"
rsCategories.ActiveConnection = MM_canphoto_STRING
rsCategories.Source = "SELECT * FROM Categories where Category_ID not in (select Category_ID FROM ContactCategories WHERE Contact_ID=" + Replace(rsCategories__value, "'", "''" + " ORDER BY Category"
rsCategories.CursorType = 0
rsCategories.CursorLocation = 2
rsCategories.LockType = 3
rsCategories.Open()
rsCategories_numRows = 0
</font id=code></pre id=code>

My insert page inserts a record into my "Contacts" table, but the new record doesn't show up in the queries. My database is based on Owen Eastwick's tutorial, so it uses a "CombinedFields" and a "DisplayFields" query. I wonder if something has happened to my table relationships.

Another problem is ASP Upload doesn't work anymore. When I try and use it I get this error:

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> Server object error 'ASP 0177 : 800401f3'

Server.CreateObject Failed

/canphotodirect/images/upload.asp, line 148

Systemmessage, messageid = 0x800401f3
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Sorry for the long list of problems, but Joel did say he wanted to hear how things went. If anyone can help I will be very grateful.

Andrew

P.S. I also can't figure out this phpMyAdmin 2.2.6-rc1 that my server is using as the MySQL interface. Could it be anymore cryptic??<img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle>

Well Joel you wanted to know how things went and so far they are not good. As I said above, my database and main pages are based on Owen's tutorial (www.tdsf.co.uk/tdsfdemo/). I wrote to Owen in private today and he was kind enough to respond. This is what he said:

"I took a look at MySQL.com and did a search for
Stored Procedures and Stored Views, It would appear
that neither are supported in the current version of
MySQL... you might be better moving to a new host."

I also wrote to Julian Roberts because my update page is based on his "Multiple insert/update" and this is what he said about converting to MySQL:

"mySQL doesn't support subqueries. It'd be a bit
of a nightmare trying to get it to work with mySQL."

So now it seems I'm left with the equally nightmarish option of moving to a new server. <img src=../images/dmxzone/forum/icon_smile_dead.gif border=0 align=middle> This is very scary because, in addition to the database and asp pages, I also have an ikonboard discussion forum with over 300 members.

Excuse the language, but I'm really pissed about the whole thing!

It never occured to me that sub-queries wouldn't work either. I know it's free, but that kind of makes the list of things it does do quite short.

I did download and install MySQL once and have to admit that after mucking about with it for a couple of hours and getting nowhere I gave up.

Out of curiosity, who is your host?

It's pretty poor if they change their package to the extent that sites that could run on the old set-up cannot work on the new.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 20 Apr 2002 04:04:00

I'm in Canada and the site is hosted with www.blacksun.ca. They are hosting their Access databases on a separate NT partition, and say they will be phasing it out because, "It has proved to be a service that is less than 99% reliable". They also say that "Those who don't want to make the switch (to MySQL) will have the rest of their term with us on the Access database, but once the plan's term is up, the Access database will not be supported or offered."

I think it's pretty lousy service, and there are obviously lots of other hosts out there who offer Access databases without any reliability issues.

Any suggestions on how to proceed?

Thanks,

Andrew

Well, moving a site is not too painful, basically find a new host and upload the site. Then you will have to update the Domain Name Servers (DNS) to point to the new host. Sometimes you can do this through a control panel with the registrar, or you may have to send a fax confirming the change. When the DNS are updated your site will be up and running at the new host.

If you're looking for cheap hosting with lots of features you could try www.readyhosting.com 500Mb of space with a 50Mb SQL Server database is $125.00 US per year. I use them and they are OK, mainly it's the technical support that a bit lacking, but at that price it's not too surprising.

If you need something a bit better and want to shop around take a look at these:
www.hostsearch.com/
www.tophosts.com/
www.hostreviewer.com/index.php
www.webhostdir.com/

If you are moving and your site is starting to get a fair bit of traffic it may be worth considerng moving up to a SQL Server database. There are differences between using Access and SQL Server, but it has a visual interface and there are wizards to help with a lot of things and it doesn't take long to get to grips with, compared to MySQL it's a breeze. It's not free, however, if you rent a SQL Server database it's not particularly expensive. You can download or order a CD copy of the 120 day trial version from www.microsoft.com/sql/evaluation/trial/2000/default.asp, plenty of time to have a look and see if it's for you.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thanks for your feedback Owen. As always you've been very helpful. It doesn't sound like it's possible to convert to MySQL, so I think moving servers may be my only option. The other problem with that is Access and ASP run on NT servers and my Ikonboard runs best on Unix.

I have found a host here in Canada www.miditech.on.ca that claims "cross platform Unix/NT/Win2K hybrid hosting with 99.9% up time". They're more expensive than some of the options above, but it may be worth it.

Why can't anything be easy? <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

Andrew