Programming Forums > Application Development > Visual Basic

eggsy · Mar 7th, 2005, 9:06 AM

Hi Guys

Just wondering if anyone can help?

Suppose I have two tables in Access

Customers
===========
CustID
CustName

Reservations
===========
ResID
CustID

As you can see the relationship between the two is CustID.

Now if I have a form and two dataControls :

datCustomers and datReservations and each corrosponding to the given table / record source

How could I code a button that when clicked runs the SQL query given below:

SELECT c.CustomerID, c.CustomerName
FROM CustomerDetails c, reservationDetails r
WHERE r.CustomerID = c.CustomerID

Any other info needed ?

Regards all and thank you for reading

Eggsy

faLLeN · Mar 7th, 2005, 10:12 AM

I normally steer clear of using the data controls. I would use ADO 2.8 as a reference and create the objects myself. I've found that to be the most flexible way to play with data really.

If for example those tables where in Microsoft SQL Server, I would use the below code:

(Toggle Plain Text)

Option Explicit

Dim dbConnection    As ADODB.Connection
Dim dbRecordset     As ADODB.Recordset

Public Sub ShowData()

    'This just outputs your query in the immediate pane (press Ctrl+G to show it)
  
    Dim strConnectionString As String
    Dim strSQLQuery         As String
    Dim strUsername         As String
    Dim strPassword         As String
    Dim strDatabase         As String
    Dim strServer           As String
    Dim intIndexRef         As Integer
    
    Set dbConnection = New ADODB.Connection
    Set dbRecordset = New ADODB.Recordset
    
    'Setup db details
    strUsername = "username"
    strPassword = "password"
    strDatabase = "database"
    strServer = "SQL-SERVER-HOST"
    strSQLQuery = "SELECT c.CustomerID, c.CustomerName " & _
                  "FROM CustomerDetails c, reservationDetails r " & _
                  "WHERE r.CustomerID = c.CustomerID"
    
    'define connection string
    strConnectionString = "Provider=SQLOLEDB.1;" & _
                          "Password=" & strPassword & ";" & _
                          "User ID=" & strUsername & ";" & _
                          "Initial Catalog=" & strDatabase & ";" & _
                          "Data Source=" & strServer
    
    'connect
    dbConnection.ConnectionString = strConnectionString
    dbConnection.Open
    
    With dbRecordset
        
        'perform query
        .Open strSQLQuery, dbConnection
    
        dbRecordset.MoveFirst
    
        'loop through all returned rows
        Do
            
            'print out all the field names and thier values
            For intIndexRef = 0 To (dbRecordset.Fields.Count - 1)
                
                Debug.Print .Fields(intIndexRef).Name & ": " & _
                            .Fields(intIndexRef).Value
                
            Next
            
            Debug.Print ""
            
            dbRecordset.MoveNext
            
        Loop Until (.EOF)
    
    End With
    
    'close recordset
    dbRecordset.Close
    
    'disconnect
    dbConnection.Close
    
    Set dbRecordset = Nothing
    Set dbConnection = Nothing

End Sub

Option Explicit

Dim dbConnection    As ADODB.Connection
Dim dbRecordset     As ADODB.Recordset

Public Sub ShowData()

    'This just outputs your query in the immediate pane (press Ctrl+G to show it)
  
    Dim strConnectionString As String
    Dim strSQLQuery         As String
    Dim strUsername         As String
    Dim strPassword         As String
    Dim strDatabase         As String
    Dim strServer           As String
    Dim intIndexRef         As Integer
    
    Set dbConnection = New ADODB.Connection
    Set dbRecordset = New ADODB.Recordset
    
    'Setup db details
    strUsername = "username"
    strPassword = "password"
    strDatabase = "database"
    strServer = "SQL-SERVER-HOST"
    strSQLQuery = "SELECT c.CustomerID, c.CustomerName " & _
                  "FROM CustomerDetails c, reservationDetails r " & _
                  "WHERE r.CustomerID = c.CustomerID"
    
    'define connection string
    strConnectionString = "Provider=SQLOLEDB.1;" & _
                          "Password=" & strPassword & ";" & _
                          "User ID=" & strUsername & ";" & _
                          "Initial Catalog=" & strDatabase & ";" & _
                          "Data Source=" & strServer
    
    'connect
    dbConnection.ConnectionString = strConnectionString
    dbConnection.Open
    
    With dbRecordset
        
        'perform query
        .Open strSQLQuery, dbConnection
    
        dbRecordset.MoveFirst
    
        'loop through all returned rows
        Do
            
            'print out all the field names and thier values
            For intIndexRef = 0 To (dbRecordset.Fields.Count - 1)
                
                Debug.Print .Fields(intIndexRef).Name & ": " & _
                            .Fields(intIndexRef).Value
                
            Next
            
            Debug.Print ""
            
            dbRecordset.MoveNext
            
        Loop Until (.EOF)
    
    End With
    
    'close recordset
    dbRecordset.Close
    
    'disconnect
    dbConnection.Close
    
    Set dbRecordset = Nothing
    Set dbConnection = Nothing

End Sub

Edit:
Sos.. just noticed you wanted MS Access.

Just use a different connection string, something like this:

(put the filename to the MDB in strDatabase and dont bother with strServer)

(Toggle Plain Text)

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
                      "UserID=" & strUsername & ";" & _ 
                      "Password=" & strPassword & ";" & _
                      "Data Source=" & strDatabase

Hope that helps.

Let me know

Matthew Hall
www.indarkness.co.uk

eggsy · Mar 7th, 2005, 1:40 PM

Great post mate will get on coding.

Sorry to drag more out of you, but after performing above what would the code be to display the results in a textField?

faLLeN · Mar 7th, 2005, 5:40 PM

Hi

Well adding to a text field isnt exactly fitting for the purpose, you should really stick the returned rows into a ListView control (or similar).

But, as you wish, you can do the following.

1) Add this sub:

(Toggle Plain Text)

Private Sub AddItem (strText As String)

   'Adds strText to a multiline textbox control 
   txtTextBox.MultiLine = True
   txtTextBox.Scrollbars = 2 '(Vertical scrollbar) MS didnt provide any Enum for this.. helpful.. :S

   txtTextBox.Text = (txtTextBox.Text & strText & vbCrLf)

End Sub

2) Replace 'Debug.Print' with 'AddItem' in the previous code example I gave.

You'll need a text control called txtTextBox in current scope of the code module you added.

Hey presto, your textbox now shows that data output!

Kind Regards,
Matthew Hall
www.indarkness.co.uk
www.idhosting.co.uk

View Poll Results: Was this question clear?
Very Clear	1	100.00%
Clear enough	0	0%
Required a bit more info	0	0%
As clear as Mud?!	0	0%
Voters: 1. You may not vote on this poll

Mar 7th, 2005, 9:06 AM	#1
eggsy Newbie Join Date: Dec 2004 Posts: 5 Rep Power: 0	Embedded SQL queries Hi Guys Just wondering if anyone can help? Suppose I have two tables in Access Customers =========== CustID CustName Reservations =========== ResID CustID As you can see the relationship between the two is CustID. Now if I have a form and two dataControls : datCustomers and datReservations and each corrosponding to the given table / record source How could I code a button that when clicked runs the SQL query given below: SELECT c.CustomerID, c.CustomerName FROM CustomerDetails c, reservationDetails r WHERE r.CustomerID = c.CustomerID Any other info needed ? Regards all and thank you for reading Eggsy Last edited by eggsy; Mar 7th, 2005 at 9:15 AM. Reason: Tables described incorrectly

Mar 7th, 2005, 10:12 AM	#2
faLLeN Newbie Join Date: Mar 2005 Location: Hertfordshire, UK Posts: 16 Rep Power: 0	I normally steer clear of using the data controls. I would use ADO 2.8 as a reference and create the objects myself. I've found that to be the most flexible way to play with data really. If for example those tables where in Microsoft SQL Server, I would use the below code: (Toggle Plain Text) Option Explicit Dim dbConnection As ADODB.Connection Dim dbRecordset As ADODB.Recordset Public Sub ShowData() 'This just outputs your query in the immediate pane (press Ctrl+G to show it) Dim strConnectionString As String Dim strSQLQuery As String Dim strUsername As String Dim strPassword As String Dim strDatabase As String Dim strServer As String Dim intIndexRef As Integer Set dbConnection = New ADODB.Connection Set dbRecordset = New ADODB.Recordset 'Setup db details strUsername = "username" strPassword = "password" strDatabase = "database" strServer = "SQL-SERVER-HOST" strSQLQuery = "SELECT c.CustomerID, c.CustomerName " & _ "FROM CustomerDetails c, reservationDetails r " & _ "WHERE r.CustomerID = c.CustomerID" 'define connection string strConnectionString = "Provider=SQLOLEDB.1;" & _ "Password=" & strPassword & ";" & _ "User ID=" & strUsername & ";" & _ "Initial Catalog=" & strDatabase & ";" & _ "Data Source=" & strServer 'connect dbConnection.ConnectionString = strConnectionString dbConnection.Open With dbRecordset 'perform query .Open strSQLQuery, dbConnection dbRecordset.MoveFirst 'loop through all returned rows Do 'print out all the field names and thier values For intIndexRef = 0 To (dbRecordset.Fields.Count - 1) Debug.Print .Fields(intIndexRef).Name & ": " & _ .Fields(intIndexRef).Value Next Debug.Print "" dbRecordset.MoveNext Loop Until (.EOF) End With 'close recordset dbRecordset.Close 'disconnect dbConnection.Close Set dbRecordset = Nothing Set dbConnection = Nothing End Sub Option Explicit Dim dbConnection As ADODB.Connection Dim dbRecordset As ADODB.Recordset Public Sub ShowData() 'This just outputs your query in the immediate pane (press Ctrl+G to show it) Dim strConnectionString As String Dim strSQLQuery As String Dim strUsername As String Dim strPassword As String Dim strDatabase As String Dim strServer As String Dim intIndexRef As Integer Set dbConnection = New ADODB.Connection Set dbRecordset = New ADODB.Recordset 'Setup db details strUsername = "username" strPassword = "password" strDatabase = "database" strServer = "SQL-SERVER-HOST" strSQLQuery = "SELECT c.CustomerID, c.CustomerName " & _ "FROM CustomerDetails c, reservationDetails r " & _ "WHERE r.CustomerID = c.CustomerID" 'define connection string strConnectionString = "Provider=SQLOLEDB.1;" & _ "Password=" & strPassword & ";" & _ "User ID=" & strUsername & ";" & _ "Initial Catalog=" & strDatabase & ";" & _ "Data Source=" & strServer 'connect dbConnection.ConnectionString = strConnectionString dbConnection.Open With dbRecordset 'perform query .Open strSQLQuery, dbConnection dbRecordset.MoveFirst 'loop through all returned rows Do 'print out all the field names and thier values For intIndexRef = 0 To (dbRecordset.Fields.Count - 1) Debug.Print .Fields(intIndexRef).Name & ": " & _ .Fields(intIndexRef).Value Next Debug.Print "" dbRecordset.MoveNext Loop Until (.EOF) End With 'close recordset dbRecordset.Close 'disconnect dbConnection.Close Set dbRecordset = Nothing Set dbConnection = Nothing End Sub Edit: Sos.. just noticed you wanted MS Access. Just use a different connection string, something like this: (put the filename to the MDB in strDatabase and dont bother with strServer) (Toggle Plain Text) strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "UserID=" & strUsername & ";" & _ "Password=" & strPassword & ";" & _ "Data Source=" & strDatabase strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "UserID=" & strUsername & ";" & _ "Password=" & strPassword & ";" & _ "Data Source=" & strDatabase Hope that helps. Let me know Matthew Hall www.indarkness.co.uk Last edited by faLLeN; Mar 7th, 2005 at 10:22 AM.

Mar 7th, 2005, 5:40 PM	#4
faLLeN Newbie Join Date: Mar 2005 Location: Hertfordshire, UK Posts: 16 Rep Power: 0	Adding to a text field Hi Well adding to a text field isnt exactly fitting for the purpose, you should really stick the returned rows into a ListView control (or similar). But, as you wish, you can do the following. 1) Add this sub: (Toggle Plain Text) Private Sub AddItem (strText As String) 'Adds strText to a multiline textbox control txtTextBox.MultiLine = True txtTextBox.Scrollbars = 2 '(Vertical scrollbar) MS didnt provide any Enum for this.. helpful.. :S txtTextBox.Text = (txtTextBox.Text & strText & vbCrLf) End Sub Private Sub AddItem (strText As String) 'Adds strText to a multiline textbox control txtTextBox.MultiLine = True txtTextBox.Scrollbars = 2 '(Vertical scrollbar) MS didnt provide any Enum for this.. helpful.. :S txtTextBox.Text = (txtTextBox.Text & strText & vbCrLf) End Sub 2) Replace 'Debug.Print' with 'AddItem' in the previous code example I gave. You'll need a text control called txtTextBox in current scope of the code module you added. Hey presto, your textbox now shows that data output! Kind Regards, Matthew Hall www.indarkness.co.uk www.idhosting.co.uk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2005, 1:40 PM	#3
eggsy Newbie Join Date: Dec 2004 Posts: 5 Rep Power: 0	Great post mate will get on coding. Sorry to drag more out of you, but after performing above what would the code be to display the results in a textField?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic

Embedded SQL queries