Acess to sql server - Help....

[Ken S]

I'm wondering if you could help me. I have been given a task to upsize an Access db from Access 2000 to SQL Server. I have gone thru the upsize process and have encountered errors. I cannot find any help on how to resolve these errors and would appreciate any assistance you can offer. I've looked at Microsoft.com and have found articles on upsizing that have been very good - but not for these specific errors. There are many errors but they fall within these categories, if you help on these I think I can fix the others.
--------------------------------------------------------------------------------------------------------------------
I am receiving message of 'the query is not upsizable because it depends on a form parameter'. How would I correct ?
the code :
SELECT ChildSchoolTbl.MRN, ChildSchoolTbl.Childname1, ChildSchoolTbl.SchoolName1, ChildSchoolTbl.Childname2, ChildSchoolTbl.SchoolName2,
ChildSchoolTbl.Childname3, ChildSchoolTbl.SchoolName3, ChildSchoolTbl.Childname4, ChildSchoolTbl.SchoolName4, ChildSchoolTbl.Childname5,
ChildSchoolTbl.SchoolName5, ChildSchoolTbl.Childname6, ChildSchoolTbl.SchoolName6
FROM ChildSchoolTbl
WHERE (((ChildSchoolTbl.MRN)=[Forms]![PatientFrm]![MRN]));
--------------------------------------------------------------------------------------------------------------------
Message: The wizard was unable to analyze this SQL for this query:
But it did create a procedure during the process according to the report but didn't upsize...
Original Code:

SELECT PatientsTbl.PatientName, PatientsTbl.SS, PatientsTbl.CIN, PatientsTbl.DOB,
AccountsTbl.ServiceDate, AccountsTbl.CaseManager, PatientsTbl.ActiveInactiveCase, AccountsTbl.Account,
PatientsTbl.PatientPhone, AccountsTbl.FinalStatus
FROM PatientsTbl INNER JOIN AccountsTbl ON PatientsTbl.MRN = AccountsTbl.MRN;
----------------------------------------------------------------------------------------------------------------------------
Message: Failed to upsize. Attempted to use SQL:
But it did create a procedure during the process according to the report but didn't upsize...
Original Code:
SELECT AccountsTbl.Account, AccountsTbl.Charges, AccountsTbl.Reimbersement,
AccountsTbl.SpendDown, AccountsTbl.ServiceDate, AccountsTbl.DischargeDate, PatientsTbl.MRN,
PatientsTbl.PatientName, PatientsTbl.ActiveInactiveCase, AccountsTbl.AdmitDate
FROM AccountsTbl INNER JOIN PatientsTbl ON AccountsTbl.MRN = PatientsTbl.MRN
WHERE (((PatientsTbl.ActiveInactiveCase)="ACTIVE"))
ORDER BY PatientsTbl.PatientName;
Please feel free to contact me at ken_saffer@urmc.rochester.edu

 

[sfreeman@co.mer]

I do not believe SQL Server recognizes data collected from a form that resides in Access.

Work around by figuring out a way [new query maybe] to provide the same data without referencing the form.

The last two issues: Not sure you have a problem since it did create a stored proc for you. Did you confirm that it is not working? [double quotes, try single quotes.]

[From Rochester? Is that SUNY? If so, we helped put your phone system in... hope it's working:-B]

 

[Pat Hartman]

I guess you have bought the party line and upsized to an .adp. I have a swell bridge for sale if you're interested.

Joking aside, unless someone is forcing you to use an .adp, stick with an .mdb. I object strenuously to .adp's for several reasons.
1. You are forced to use ONLY SQL Server. You loose the ability to link to Oracle, DB2, etc. For some organizations, this may not be a problem but I object to it on principle.
2. You can't even link to Jet tables!
3. The GUI doesn't support the same features in form/report development that the .mdb does.
4. You are forced to convert many things yourself.
5. etc.

When I "upsize", I convert the tables and NOTHING else. Of course, I always build my apps with upsizing in mind so my forms/reports are always based on queries with selection criteria. My table/column names are always properly formed so they comply with most professional standards.

Although there is a slight speed advantage to using stored procedures, it is not enough for me to give up bound forms and the other things that come with the .mdb. Many people are not aware that Jet attempts to send EVERY query to the backend server for processing so if your queries don't contain elements that will prevent it, SQL server will send ONLY the requested rows and columns back.