Microsoft Access to ADP conversion tips & tricks 1

[Silvertri]

New poster, currently in process of converting access to adp with SQL backend when finished next step is to convert front end to .Net (more fun no doubt). Thought experiences so far might be useful reference. Our product is quite large 400 forms 1600 queries etc. Would not convert from 2000 to SQL7 installed XP and SQL 2000 conversion still hung in forms transfer but approx 1500 queries came across and manually imported remaining forms reports etc.
Now the fun starts.. the biggest identified problem is parameters on queries which we use extensively. In most cases the parameters were corrupted on transfer and wont work anyway without using stored procedures useful doco at

http://www.datarevive.com/access_to_sql_server.php

 

[Silvertri]

BTW the best most useful tip I've found and should be added to Hall of Fame is

When the stored procedure is used as the record source of a Form the parameters can be added in the Parameter property under the data tab. Use the format like so:

@var1=Forms!myForm!myField,@var2=Forms!myForm!myfield2

cmmrfrds take a bow.... I voted for you!!

 

[Silvertri]

Another good site to explain the correct syntax for DateDiff etc in SQL note that the Access XP on line help is incorrect eg you need to use "hour" not "h"

http://www.databasejournal.com/features/mssql/article.php/10894_2216011_2

 

[Silvertri]

Another difference to be overcome b/t Access & ADP.

We use extensive sub forms ( hundreds) all linked by indexed fields on the parent/child options on the sub form.
It appears this doesnt work in ADP and you have to specify you data on the sub form input parameters. I am happy to be corrected????/

 

[Databaseguy]

Another point, your cross tab queries won't import. You have to write new SPs and use the CASE statement.

 

[cmmrfrds]

"We use extensive sub forms ( hundreds) all linked by indexed fields on the parent/child options on the sub form.
It appears this doesnt work in ADP and you have to specify you data on the sub form input parameters. I am happy to be corrected????/"

You might have to hunt around with the cursor, but the every upper left corner of the subform allows you to link to the master Form -- every small area on upper tip. The same as subforms in an mdb.

 

[Silvertri]

thanks for your reply, a point of clarification we finished up importing all the forms into the ADP front end due to it hanging on convert proces after tables and queries had come across. I can see the master child link fields and they are set up correctly however on this particular form it doesnt appear to restrict data display in the child sub form, hence my comment (fixed by using input parameters) from what you are saying this should not be necessary??
So it actually may be a conversion issue (as some sub forms work ok???) BTW have you some advice posted anywhere on using combo boxes to drive Stored Procedures?

TIA Silvertri

 

[cmmrfrds]

I have seem threads on using stored procedures with comboboxes. If I remember correctly, a combobox will not accept a stored procedure as the data source. I believe a combobox will only take a list, table, or sql statement as the data source, but try searching on the Forum -- maybe about 2-3 months ago.

I usually use public functions to return variables to build an sql statement.

In standard module.
public mydate as date
public function returnmydate() as date
returnmydate = mydate
End function

Dim sql1 as string
mydate = "1/1/2003"
sql1 = select * from mytab where adate > '" & returndate() & "'"

You can do this is the query builder of the combobox.

 

[ESquared]

Sure you can use a stored procedure in a combobox (although this may be version dependent, it at least works in Access 2003). Just use the "EXEC SPName" syntax.

 

[dwight1]

CMMRFRDS AND SILVERTRI,

Well, My conversion was not that big one but got enough experience to handle move from mdb to adp.

I was curious to read about the forms and subforms. I have one db that has many forms/subforms. Frankly, never tried to move it to adp but your reference was quite helpful.

Silvertri your quote
"It appears this doesnt work in ADP and you have to specify you data on the sub form input parameters. I am happy to be corrected????/"

Does that mean if we have to link borrowerid from parent form to subform, how do we give our input parameters. Can you give an example? The closest i could get was
@borrowerid=forms!borrowerform!borrowerid. Is this right?


Cmmrfrds Your Quote
"You might have to hunt around with the cursor, but the every upper left corner of the subform allows you to link to the master Form -- every small area on upper tip. The same as subforms in an mdb."

Did not quite get it? How do we link it?

Thank you guys for your inputs.

Dwight