• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3383
  • Last Modified:

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.
0
originalrobby
Asked:
originalrobby
  • 2
  • 2
  • 2
  • +1
1 Solution
 
1WilliamCommented:
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.
0
 
1WilliamCommented:
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!  :-)
0
 
mr_mikaCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
originalrobbyAuthor Commented:
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
0
 
mr_mikaCommented:
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
0
 
viscontiCommented:
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
0
 
originalrobbyAuthor Commented:
Well wasnt that an easy 100 points! :() Thanks friend
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now