ODBC connection to AS400 in Access VBA

Posted on 2003-03-14
Medium Priority
Last Modified: 2007-12-19

I need to be able to create a connection through coding VBA. I will be putting the code in a module, but I do not know how to do this ( how to make the connection)

The ODBC32 has been setup with the AS400 source, that is there is an AS400 provider there.

I can import the table I need through the insert and then import table command from the menu, but I would like to do this coded.

Now how do I make a connection to this machine source ? I want to be able to update an imported table every time my access application opens, I cannot have it linked.

Please help me, if you need further details let me know.

Thank You.
Question by:originalrobby
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 18

Expert Comment

ID: 8137696
You will have to have the AS400 client installed on the PC Access is running on.  This will also install the proper drivers needed to create an ODBC connection.
LVL 18

Expert Comment

ID: 8137724
Once you have the ODBC connection created on the machine, right click on the tables tab page in the database window.  Select 'link tables'.  Go to the 'Files Of Type' and select 'ODBC Databases'  Look at the items in tabl 'MachineData Sources' for the ODBC connection created earlier.

And off you Go!  :-)

Expert Comment

ID: 8137852
I hope that this helps

to import a table through vba you would use the TransferDatabase method.  Basically you put in the constraints by code that you would normally put in the import table that you have been doing through the menu.

DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

if you want to update the database when the application opens you could use the form load event..

Private Sub Form_Load()
  '**place code here to import table**
End Sub
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8153015
Mr. Mika,

Ok, I will award u the points if u tell me please, this transferdatabase method... will it keep the AS400 table in my database and will only import it when opening my database, or will it actually do imports everytime I open my database ?

Also, at the momen I have a linked table to the AS400, does this get updated everytime I open my database, does it update when opened ? If it does it must be a live connection ?

Robert Mezei
Programmer / Analyst

Accepted Solution

mr_mika earned 400 total points
ID: 8153115
The transferdatabase method will import the table into the access database every time you call the transferdatabase method...in this case everytime you load the form.

If you have a linked table then it is basically a live connection.

I hope this helps.

Good luck

Expert Comment

ID: 8157548
I have a problem when I try to connect as400 table with ACCESS2000.
I have installed necessary router as400 and driver nsodbc, so i have proceded to import
as400 table in access2000 file's choosing nsodbc file created in Administration of ODBC in winnt control panel .
In the access FORM i have create a button with code

DoCmd.TransferDatabase acLink, "Database ODBC", _
    & "DATABASE=QGPL", acTable, "QGPL.ANAGFAR", "dboAutori"

I receive the RUNTIME ERROR 3000 and error code -7756.

Can I know what about this error ?

FAbrizio Visconti

Author Comment

ID: 8159630
Well wasnt that an easy 100 points! :() Thanks friend

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question