Hi all,
I am new to this forum and relatively new to Access.
I have created an Access database and I am importing 13 tables via ODBC every morning. I am importing these tables instead of linking to them b/c if I link to them the queries are VERY SLOW.
Is there a way to automate the import process thru VBA?
Thanks!
Your AS400 drivers may require Driver,Provider or other driver specific items in your connection string.
Here's a couple for SQL Server as samples
Standard Security:
"Driver={SQLServer};Server=Your_Server_Name;Databa se=Your_Database_Name;Uid=Your_Username;Pwd=Your_P assword;"
Trusted connection:
"Driver={SQLServer};Server=Your_Server_Name;Databa se=Your_Database_Name;Trusted_Connection=yes;"
If you go back to your original way of linking the AS400 tables but change your process to append vice recreate the tables each time. The MakeTable queries are very slow. Don't delete the tables each day - just clear the data and reload them. You will find this much quicker.
Function getMyData()
'repeat for all data tables
Docmd.RunSQL "DELETE tblData1.* from tblData1;"
Docmd.OpenQuery "qryApdData1
End Function
Play around with your processes and queries - I had a process that run a monthly finance report that took 8 hrs to run (1600 page report). I dropped it to 2 hrs by getting rid of all of the make table queries and breaking up some of the more complex single queries into multiple queries.