Results 1 to 3 of 3
  1. #1
    hcruz is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2011
    Posts
    4

    Use VBA to call stored SQL from table

    I am trying to store my queries in a table using some code from the Access Cookbook by Getz, Litwin & Baron.

    tblQueries has the following fields:

    QueryID - AutoNumber, Primary Key
    QueryName - text
    QueryDescription - text


    SQLText - Memo
    DateCreated - Date/Time


    The procedure I am using to get the SQL string is:

    Public Function acbGetSavedQuerySQL(strName As String) As String

    ' Returns a SQL string from tblQuerySQL
    ' In : strName - name of query to retrieve
    ' Out : SQL string

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblQueries")

    rst.Index = "PrimaryKey"
    rst.Seek "=", strName

    If Not rst.NoMatch Then
    acbGetSavedQuerySQL = rst!QrySQLText
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    End Function

    The event I am using to run the SQL is:

    Private Sub cmdGetQryShiningStarBirthdate_Click()

    DoCmd.RunSQL acbGetSavedQuerySQL("Test")

    End Sub

    The error I am getting is:

    Data type conversion error and when I debug it highlights this line of code:
    rst.Seek "=", strName

    I am still learning VBA and have been unable to problem solve this one on my own.

    Thank you to anyone who can provde some help!

    Heather

  2. #2
    Join Date
    Apr 2011
    Posts
    18
    I dont know enough of the seek method to tell you why its not working. Personally I use the .FindFirst method, which would look like this, just after the line where you open the recordset:
    Code:
    rst.FindFirst "QueryName= '" & strName & "'"
    If rst.NoMatch then
    Msgbox "No sql string found in table"
    else
    acbGetSavedQuerySQL = rst!SQLText 
    End If

  3. #3
    hcruz is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2011
    Posts
    4
    Thank you for your reply. I haven't not tried the FindFirst method yet, as I did discover that the problem with my specific code was that the PrimaryKey field I was using in my table was not the PrimaryKey field I was "searching" by. Easy fix once I noticed that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help Writing VBA and call to query
    By brianb in forum Programming
    Replies: 7
    Last Post: 03-09-2011, 09:42 AM
  2. combo box selection stored in table
    By combine21 in forum Forms
    Replies: 1
    Last Post: 12-02-2010, 09:57 PM
  3. Assign and call variable from table
    By smikkelsen in forum Access
    Replies: 7
    Last Post: 04-01-2010, 09:38 AM
  4. how to call a sub procedure?
    By dollygg in forum Access
    Replies: 1
    Last Post: 08-18-2009, 05:10 AM
  5. Call Excel Data into Access table
    By jiguvaidya in forum Import/Export Data
    Replies: 0
    Last Post: 09-15-2008, 04:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums