[RESOLVED] program conversion from Access to SQL Server2000

I have developed a vb6 application which works off of an Access database. Originally it was to be an interim solution but I believe the powers that be want to make it a more permanent solution. This being the case, I need to get this program working off of a SQL Server database before it grows too far out of hand as an Access database.

My question: What would be involved in migrating an application based on Access over to be one based on SQL Server? Will the connection string change? Will I need to rewrite the SQL for my select/update/insert/delete queries?

I've never developed anything that used SQL Server before so this is all new to me, any advice or insight from anyone who has been down this road before would be greatly appreciated.

Many thanks!

Quote:

---

Originally Posted by ERage

Will the connection string change?

---

Yes. Do you have an onsite DBA that will be responsible for your SQL Server databases? (Or is that you?)

Quote:

---

Originally Posted by ERage

Will I need to rewrite the SQL for my select/update/insert/delete queries?

---

Possibly. Access and SQL Server deal with dates differently and the wildcard charcter used in LIKE clauses is different.

There will be a DBA other than myself.

I do have quite a bit of dates and a couple LIKE clauses used...

The LIKE clause will likely (no pun intended) be an easy fix, but could you elaborate a little on how SQL Server deals with dates differently or maybe there is some resource out there I should look towards for details?

Thanks for your comments so far.

It has been a very, very, very long time since I've used Access, but, I believe when referencing a date in an query, the date (or textbox storing the date) has to be encapsulated with # signs. SQL Server uses single quotes.

Your DBA should be able to provide you with the connection string that you can use.

I would also talk to your DBA about the possibilities of setting up a test and production environment so that after you (or the DBA) has imported the data from your Access database into your SQL Server, you have test side to play around with while doing your code rewrite. You will also have that as a platform against which to test any future requirements/enhancments.

I'm more of a programmer than I am a database person, and the though just stuck me that you might need some advice/pointers from database people, and this is the section to get that from, so I think your question might be better off here. :)

Moved

Right Access does use # for dates, changing to single quotes is not too big of a problem, I was worried there was going to be more to it than that. Well these seem like mostly minor details. I feel more comfortable about it now, Thanks Hack :D

I'm going to mark this thread resolved but if anyone else has further thoughts please drop them in here.

Thanks!

Quote:

---

Originally Posted by ERage

Right Access does use # for dates, changing to single quotes is not too big of a problem, I was worried there was going to be more to it than that. Well these seem like mostly minor details. I feel more comfortable about it now, Thanks Hack :D

I'm going to mark this thread resolved but if anyone else has further thoughts please drop them in here.

Thanks!

---

Without knowing the absolute specifics I can't be 100% positive, but I've been involved with these types of projects before, the recoding was pretty easy and straightforward.

What you will probably find out, however, is once you get the new version of your program rolled out using SQL Server, you are going to get hit with a ton and a half of enhancment requests. :D (Been down that road - :sick: )