Automatically updating tables prior to running a report

[icodian]

I have 2 tables that I must retrieve data from via an ODBC driver. The linking between these 2 tables has caused some major performance issues. The tables are an AP Invoice table and an AR Invoice table. These are related through a Sales Order Number field (both tables have this field). Please don't ask for details as to why these are related, just accept it because it's way too complicated to get into.

Anyway, all I need to do is print a report with fields from both tables. I am more of a Crystal Report programmer so I initially did this through Crystal using the ODBC driver. There were only 50 records in my tests and it took almost an hour to get anything at all. Sometimes it simply locked up.

So, I thought I'd write a simple SQL query to get the data. I did this through WinSQL and it had the same result. Therefore, I figured it must have something to do with the ODBC driver. I am not able to create new indices because of its limitations either.

I imported the tables directly into Access, linked the tables, and ran a query. BANG...2 seconds later I had all the information. I created an Access report (since I am sick of Crystal for today) and it looks great. Now my problem is, how do I update these imported tables each time the report is run?

I tried simply using the "Link tables" feature pointing directly to the database but that took me back to 2 hours of waiting. So, the only way I've discovered to quickly get the data is through the importing the tables.

I have created a form with parameters to enter. These parameters are used in the query that the report is based off of.

I would like the 2 tables to be re-imported using the ODBC driver whenever the FormLoad event is triggered. Can someone help me out the VBA code to do this? I am a little familiar with VB but have not had any luck to this point.

I really appreciate it.

 

[KenReay]

Hi

You say:-

"I imported the tables directly into Access, linked the tables, and ran a query. BANG...2 seconds later I had all the information. I created an Access report (since I am sick of Crystal for today) and it looks great. Now my problem is, how do I update these imported tables each time the report is run?"

Do you do the import via File \ GetExternal Data ?

When you say 'linked' do you mean that you created a query with a join between the two tables?,

Which version of Access are you using?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[icodian]

Hi Ken,

Thanks for the response. Let's see if I can answer your questions here.

Yes, I imported the files using an ODBC driver through the File->Get External Data->Import functionality. When using File->Get External Data->Link Tables, the query ran for an hour before I got tired of waiting and ended the task. Importing the tables and running the query only takes a few minutes.

Yes. I linked the tables through Tools->Relationships by linking the SalesOrderNumber field. No referential integrity was used. When I created the query, the tables appeared as joined as in the Relationships dialog. In the SQL view, they appear as an INNER JOIN.

I am using Access 2002 on WinXP Pro.

Thanks again and I'll look forward to hearing from you.

 

[KenReay]

hi

sorry,not sure I can offer any significant suggestion, except have you tried creating an ADO connection and reading in the data via that? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[cmmrfrds]

What database management system are the linked tables in? Oracle? Sql Server? other?

Is the join only between the linked tables or is there a local Access table involved in the join?

What version of Access?

 

[icodian]

The data is coming out of MAS 90. The database is not relational and normally, these 2 tables would never be linked. A small customization allows you to enter the Sales Order Number manually for the AP Invoice. This field is a reference back to the Sales Invoice table that stores the actual Sales Order Number. No 'official' linking occurs.

Technically, when I import the tables, they are local to Access at that point. I haven't been using the Link Table option in Access because it is sooooo sloo

http://wwwww.

Yet, there are no other tables involved if that's what you are asking.

I am using Access 2002 on WinXP Pro.

Thanks!

 

[icodian]

Maybe I should be posting this on the VB forum. But what I'd really like to know is:

Is there is a way through VBA code to import tables just like the File->Get External Data->Import function does?

If so, this would be a successful solution, I believe...

Thanks again!

 

[mp9]

Importing a table from another Access db in VBA is as follows:

DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\temp\sourcedb.mdb", acTable, "tblSourceTableName", "tblDestinatinTableName", False

http://www22.brinkster.com/accessory/

 

[icodian]

Thanks for the suggestion.

Will the TransferDatabase method work for transferring via an ODBC driver as well?

 

[Nickels]

yes, it can be used with odbc also. here is an example:

DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=silentMAS90", acTable, "IM1_InventoryMasterfile", "IM1_InventoryMasterfile", False

the third argument can also be used to send id and password information to the database:

"ODBC;DSN=silentMAS90;uid=xxxx;pwd=xxxx"