Standalone to client server?

[PatrickIRL]

Hi all, if this is wrong forum then apologies.

I have a standalone app in VB6 and MS Access. I was asked recently what would be involved in changing this to use SQL Server instead and have it point to a centralis(z)ed server.

Off the top of my head all I could think of was changing the ADO connection string. Simplistic view I know but what more is involved????

Thanks in advance,

Patrick

 

[rickyoswaldiow]

Hey Patrick. I am currently learning this too, I have worked with standalone VB6 connecting to Access but I've recently started a job where we are working with a SQL server.
Unfortunately it is more complex than simply changing the connection string
I'm sure we will both get there with the wonderful help offered on these forums

 

[Andrzejek]

I would recreate Access DB in SQL, point my app to it and see what will happen.

If you already use ADO, you may be fine.

If you have any dates, you may change some SQLs, in Access you had #'s around dates, and SQL does not have them, I think.

Let us know about other problems, people here will help you.

Have fun.

---- Andy

 

[BobRodes]

A few years ago I got involved with a very large conversion, involving hundreds of tables, and the process was actually pretty complex. Most of it had to do with wandering around on the internet looking at pages and pages of "this behavior is by design" to find out why something isn't working as expected, and I can tell you that that's much more frustrating to me than working out the bugs in my own code.

There are indeed some syntactical differences between Access and SQL server, so these have to be handled. Also, if you have any linked tables in the Access solution, you'll have to revisit them; whatever you're using to enter data in the linked tables will have to be rearchitected.

So, the first step is to recreate your existing Access schema in SQL Server. Then migrate the data, then point your app to it. Then start debugging...and debugging....

Bob

 

[JoeAtWork]

It mostly depends on how complex your database currently is. If you have only a couple of dozen tables, it may require little more than changing the connection string.

You are fortunate that you are already using a VB6 front-end with ADO to connect to the database. If this had been an Access front-end to VB6 conversion, it would have been a lot messier.

When you upgrade the database, I suggest you use one of the conversion tools already available, either the Access upgrade wizard, or the freely downloadable Microsoft SQL Server Migration Assistant For Access (SSMA). The conversion tools will in most cases match up your data-types correctly, set up triggers to emulate Access's cascade updates, etc.

After that, just change your connection string and see what happens.

The most likely place where you will need to make changes are any SQL statements involving dates. SQL Server does not use the #m/d/y# syntax. Instead, you usually use some sort of string, like 'July 31, 2007'.

 

[PatrickIRL]

Ok, thanks for all the feedback, much appreciated. There are only 10 tables, not linked, not an overly complex app so based on the posts above it could be an easy switch.

Thanks again all,

Patrick

 

[BobRodes]

Oh yes, this was also a DAO conversion that I did, and the client didn't want to do the code revision necessary to switch to ADO. ADO will be easier, and as you say it doesn't look too difficult. BUT....Access is a pretty squirrely (well, let's say unpredictable) environment to work in. After all, Microsoft wrote it from scratch, whereas for SQL Server they bought Sybase and had the good sense not to mess with the internals.

 

[PatrickIRL]

Well, feedback is this:

After an expletive torn long weekend that would make Colin Farrell blush (must be an Irish thing), I finally got everything up and running. Most of the time was spent setting up SQL Server Express and configuring that. Talk about losing my religion, honestly !

The app worked almost perfectly, only issue was date format as stated in earlier posts.

Just want to say thanks to all for input, much appreciated.

Patrick