mathprof
asked on
variable in from clause
I'm having trouble in VB6 with an access db using a variable (TableName$) in the FROM clause of a SQL statement.
Set rs = dbmath.OpenRecordset("Sele ct Password,Number, First, Last FROM TableName$ where First = '" & TeacherFirst.Text & "' and Last = '" & TeacherLast.Text & " ' ")
What is the correct syntax?
Mathprof
Set rs = dbmath.OpenRecordset("Sele
What is the correct syntax?
Mathprof
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you !. It worked.
And thank you too, RSPAHITZ, for helping out someone very new to SQL.
Mathprof
And thank you too, RSPAHITZ, for helping out someone very new to SQL.
Mathprof
If it helps in the future, you may want to break things down into smaller pieces:
' First define constants which can be carried from code to code or defined globally
Const cSelectVerb as string = "SELECT "
const cFromVerb as string = " FROM "
const cWhereVerb as string = " WHERE "
' Now define variable pieces:
Dim strSelectFieldList as string
dim strWhereClause as string
strSelectFieldList = "Password,Number, First, Last "
strWhereClause = cWhereVerb & "First = '" & TeacherFirst.Text & "' and Last = '" & TeacherLast.Text & "'"
' Now assemble all the pieces:
Set rs = dbmath.OpenRecordset(cSele
--
And if the where clause gets too complex, you could break that down in the same way as the select:
const cFieldName1 as string = " First "
const cFieldName2 as string = " Last "
const cAndVerb as string = " AND "
const cEquateVerb as string = "="
dim strFieldValue1 as string
dim strFieldValue2 as string
strFieldValue1 = "'" & TeacherFirst.Text & "'"
strFieldValue2 = "'" & TeacherLast.Text & "'"
strWhereClause = cWhereVerb & cFieldName1 & cEquateVerb & strFieldValue1 & cAndVerb & cFieldName2 & cEquateVerb & strFieldValue2
--
This may look confusing at first, but it's general-purpose enough that you could turn it into a function which assembled all the pieces for you and returns the correct SQL string. That's a bit overkill for simply queries, but if you do a lot, it may be worthwhile.