MS Access to SQL/Crystal Reports

Hi.  I have a number of MS Access reports that have to be translated into Crystal Reports using SQL server.  I have already gotten the tables moved over to SQL, however I am having a difficult time looking at the Access reports and figuring out where the data is coming from...  can anyone help me?  I am unsure which table or query or whatever these fields are coming from, and I as I have to redo everything in SQL, it would be helpful to know where the data is coming from...

ANY IDEAS???  thanks so much!
LVL 2
suzbaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FeyoCommented:
If I am reading your question correctly, to find out where the data is coming from in a report, all you have to do is view the report's record source: right click in the upper left hand corner of the report and select "Properties" from the menu that appears. Click on the "Data" tab. There you will see the record source for the main form. Now, if some of the record sources are set programatically, as listboxes and combo boxes sometimes are, you will have to search through the code attached to various report events to figure out where they are getting their data.
FeyoCommented:
I'm sorry. Anytime you see "form" in my post, I meant to write "report".
BCooperCommented:
If your original DB was Access for both front and back end, you can use the Documenter tool (not available for Access with SQL backends).  Located in the Tools menu under Analyze, it will allow you to choose reports, tables, queries, etc.  You can run it on all your reports or just single reports and will allow you to choose what you'd like to see in the displayed by the Documenter's report by clicking the option button.  This will show you where the data comes from.  From there you may need to look at the query or table that drives your reports to determine more information.  I've used this method for every Access to SQL upsize I've ever done.

Hope this helps

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

suzbaraAuthor Commented:
Ok.  so I love this analyze thing... however it isn't seemless... and I am struggling with it checks out ok, but when I run it it errors with this:  Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ' '.
  any ideas how to figure out where it is dying???
BCooperCommented:
What application is giving you this error?
suzbaraAuthor Commented:
When I run the "code" in SQL.  (the code that came from the analyze in Access)...

I edited a lot of it, but I don't know anything about how to read the errors in SQL.
BCooperCommented:
If it's not too sensitive, could you post the "code" here.  The error relates to a column (field) name being invalid which can happen if the upsize to SQL found spaces, reserved words, or invalid characters in the column names and changed them.  The sketchy part is the fact that it doesn't give you the column name.
suzbaraAuthor Commented:
SELECT  'Employee.FirstName', 'Employee.LastName' AS Name, 'Employee.EmpUID', 'EmpSalH.StartDate',
      'EmpSalH.SalChgCode', 'Employee.FirstName', 'Employee.LastName', 'Employee.EmpAddr1', 'Employee.EmpCity',
      'Employee.EmpState', 'Employee.EmpZipCode', 'Employee.HireDate', 'CmpnyJob.JobName', 'EmpSalH.SalaryType',
       'EmpSalH.HoursPerWk', 'EmpSalH.SalaryAmt', 'CmpnyDep.DeptName', 'CmpnyLoc.LocName',
      'CmpnyDiv.DivName', 'EmpSalH.SalaryAmt'/'JobEvalTable2.MIDPOINT' AS [Compa-Ratio], 'JobEvalTable2.MIDPOINT'*0.75 AS [MIN],
      'JobEvalTable2.MIDPOINT'*1.25 AS [MAX], 'JobEvalTable2.MIDPOINT', 'Reason.SalChgReason',
      'EmpSalH.StopDate', 'EmpJobH.StopDate', 'EmpSalH.Notes', 'Employee.TermDate', 'EmpJobH.StartDate' AS [Title
      Start Date], 'Employee.CompanyID', 'Employee.EmpUID'

FROM [Reason] RIGHT JOIN (((((((Employee INNER JOIN EmpJobH ON (Employee.EmpUID=EmpJobH.EmpUID) AND (Employee.CompanyID=EmpJobH.CompanyID)) INNER JOIN
      EmpSalH ON (Employee.EmpUID=EmpSalH.EmpUID) AND (Employee.CompanyID=EmpSalH.CompanyID))
      INNER JOIN CmpnyJob ON (EmpJobH.JobID=CmpnyJob.JobID) AND
      (EmpJobH.CompanyID=CmpnyJob.CompanyID)) INNER JOIN CmpnyDep ON
      (EmpJobH.CompanyID=CmpnyDep.CompanyID) AND (EmpJobH.DeptID=CmpnyDep.DeptID)) INNER JOIN
      CmpnyDiv ON (EmpJobH.CompanyID=CmpnyDiv.CompanyID) AND
      (EmpJobH.DivisionID=CmpnyDiv.DivisionID)) INNER JOIN CmpnyLoc ON
      (EmpJobH.CompanyID=CmpnyLoc.CompanyID) AND (EmpJobH.LocationID=CmpnyLoc.LocationID)) LEFT
      JOIN JobEvalTable2 ON CmpnyJob.JobName=JobEvalTable2.JOB_TITLE) ON [Reason].SalChgCode=EmpSalH.SalChgCode
      WHERE (((Employee.EmpID)=[ ]) AND ((EmpSalH.StopDate) Is Null) AND ((EmpJobH.StopDate) Is Null) AND
      ((Employee.TermDate) Is Null));

BCooperCommented:
Everything looks ok in the code.  From here I can only guess that an SQL Column name does not match the Access Column name.  Are you using SQL's Query analyzer, Enterprize Manager, or are you pasting the code into Access?  In any event, I'd be forced to fall back on Troubleshooting technique 101.  Create the code one field at a time.  I'd paste the code and then eliminate fields from the Select portion until the error goes away.  If nothing there clears the error, move to the From section and try again.  I wish I could be of greater help, but without a copy of the DB I really can't do much more than lend you moral support.
Sorry,
suzbaraAuthor Commented:
thanks... i figured that would be what I have to do... i will double check the names though...  very irritating it doesn't give you more of a hint...  

I appreciate the moral support... I have a feeling this is going to be a long process.

:)  S
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.