Results 1 to 9 of 9
  1. #1
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13

    MYSQL database linked in MS Access - slow response time

    Hello

    I have successfully linked a MQSQL database (for the moment on localhost) into an Access database.
    But the response time to open a table in Access or to perform a query on that table is very slow.
    Is this an Access issue or MySQL issue ?
    Who can help me to solve this problem.

    Furthermore : can anybody help me with the correct VBA code to open such a linked MySQL table ?

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't know about the response time.

    What do you mean by 'open' table. You should be able to build form or report with the table as RecordSource.

    However, the code to open a linked table should be same as a native table.

    DoCmd.OpenTable "tablename"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13
    To open a table as recordsource when the table is Access native I use following code

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rs1 = db.OpenRecordset("TABLE1", DB_OPEN_TABLE)
    Set rs2 = db.OpenRecordset("TABLE2", DB_OPEN_TABLE)
    rs1.index = "primarykey"
    rs1.MoveFirst
    Do While rs1.EOF = False
    rs2.AddNew

    How should the code be if I want to open a linked MySQL table and be able to add, edit and delete records ?

    Thanks.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This is isn't really opening the table. It is opening a virtual recordset for manipulation in VBA.

    I haven't used MySQL but I expect the code could be the same. Have you tried? What happens? There are other syntax structures for opening recordsets. I've actually never seen the version you posted. What is the procedure for? Why is it setting rs1.index = "primary key".

    Review

    http://allenbrowne.com/ser-29.html

    http://allenbrowne.com/func-dao.html

    http://allenbrowne.com/func-ado.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Yann63 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    13
    Sorry, but if you have never seen such a VBA coding before then you are not quite familiar with VBA. Opening recordsets, using indexes , adding and editing records is done like this.

    Thanks anyway for the links.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have not solved the issue you might try
    - use the linkTable Manager (ODBC)

    - instead of asking for a lot of data to be brought from MySql to Access for processing (which sounds like what you have if things are slow); try to restrict the amount of data being requested.

    - have you considered pass through queries (SQL dialect must be that of the host database --MySQL in this case.

    If you have solved it, could you please post your solution to help others?

    I did find this link that may be useful.
    http://www.access-programmers.co.uk/...d.php?t=204590

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have a lot of code that opens recordsets, adds and edits records, just not the specific syntax you posted. Was not even aware of the Index recordset property. I just use ORDER BY clause in query or SQL statement. But then I don't use MySQL nor any member of the SQL database family.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by Yann63 View Post
    To open a table as recordsource when the table is Access native I use following code

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rs1 = db.OpenRecordset("TABLE1", DB_OPEN_TABLE)
    Set rs2 = db.OpenRecordset("TABLE2", DB_OPEN_TABLE)
    rs1.index = "primarykey"
    rs1.MoveFirst
    Do While rs1.EOF = False
    rs2.AddNew
    You should always declare variables with a Dim statement to prevent Access from mis-managing their data types (for example, if you're using both DAO and ADODB Recordsets in the same database, you can give Access - and yourself - fits by not declaring which Recordset type you're wanting to use). And, while your way of getting the database ("Set db = DBEngine.Workspaces(0).Databases(0)") is perfectly valid, if you're only connecting to the current database, you can also use "Set db = CurrentDb()" which is easier to type :P

    Also, I generally try to stay away from Table type Recordsets since they can only be used to open a specific Table. I generally use Dynaset so I can use a SQL Query to access just the Fields I want (and to access Fields from multiple Tables in a single Recordset).

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Thanks, Rawb. Now I have a better idea of why the syntax I was taught is preferred.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. MS Access Response Time Calculation Help
    By qcjustin in forum Programming
    Replies: 9
    Last Post: 12-13-2012, 10:58 AM
  2. Replies: 3
    Last Post: 06-29-2012, 08:58 AM
  3. Replies: 5
    Last Post: 05-16-2012, 12:48 AM
  4. Replies: 2
    Last Post: 03-29-2012, 07:45 AM
  5. Slow Database Response
    By Nixx1401 in forum Access
    Replies: 3
    Last Post: 02-25-2010, 11:09 AM

Tags for this Thread

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