Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
PROBLEMS WITH SQL

답변됨 PROBLEMS WITH SQL

  • Saturday, March 16, 2013 2:47 PM
     
      Has Code
    Sign In to Vote
    0

    I am practicing SQL Queries, and i am faced with a problem

    i have an sql statement.

    SELECT APPTLOOKUPTABLE.DEPT FROM APPTLOOKUPTABLE WHERE APPTLOOKUPTABLE.APPT =  APPTCB.Value

    This statement works perfectly fine.

    However, when i include it into vba access

        Dim data As Database
    Dim record As Recordset
    
    Set data = CurrentDb
    Set record = ("SELECT APPTLOOKUPTABLE.DEPT FROM APPTLOOKUPTABLE WHERE APPTLOOKUPTABLE.APPT = " & Chr$(39) & APPTCB.Value & Chr$(39))
    
    MsgBox (record.RecordCount)

    i do not get any returns. There is no error message, just that the query simply returns nothing.

    i do not understand how it is that the statement can run when executed alone, but when included into vba access, i couldnt get it to run.

    Any assistance in helping to understand this better is appreciated.

    Thank you

     

All Replies

  • Saturday, March 16, 2013 4:06 PM
     
     Answered Has Code
    Sign In to Vote
    1

    First of all: Transact-SQL is the SQL dialect used by SQL Server. Microsoft Access and the Jet/ACE use the Jet SQL dialect.

    I don't think that your statement works as you think. The right side of your WHERE condition is undefined. Your also not correctly using the Recordset object. But for your actual problem: Output the SQL statement to see what you're doing.

    Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Sql As String
Dim SearchValue As String
    
SearchValue = "'" & Replace(APPTCB.Value, "'", "''") & "'"
Sql = "SELECT DEPT FROM APPTLOOKUPTABLE WHERE APPT = " & SearchValue
MsgBox SQL
	
Set db = CurrentDb
Set rs = db.OpenRecordSet(Sql)
    
MsgBox (rs.RecordCount)

rs.Close
Set rs = Nothing
Set db = Nothing


    And last but not least: You may use DCount("*", "APPTLOOKUPTABLE", "APPT=" & SearchValue) to get the same result.
     
  • Saturday, March 16, 2013 4:19 PM
     
     Answered
    Sign In to Vote
    1

    When I try your code in A2010, I got the correct error 424: Object Required, on the "set record" line. This is because a recordset is not created the way you tried to, but rather like this:

        Dim data As DAO.Database
        Dim record As DAO.Recordset
       
        Set data = CurrentDb
        Set record = data.OpenRecordset("SELECT APPTLOOKUPTABLE.DEPT FROM APPTLOOKUPTABLE WHERE APPTLOOKUPTABLE.APPT = '" & APPTCB.Value & "'", dbOpenSnapshot)
        If Not record.EOF Then record.MoveLast  'Make sure RecordCount is correct (lazy loading)
        MsgBox "Record count: " & record.RecordCount, vbInformation
        record.Close
        Set record = Nothing
        Set data = Nothing

    You could also have used the one-liner:
        MsgBox "Record count: " & DCount("*", "APPTLOOKUPTABLE", APPT='" & APPTCB.Value & "'")

    The reason the statement runs on its own without all this code while in the query designer, is that MSFT did all the work behind the scenes.

    -Tom. Microsoft Access MVP

     
  • Saturday, March 16, 2013 5:48 PM
     
      Has Code
    Sign In to Vote
    0

    Hi, thanks for the prompt reply.

    However, i still dont really understand.

    this is a screenshot of my sql query

    the results when executed are

    the query works perfectly fine, returning the dept value.

    However, when i insert it into vba

        Dim data As DAO.Database
    
    Dim record As DAO.Recordset
  
    Set data = CurrentDb
    Set record = ("SELECT DISTINCT APPTLOOKUPTABLE.DEPT FROM APPTLOOKUPTABLE WHERE APPTLOOKUPTABLE.APPT = 'COMMS2' ")

    i have no returns stored in the recordset.

    i have edited the declaration of database and recordset to DAO.DATABASE AND DAO.RECORDSET accordingly

     
  • Saturday, March 16, 2013 5:52 PM
     
     
    Sign In to Vote
    0

    That's why I wrote:

    "This is because a recordset is not created the way you tried to, but rather like this:"

    Study my code, and run it, and you will see.

    -Tom. Microsoft Access MVP

     
  • Saturday, March 16, 2013 5:54 PM
     
      Has Code
    Sign In to Vote
    0

    Well, ever heard of copy'n'paste? Just paste the entire piece of code - which I or Tom has posted - into your module...

    The basic problem is that you assign a string to a recordset object. This cannot work. You need to use the OpenRecordset method from the database object:

    Set db = CurrentDb

' Retrieve record set object from database object:
Set rs = db.OpenRecordset(Sql)
    
MsgBox (rs.RecordCount)

     
  • Sunday, March 17, 2013 9:56 AM
     
     
    Sign In to Vote
    0

    Hi stefan and tom

    thank you for your assistance. I finally understand.

    i didnt realise that the way i open the recordset was incomplete

    totally left our db.openrecordset, so i could run in as an sql statement, but not in vba. because by just running an sql query, the db.openrecordset() is not required.

    i apologise for my mistake.

    Thank you both.

     
  • Sunday, March 17, 2013 4:13 PM
     
      Has Code
    Sign In to Vote
    0

    Just a small correction in Stefan's code (rs.MoveLast, rs.MoveFirst), see below. I would not use Sql as a variable name, since SQL is a keyword.

    Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sq As String
Dim SearchValue As String
    
SearchValue = "'" & Replace(APPTCB.Value, "'", "''") & "'"
sq = "SELECT DEPT FROM APPTLOOKUPTABLE WHERE APPT = " & SearchValue
MsgBox SQL
	
Set db = CurrentDb
Set rs = db.OpenRecordSet(sq)
rs.MoveLast
rs.MoveFirst
    
MsgBox rs.RecordCount

rs.Close
Set rs = Nothing
Set db = Nothing

    Vladimir Cvajniga