DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL Statement in Access - PLEASE HELP!! URGENT!!

  1. #1
    Jill Morris Guest

    SQL Statement in Access - PLEASE HELP!! URGENT!!


    I have 3 tables in an Access Database (Access 2000) and I want data from all
    3 tables. I have put in the following select statement but the Access query
    keeps reporting a Syntax error - PLEASE HELP - I need this solution within
    the next couple of hours!!

    SELECT Guest.Firstnames+' '+Guest.Surname+','+' '+Spouse.SPFirstName+' '+Spouse.SPLastName
    AS Name, Contact.CoName, Contact.ConNotes, Guest.HAttend, Spouse.SPAttend
    FROM Functions INNER JOIN Guest ON Functions.GuestID=Guest.GuestID
    JOIN Spouse ON Functions.SpouseID=Spouse.SpouseID
    Join Contact ON Guest.GuestID=Contact.GuestID
    Where Functions.Description= '" & strFnName & "' and Functions.DOF=#" & strFnDate
    & "#"

    The table names are Guest, Functions and Contacts
    The conditions are strings that are passed to the database from the visual
    basic form, using the variables strFnName and strFnDate

    This has driven me up the wall - am at breaking point now!!!!!!

  2. #2
    JimmyZ Guest

    Re: SQL Statement in Access - PLEASE HELP!! URGENT!!


    Hi,

    Instead of writing SQL Statements in VB, which to me is a pain in the you
    know where, try creating the Query in Access and then use the ADODB.Command
    and the ADODB.Parameter objects to return the recordset.

    You code will look something like this:

    Dim objCon As ADODB.Connection
    Dim objCom As ADODB.Command
    Dim objPara As ADODB.Parameter
    Dim objpara2 As ADODB.Parameter
    Dim objRS As ADODB.Recordset
    Dim Value as string
    Dim value1 as string
    Dim k As Integer

    Set objCon = New ADODB.Connection
    Set objCom = New ADODB.Command

    'Creating the DB connection string
    'Please change the below connection string as per your server and

    database being used.
    objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY
    INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"

    'Opening the connection
    objCon.Open objCon.ConnectionString

    MsgBox "Connection opened"

    'assigning the command object parameters
    With objCom
    .CommandText = "GetRecords" 'Name of the access query
    .CommandType = adCmdTable 'Type : Table/Query
    .ActiveConnection = objCon.ConnectionString 'Connection String
    End With

    'Create 2 parameters
    Set objPara = objCom.CreateParameter("rows", adInteger, adParamInput,4,Value)
    Set objpara2 = objCom.CreateParameter("Status", adVarChar,
    adParamInput, 50,Value1)

    'Append the output parameters to command object
    objCom.Parameters.Append objPara
    objCom.Parameters.Append objpara2

    'Store the result in a recordset
    Set objRS = objCom.Execute


    'Open the recordset
    Do While Not objRS.EOF
    For k = 0 To objRS.Fields.Count - 1
    Debug.Print objRS(k).Name & ": " & objRS(k).Value
    Next
    Debug.Print "_____"
    objRS.MoveNext
    Loop

    'Close the recordset
    objRS.Close

    'retrieve the output parameters values
    MsgBox "Total records returned: " & objPara.Value
    MsgBox objpara2.Value

    'close connection
    objCon.Close

    'cleaning up
    Set objCom = Nothing
    Set objCon = Nothing
    Set objPara = Nothing
    Set objpara2 = Nothing
    Set objRS = Nothing

    End Sub

    "Jill Morris" <genem@omantel.net.om> wrote:
    >
    >I have 3 tables in an Access Database (Access 2000) and I want data from

    all
    >3 tables. I have put in the following select statement but the Access query
    >keeps reporting a Syntax error - PLEASE HELP - I need this solution within
    >the next couple of hours!!
    >
    >SELECT Guest.Firstnames+' '+Guest.Surname+','+' '+Spouse.SPFirstName+' '+Spouse.SPLastName
    >AS Name, Contact.CoName, Contact.ConNotes, Guest.HAttend, Spouse.SPAttend
    >FROM Functions INNER JOIN Guest ON Functions.GuestID=Guest.GuestID
    >JOIN Spouse ON Functions.SpouseID=Spouse.SpouseID
    >Join Contact ON Guest.GuestID=Contact.GuestID
    >Where Functions.Description= '" & strFnName & "' and Functions.DOF=#" &

    strFnDate
    >& "#"
    >
    >The table names are Guest, Functions and Contacts
    >The conditions are strings that are passed to the database from the visual
    >basic form, using the variables strFnName and strFnDate
    >
    >This has driven me up the wall - am at breaking point now!!!!!!



  3. #3
    MarkN Guest

    Re: SQL Statement in Access - PLEASE HELP!! URGENT!!


    Which is fine - until you move to a different DB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center