• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

DataControl.RecordSource= How to pass a SQL to MSSQL Server 6.5??

I have recently upsized the back end of my app from Access 97 to MSSQL Server 6.5. One DataControl originally has the following statement:
Data1.RecordSource="SELECT * FROM Staff WHERE StaffID='" & txtStaffID.Text & "'"
However, it does not work with MSSQL Server 6.5 since it goes thru the ODBC layer. I read from "Help" and know that I can set the RecordSource = QueryDef.some_kind_of_property
to pass the SQL statement and then do the Refresh. But how??? Can anyone give me an example???
  • 2
1 Solution
The code you have will work.  You have another problem.
Let me clarify.

Your original code:
Data1.RecordSource="SELECT * FROM Staff WHERE StaffID='" & txtStaffID.Text & "'"

Is fully acceptable, even when you go through an ODBC layer.

Are you sure txtStaffID.Text contains data?
Are you sure the tables were rebuilt in SQLServer the same as they were in Access (table and field names the same)?

Are you getting any errors?

If StaffID is an Autonumber field then take out the single quotes it should read
Data1.RecordSource="SELECT * FROM Staff WHERE StaffID= " & txtStaffID.Text

Like everyone said this SQL statement will work, but this looks to be your problem not the statement itself.
ppmbAuthor Commented:
I read the comments from expert clifABB first and found out that it really is not the problem from the statement. Instead, it is from setup and the DBA. I am gonna credit clifABB the points but clifABB didn't have an answer for me to give points. How am I gonna do this???
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now