MS SQL Conversion and Deployment

[ssteinke]

Hi guys, I hope somebody can give me a quick step-by-step.

I've nearly completed a program written in Access and have started to venture into marketing the program to a local company. When asked what other modifications they would like to see i was sent an email stating;

"A major item would be to deploy in MS SQL. You should be able to do that free of charge with the low tier version of SQL."

I had a feeling this would come up, but I don't want to sound like a total idot when talking to these folks... I really have no true understanding of what they are asking, but I suspect it refers to conversion from jet. Question: Is this a reasonably easy modification? What do I need to purchase? Would I be better off telling them the program is 'as-is'? Can this be easily done by someone who has never done it before? I'm sure other threads addressed this issued but I would truly appreciate a full understanding of my specific problem. Thanks, Scott.

 

[shadow9449]

...converting the be to SQL Server will take less than an hour since all you would need to do would be to upsize the tables with the upsizing wizards.

What about local queries? Don't they have to be transferred to stored procedures to get the benefits of client/server?

I assume that when you say "created with client/server in mind", you are referring to the forms and reports, that they are unbound or based on very limiting queries.

SHADOW

 

[ssteinke]

"Created with client/server in mind" brings me to another question, are their some major DO's and DONT's when programming with the intent on upsizing later? Specifically, my program is split, most forms are unbound using DAO to populate forms, with the exception of listboxes and continuous subforms.

Are their some common programming mistakes people can avoid or that may simplify the upsizing process with less headache?

 

[Banana]

ssteinke, I'm in the same shoes as you are. I already read the kb articles.

The only one major thing that stood out to me was about how Jet and SQL differ in binding. With Jet, Autonumber is assigned as soon a new entry is made, and you can use that to set subforms or other related records. However, with SQL, IDENTITY is assigned only when the record is saved. Therefore anything that needs to have same ID for related records has to be moved to after saving the record.

That said, I can't help but feel there's more to the pictures.

 

[Banana]

but what about functions such as Nz and IIF? I read somewhere that they are not necessarily supported?

 

[Banana]

Funny,

I was just reading Alison Balter's Mastering Access 2002 about developing/distributing Access.

It says if Access is to be used as a front end, all queries must be pass-through, and all forms must be unbound.

Furthermore, it even says that using Access as a pure front-end may not as effective as developing an application in VB.

Is that accurate?

 

[shadow9449]

So as long as you are careful with your queries, Jet will automatically pass them directly through to the server for processing and only the requested records will be returned.

This is VERY interesting info, Pat. It's contrary to what I've heard from Access to SQL Server upscaling "gurus". I'm glad that it's not as bad a task as they make it sound.

Thanks, Pat.

SHADOW