MS Access using Iseries File

[GLS400]

Hi All:

Let me start out by stating I'm so new to this I'm not sure I'm even asking the right question.

I have an access database with a linked table to the Iseries. I use sql to validate all employee id's match the Iseries database. I don't want to setup a dsn for everyone who will be using this sql.

I found this code on the web:

Public Sub Copy_Files()
Dim cnAS400 As ADODB.Connection, rsAS400 As ADODB.Recordset
Set cnAS400 = New ADODB.Connection
cnAS400.Open "Provider=IBMDA400; data source=10.2.14.14;", "USERID", "PASSWORD"

blah blah blah

When I run the above it bombs out on the dim statement with "user-defined type not defined"

Am I going in the right direction? Will this approach allow me to get the descrepencies without copying the Iseries file?

Thanks for any insight you can provide

GLS

[DeadManWalks]

Re: MS Access using Iseries File

Hi, this might have to do with the references that are defined to access. I know I have to set this up for VBA or it does not like some of the declares. So you will have to add the references to access.

This is what I do for excel, your will be diff, but I don't do access.

' In order for this to run, you must first have the following
'References selection for the project. This is done by using the
'Menu "Tools" --> References
'================================
' Visual Basic for Applicatons
' Microsoft Excel 12.0 Object Library
' OLE Automation
' Microsoft Office 12.0 Object Library
'IBM AS/400 ISeries Access for Windows ActiveX Object

[littlepd]

Re: MS Access using Iseries File

DMW is correct: you need to reference ADODB.

From the VBA editor, click on the "Tools" menu, then click "References". In the resulting dialog box, select "Microsoft ActiveX Data Objects 2._ Library". If it was me, I would pick the one with the highest version number.

[bryce4president]

Re: MS Access using Iseries File

I think I would go about it just a bit differently.

As I recall, Access has a nice uninstall program. I would uninstall Access from all machines in the company after migrating all the Access DB's to the 400. Problem solved right?

[GLS400]

Re: MS Access using Iseries File

Thank You Gentlemen:

I never would have found that without your guidance.

All works well now... or at least I'm in the Iseries.

The Attachment shows the references that I've chosen.


Thanks Again
GLS

[littlepd]

Re: MS Access using Iseries File

2.0 is the newest version you have? You don't have a 2.7 or 2.8? Even 2.5 would be better than 2.0.