upsize to SQL? 1

[MichaelRed]

Having read many of the suggestion, some directions and a few horror stories, I am about to start the oft delayed process. To this end, I have a few(?) questions:

Do we really move ALL tables (even the local ones) to a seperate BE?

(Assuming Yes to the above), SQL server is advertised to be able to know which tables are temp and allocate these differently (so multiple users can run the same processes w/o interfeering with each other). However I can't seem to find / figure out how these temp tables are identified by / to SQL Server. Who can explain this (even if via reference to other documentation).

Is it preferable to move all queries to SQL Server? Even the litbox, combobox, ... rowsource queries?

If 'all' Tables and queries are sepweated, does the upsize whizzz do a beter job of conversion (esp issues like data typing? and syntax)?

If I can identify the procedures included in the Queries, can the upsizeing process utilize them to 'ease' the process? Will Mr. UpSize do anything with procedures?

What will Mr. Upsize do re Indexes? Are they just 'replicated'? Is there some part of the process which examines them and remove those which are not used?

If Mr. upsize doesn't 'review' and correct the indicies, are there any useful guidelines to doing such as review from within MS Access to help remove un-useful ones?

What (other?) perils and pitfalls are most likely to cause trouble.

PS this is cross posted in the SQL Server forum

Thanks,


MichaelRed

 

[JoeAtWork]

I'm assuming you are talking about linked tables on an MDB, not an Access Data Project (ADP). Some of my answers do not apply for ADP's, especially regarding queries.

Do we really move ALL tables (even the local ones) to a seperate BE?

Some tables you may keep on the front end, if it makes sense. These would usually be tables that are hardly ever updated, an example might be a list of provinces or states.

(Assuming Yes to the above), SQL server is advertised to be able to know which tables are temp and allocate these differently (so multiple users can run the same processes w/o interfeering with each other). However I can't seem to find / figure out how these temp tables are identified by / to SQL Server. Who can explain this (even if via reference to other documentation).

I'm not sure what you are referring to here. It's true you can make temporary tables within a stored procedure, and return that as a recordset. There's nothing automatic about that, and you can't bind a form directly to it - you would need to write code to populate a recordset from the stored procedure, and then bind the form to the recordset.

Is it preferable to move all queries to SQL Server? Even the litbox, combobox, ... rowsource queries?

Your queries are still on the front end, they are bound to your linked tables. Unless you are talking about upsizing to an ADP - in that case it does try to convert all your queries to either views, functions, or stored procedures. Depending on the complexity of your query, it will get maybe 80% - 90% correct, while some it can't convert.

If 'all' Tables and queries are sepweated, does the upsize whizzz do a beter job of conversion (esp issues like data typing? and syntax)?

It does about 95% of what you would have done yourself. It's a lot less work, in my opinion, to just do the little tweaks necessary after the conversion, then to try to recreate the database manually yourself.

If I can identify the procedures included in the Queries, can the upsizeing process utilize them to 'ease' the process?

As I said above, your queries will be on the front-end (unless you're making an ADP). They will function the same as before.

Will Mr. UpSize do anything with procedures?

The upsizing wizard will do absolutely nothing to your procedures.

What will Mr. Upsize do re Indexes? Are they just 'replicated'? Is there some part of the process which examines them and remove those which are not used?

As I recall the upsizing wizard simply tries to match the current schema one for one, although I think it does make some recommendations at the end.

If Mr. upsize doesn't 'review' and correct the indicies, are there any useful guidelines to doing such as review from within MS Access to help remove un-useful ones?

In the end it's just an SQL database - use the same tools and procedures that a database administrator would to optimize the database.

What (other?) perils and pitfalls are most likely to cause trouble.

The main problem is usually very complex queries, the ones that are several layers deep, do a lot of grouping, filtering, etc. These usually have to be replaced, I often create stored procedures and then open recordsets and I apply them to my form and/or controls.

 

[MichaelRed]

WOW!!! Thanks These are just the types of responses I was hoping to get!!!



MichaelRed