Access Reports from MySQL data source

[LittleSmudge]

I have an Access database that is split FrontEnd and BackEnd.

As part of a migration process the back end has been ported onto MySQL

I have no problem using ODBC to get to the data and presenting this one record at a time on Forms.


HOWEVER, How do I take a complete recordset of many records and bind this to a report ?






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[RCPD700]

Since you can successfully retrieve data via ODBC for forms, take the next step and write a query using that linked table as the data source. When you create your report, specify the query as its data source. What you want to do is really no different than what I'm doing with data residing on our "mainframe" in a Datacom/DB database. It works just fine.

Hope this is helpful.

 

[LittleSmudge]

RCPD700 - that the sort of process that I'm looking to achieve.
HOWEVER, when I retrieve data via ODBC it is in the form of a ADODB.Recordset
Yet the RecordSource in the report needs a "Text" string
and I cannot find a way to set the RecourdSource = RecordSET

I think you are saying that I should be able to define a query in the query window that looks at the MySQL data via ODBC - THAT is the bit that I'm missing :- How ?

Once the query is defined I can use it as the reports RecordSource without a problem.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[RCPD700]

If you don't have the MySQL Connector/OBDC installed, you'll need to install it. Quote from the MySql.com site: "MySQL Connector/ODBC (also known as MyODBC) allows you to connect to a MySQL database server using the ODBC database API on all Microsoft Windows and most Unix platforms, including through such applications and programming environments such as Microsoft Access, Microsoft Excel, and Borland Delphi." That driver should provide you with a usable record set.

That said, to then link to your table and use it in a query and report, click on the menu bar File, Get External Data, Link Tables.

You'll get a Link window. At the bottom of that window is a drop-down box labelled "Files of Type" where you select OBDC.

You will then get a "Select Data Source" window. Select whichever tab ("File Data Source" or "Machine Data Source&quot is appropriate for your data source (MySQL), then select the appropriate entry.

At this point, you may get an ODBC login form for that data source. Once connected, you will get a "Link Tables" window showing all the tables available in the data source you selected. Select one or more tables as needed, then click OK. Now you have the table linked to Access and you're ready to create your query using that linked table as the data source.

Presumably you know how to create a report... just select your query in the drop-down box labelled "Choose the table or query where the object's data comes from" in the New Report window.

That should pretty much take care of it.

Hopefully this gets you the results you're after.

 

[LittleSmudge]

Thanks RCPD700

After reading your earlier comments I went home last night and tried it and found it myself - exactly as you describe above - its amazing what you'll find if your given a nudge in the right direction to look for it.

Next question:
If the MySQL table in linked to the Access 'FrontEnd' do these two now act as a File-Server or are they still able to operate in some way as a Client-Server ?
I have one application that I'm going to be working on that has 35,000 records currently in a central BackEnd Access database which is linked to the user's local FrontEnd.
Because Access is a File-Server it takes an age for the form to open becuase the Front end insists on downloading the entire table before it displays the first record.
One major advantage of going to a MySQL BackEnd is to take advantage of the Client-Server structure when the FrontEnd only gets sent the records that match the query ( one record at a time ) making it a lot quicker.
Will I loose this advantage if I link the MySQL table into the Access FrontEnd ?





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

& and for Presumably you know how to create a report...

(no offence taken) but - I'll refer you to the 'Full List of This Forums Experts' for each of the 6 Access boards in Tek-Tips !!!!



It's MySQL that I'm new to, not Access ;-)

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[RCPD700]

With regard to your comment "One major advantage of going to a MySQL BackEnd is to take advantage of the Client-Server structure when the FrontEnd only gets sent the records that match the query (one record at a time ) making it a lot quicker.
Will I loose this advantage if I link the MySQL table into the Access FrontEnd?"

I would think not. I'm running applications against a mainframe database which has somewhere around 800 tables with literally millions of rows and we have experienced few problems. The only time we see the front-end having to do an inordinate amount of work is when the back-end can't quite figure out how to handle a complex query so it ships the various results sets to the front-end and it finishes the processing. I think that is due to some weaknesses in the ODBC communication/translation.

Glad you took no offense at my "Presumably you know how to create a report..." remark. None was meant, it's just that you never really know the experience level of the recipient and I simply don't have time to check "credentials" before responding. I am happy to have some knowledge to share, which for me is an accomplishment having been a mainframe dude for 36+ years now. I am really enjoying the rather large Access development project I'm working on.

Cheers,

Jim

 

[LittleSmudge]

Chears for that Jim.

Over the weekend I've been doing some experiments with an Access front end facing up for a variety of back ends :-
MySQL using OBDC
MySQL using linked tables
Access using linked tables
Access with local table ( in the front end )

and working on one simple table of 50,000 records of 4kbytes per record
over a USB link between the two computers

Measuring the time to add 1000 new records to the table
and
Measuring the time to load a form, present one record of data and close the form ( & repeat that 100 times )

One method, using MySQL and ODBC, of 100 Form open cycles took 1680 seconds ( 1.7 seconds per form open )

Yet with a bit of thought and an extra few lines of code I got it down to 36 seconds ( 0.4 seconds per form open )


Interesting Stuff !


.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[DonQuichote]

Just interested: Do you use pass-through queries for the MySql queries? Pass-through queries let the server parse the queries instead of having MS-Access trying it. It should make a difference in speed.

Best regards

 

[LittleSmudge]

So far I have just been loading forms bound to single tables so I have not used pass-throughs DonQuichote

I have bound the form to a linked table ( Back End in Access or Back End in MySQL ) or I have opened an ADODB.Recordset on the data using ODBC and populated an unbound form from a single record from the recordset.

I will look at trying a Pass-Through Query later today and I'll let you know what I find

( for the record a simple unbound form with no data at all took 12 secords to conplete 100 Open,Close cycles. So in theory the is no practical way of getting below that limit which is a function of the complexity and size of the form itself. )


.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.