Connect to Remote MySQL from VBA (Access 2007)

[storas23]

Hi Folks

I need to query a remote mySQL database to tie in some stats with our main Access 2007 database and produce a report. I have tried linking the remote tables and querying them that way with limited success but Access keeps crashing. I therefore would prefer to connect to the remote DB using code and get my stats that way. Any ideas how to do this?

Have found solutions for previous versions of Access but not 2007.

Thanks in advance.

 

[Banana]

I don't have Access 2007, but I do use MySQL with 2003.

I'll need you to elaborate a bit on the part about crashing.

Have you configured the options. I know one good example would be if a BIGINT was used, which Access cannot support, but MyODBC does provide an option for modifying this. Have you already read the documentations on MySQL's website about linking Access to MySQL and the pitfalls to avoid?

 

[storas23]

Thanks

It seems to crash when carrying out fairly simple queries. I don't know if it's got anything to do with our broadband connection as our upload bandwidth is maxed out most of the time!!

I haven't read the documentation yet. I foolishly promised a test report by tomorrow!! (don't think it's gonna happen!)

 

[Banana]

Well, that's software development for you. Far easier to promise pies in sky than to come up with one, unfortunately.

To verify whether this is a broadband connection issue, try to use a local server (install one if you don't have one) to see how it behaves. Next, try and just connect but don't run any queries. See if you can view the tables (by opening it from database windows). If there's a problem with this, there will be an error message.

 

[georgedwilkinson]

storas,

If you just want to get moving on using VBA, either look up ADO or DAO automation. You should be able to connect to a SQL database from your Access database using either. And the support is built right in to Access.