Import ODBC tables automatically?

[abrown728]

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!

[Minion]

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!

Couple questions about your situation:

1. What type of system is hosting your orginal tables (ie. SQL Server, Oracle, etc)?
2. Currently are you using the import wizard or have you written code to the imports for you?
3. Are the imported tables replacing the existing day's table or are you appending the values to the table?

There might be more information that is needed, but I'll know more when the above is answered. And so you're not lefted with more questions than answers I will tell you that there are a couple of methods to do what you want and to automate it down to a press of a button (or a little more). However, I (and those much more skilled than I) will need more information on the situation so that we can provide the best answer for you.

Hope this helps.

- Minion -

[abrown728]

Here are the answers:
1. My original tables are in an AS400 system and I have an ODBC to connect to them.
2.Currently I am going to File-Get External Data-Import-Machine Data Source and importing them all at once daily.
3.No, every day I delete the tables and then reimport them.

Thanks for your help.
Alyssa

Couple questions about your situation:

1. What type of system is hosting your orginal tables (ie. SQL Server, Oracle, etc)?
2. Currently are you using the import wizard or have you written code to the imports for you?
3. Are the imported tables replacing the existing day's table or are you appending the values to the table?

There might be more information that is needed, but I'll know more when the above is answered. And so you're not lefted with more questions than answers I will tell you that there are a couple of methods to do what you want and to automate it down to a press of a button (or a little more). However, I (and those much more skilled than I) will need more information on the situation so that we can provide the best answer for you.

Hope this helps.

- Minion -

[ADezii]

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!

To automate the Import Process through VBA would require code similar to the following (for a 1 Table Import):

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "Destination Table Name", False, True
 

1. acImport - Data Transfer Type.
2. ODBC Database - Specific Database Type.
3. "ODBC;DSN=Y our DSN Name;UID=Your UserID;PWD=Pass word;LANGUAGE=u s_english;" _
   & "DATABASE=Datab ase Name" - Name of Database and qualifiers.
4. acTable - Type of Object you are Importing.
5. Source Table Name - Name of the Source Table being Imported.
6. Destination Table Name - Name of the Destination Table being created.
7. False - Both the Structure of the Table and its Data are being Imported.
8. True - The UserID and Password are both stored in the Connection String which prevents repetitive Log-ins each time the Table is accessed.

P.S. - Most Arguments to the TransferDatabas e Method are Optional. Reference this Method in the Help Files for specifics, and let us know how you make out.

[abrown728]

Thanks. Here is what I have. When I run it I get this error:
Run Time Error 3151
ODBC Connection to 'barry Mfg' failed

I tried doing it manually and was able to.

Thanks, Alyssa

Function ODBC()
 
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=barry Mfg;LANGUAGE=us  _english;" _
& "DATABASE=SalesReportingDatabase", acTable, "QS36F.ARDETAIL", "TEST", False, True
 
End Function
 

[jaxjagfan]

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={SQLSer ver};Server=You r_Server_Name;D atabase=Your_Da tabase_Name;Uid =Your_Username; Pwd=Your_Passwo rd;"

Trusted connection:
"Driver={SQLSer ver};Server=You r_Server_Name;D atabase=Your_Da tabase_Name;Tru sted_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 "qryApdData 1
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.

[ADezii]

Thanks. Here is what I have. When I run it I get this error:
Run Time Error 3151
ODBC Connection to 'barry Mfg' failed

I tried doing it manually and was able to.

Thanks, Alyssa

Function ODBC()
 
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=barry Mfg;LANGUAGE=us  _english;" _
& "DATABASE=SalesReportingDatabase", acTable, "QS36F.ARDETAIL", "TEST", False, True
 
End Function
 

1. The True Argument indicates the UserID and Password are both stored in the Connection String. Is this the case and are they correct?
2. I'm not sure on this one, but the space in the DSN Name may be causing a problem.
3. If you can manually make the Connection, then try opening the DSN (*.udl) with Notepad and look at the Connection String Parameters to see what may be missing.

[jandi01]

I have tried to run the code but it comes up saying it cannot find the destination table.
I need it to create the tables each time it has run