Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: mysql query results in access

    I've been shifting my Access database to use a mysql back end and have been trying to solve this problem:

    I create my SQL queries based on input from the user, execute a SELECT query, and then display the results in a temporary query - but the results can't be sorted or the display of the data otherwise adjusted.

    My latest approach was to create a "generic" query in Access whose records would be updated to the SQL results and then displayed, but I haven't had much success with that.

    This is what I've been using (VBA) to execute the SQL statement and return the records in a temporary query (ContactSQL and ContactTitle are functions that generate the SQL statement and descriptive text of the query, respectively):

    Dim dbs As Database
    Dim rs As Recordset
    Dim qdf As QueryDef

    strSql = ContactSQL
    strTitle = ContactTitle

    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset(strSql, dbOpenDynaset)

    With dbs
    Set qdf = .CreateQueryDef(strTitle, strSql)
    DoCmd.OpenQuery strTitle
    .QueryDefs.Delete strTitle
    End With
    dbs.Close
    qdf.Close

    Any suggestions on how to best present the data in Access would be greatly appreciated - thanks in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can create permanent objects (Linked Tables or Pass-through Queries) in your Access database, then use them as you would use the corresponding genuine Access object (Table or Query):
    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub CreatePassThroughQuery(ByVal Connect As String, ByVal RowSource As String, ByVal QueryName As String)
    '
    ' Creates a permanent (i.e. visible in the Database window) pass-through query in the current Database.
    '
    ' Connect: The connection string to the database server. Ex. for a SQL Server:
    ' -------
    '               "ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
    '
    '          For connecting other database servers, see: http://www.connectionstrings.com/
    '
    ' RowSource: The name of a Stored Procedure that returns a rowset or a SELECT SQL Statement that is
    ' ---------  valid in the SQL syntax of the server. To open a Table or a view, use:
    
    '               "SELECT * FROM <TableName>;" or "SELECT * FROM <ViewName>;",
    
    '            You can also create an attached table.
    '
    ' QueryName: The name of the Query to be created in the current Access database.
    ' ---------  If QueryName alreadyexists in the current database, it will be deleted and re-created.
    '
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        If DCount("*", "MSysObjects", "Name='" & QueryName & "'") Then DoCmd.DeleteObject acQuery, QueryName
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef(QueryName)
        qdf.Connect = Connect
        qdf.SQL = RowSource
        dbs.QueryDefs.Refresh
        Set qdf = Nothing
        Set dbs = Nothing
        
    End Sub
    
    Public Sub CreateLinkedTable(ByVal Connect As String, ByVal RowSource As String, Optional ByVal TableName As String)
    '
    ' Creates a permanent (i.e. visible in the Database window) linked table (attached table) in the current Database.
    '
    ' Connect: The connection string to the database server. Ex. for a SQL Server:
    ' -------
    '               "ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
    '
    '          For connecting other database servers, see: http://www.connectionstrings.com/
    '
    ' RowSource: The name of an existing Table or View (Query in MySQL terminology ?) in the SQL Server database.
    ' ---------
    ' TableName: The name of the Table to be created in the current Access database.
    ' ---------  If TableName is omitted (or is a zero-lenght string), the linked table will be
    '            created with the same name as the source table specified in RowSource.
    '            If TableName already exists in the current database, it will be deleted and re-created.
    '
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        If Len(TableName) = 0 Then TableName = RowSource
        If DCount("*", "MSysObjects", "Name='" & TableName & "'") Then DoCmd.DeleteObject acTable, TableName
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef(TableName)
        tdf.Connect = Connect
        tdf.SourceTableName = RowSource
        dbs.TableDefs.Append tdf
        dbs.TableDefs.Refresh
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    There are several mechanisms that can be used to record the creation of such "linked" objects and delete them when the database is closed.
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    thanks for the code! i think that may achieve the ends i need, make maintaining the linked tables easier, and while i'm at it i'm going to switch over to a dsnless connection to streamline installation on multiple machines.. thanks again!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome !
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •