Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 21st, 2005, 12:48 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default Backend Issues

I’m using mySQL 5.0.11 as a back end for this Access project I’ve been working on. Trying to complete it this week too.

Anyway, I just learned that multi-table Views in mySQL that use Outer Joins are not updatable. What a bunch of %%#*$^!

This really bums me out, as all my forms are based on views, many of them multi-table views that use outer joins.

So I’m guessing I have two choices: Keep the views on the client side as Access queries, or change my forms’ Record Source properties to do the joins themselves.

So what I’m wondering if one approach is faster than the other. Also, is there a property that might point my Access queries directly to the backend tables, rather than to the ODBC links to the backend tables (skipping the middle-man in effect)?

Thanks

Aaron


Reply With Quote
  #2 (permalink)  
Old August 21st, 2005, 02:39 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've also noticed that the realy SLOOOOOOWWW parts of my application, now that I've moved the data to MySQL, are not the ADO commands, but the basic Find commands using the Form, e.g.

DoCmd.FindRecord ProjectNumber, , , , , acSearchAll, , acCurrent

Also, any time I do a Find on the form using ctrl-F are painfully slow. This was not the case when it was in Access, and has not been the case when I've used SQL Server as the backend (not an option this time, unfortunately). It's really wierd because the actual query results come back from mySQL very, very fast.

Any help would be forever appreciated. Thanks.

Aaron

Reply With Quote
  #3 (permalink)  
Old August 23rd, 2005, 11:23 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It sounds like it could be an index issue with the mySQL tables on the second issue. Perhaps?

You should also be able to create a direct connection to a dbms without a DSN. DSN's are solid, but slower than a direct connection. Have you ever done this with SQL Server? If not, do a search in VBA for data provider statements. I am not sure what mySQL is expecting in the string. You want the article "Using providers with ADO." Here is the syntax for SQL Server from that article. Note the lack of a reference to a DSN.:

"Provider=SQLOLEDB;Data Source=serverName;Initial Catalog=databaseName; User ID=userName;Password=userPassword;"

HTH


mmcdonal
Reply With Quote
  #4 (permalink)  
Old August 26th, 2005, 10:50 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks. I got a lot of information from this page here:

http://support.microsoft.com/default...b;en-us;286222

Thank you though.

Aaron

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
connection string issues, web.config file issues kaliaparijat ASP.NET 2.0 Professional 1 June 12th, 2008 08:07 AM
Path to database backend Clive Astley Beginning VB 6 3 January 12th, 2007 01:29 AM
Backend security simmyboy Access 3 August 22nd, 2005 02:42 AM
Linking to backend Clive Astley Access VBA 6 April 1st, 2004 01:35 PM
Backend as a constant Steven Access 1 June 29th, 2003 11:49 PM



All times are GMT -4. The time now is 04:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.