Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    1

    Unanswered: How To use Table Name which is in variable in SQl

    I faced a problem while executing an SQL query from VB
    using VB ADO and MS Access database.The following
    query does not work.

    Here is the scenario:


    strFirstFile =Text1.Text
    strSecondFile =Text2.Text


    adoRecSet=adoConn("SELECT &strFirstFile.FirstName,
    &strSecondFile.Salary FROM "
    &strFirstFile,&strSecondFile WHERE
    &strFirstFile.Empcode=&strSecondFile.EmpCode )

    How do i use Table name which is stored in a variable
    as in this example?

    I would be thankful to you for throwing some light on
    the above scenario .

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be inclined to use something like:
    Code:
    adoRecSet=adoConn("SELECT f1.FirstName, f2.Salary FROM " & strFirstFile _
    & " AS f1 JOIN " & strSecondFile _
    & " AS f2 ON (f1.Empcode=f2.EmpCode)"
    I'm going to punt this over to the MS-Access forum to see if they've got better ideas.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try adding four ' single quotes
    ...and the accidentally dropped )

    adoRecSet=adoConn("SELECT f1.FirstName, f2.Salary FROM '" & strFirstFile _
    & "' AS f1 JOIN '" & strSecondFile _
    & "' AS f2 ON (f1.Empcode=f2.EmpCode))"

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •