Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

VB,Access,SQL Data controlor hard code?

I'm trying to create a VB front end for an access database of book titles.  I have a text box that accepts SQL code and searchs the Database and displays the correct records in a DB Grid.  I would like to be able to run one regular SQL Query

SELECT Titles.[Title] ,Titles.[Year Published] FROM Titles WHERE [Year Published] > YEAR

where year is a differnet value entered in a seperate text box.  I guess my immediate question is can I set up this SQL statement in code somewhere instead of entering it in the text box.  Then I would like to enter a value (year variable) that would execute my query for that particular year

suggestions?


Private Sub cmdExecute_Click()

    On Error GoTo CorrectSQL
   
    Data1.RecordSource = "" & txtSQLCode & ""
    Data1.Refresh

    On Error GoTo 0
    Exit Sub
   
CorrectSQL:
    If Err.Number = 3061 Then
        nresponse = MsgBox("Check your SQL Code!", vbOKOnly, "Error!")
        txtSQLCode.SetFocus
        txtSQLCode.SelStart = 0
        txtSQLCode.SelLength = Len(txtSQLCode.Text)
    End If
   
End Sub

Private Sub cmdQuit_Click()
    End
End Sub


Private Sub Form_Load()

    Data1.DatabaseName = "C:\Program Files\Microsoft Visual Basic\Biblio.MDB"
    Data1.RecordsetType = 1
    Data1.RecordSource = "Titles"
   
    Data1.Refresh
   
End Sub


Private Sub txtSQLCode_Change()
Dim year As Integer
year = 1992
    If txtSQLCode <> "" Then
        cmdExecute.Enabled = True
    Else: cmdExecute.Enabled = False
    End If
End Sub



0
john_d@winmill.com
Asked:
john_d@winmill.com
1 Solution
 
dirkmartinCommented:
The best way of doing this is to create your query right over in Access, in the Query QBE grid.  Make Year a parameter.  I don't know if you've worked with parameter queries or not, but this is bar far the best way to do it.

Make Year a parameter over in Access.  Run the query to make sure that Access asks you for the year each time the query is run. (look up parameter queries in Access).

Then go back to VB and do something like this:

Dim MyDB As Database, MyQuery As QueryDef, MySet As Recordset
Set MyDB = Workspaces(0).OpenDatabase("BIBLIO.MDB")
Set MyQuery = MyDB.OpenQueryDef![NameOfMyQuery]      ' Open QueryDef.MyQuery.Parameters![Year]] = Textbox.text      ' Set parameter.
' Create snapshot-type Recordset.
Set MySet = MyQuery.OpenRecordset(dbOpenSnapshot)

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now