Link to home
Start Free TrialLog in
Avatar of originalrobby
originalrobby

asked on

ODBC connection to AS400 in Access VBA

Hi,

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.
Avatar of 1William
1William

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.
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!  :-)
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
Avatar of originalrobby

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of mr_mika
mr_mika

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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", _
    "ODBC;DSN=pippo2;UID=PCSUPPORT;PWD=PCSUPPORT;LANGUAGE=italiano;" _
    & "DATABASE=QGPL", acTable, "QGPL.ANAGFAR", "dboAutori"

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

Can I know what about this error ?

Saluti
FAbrizio Visconti
Well wasnt that an easy 100 points! :() Thanks friend