Create Linked Table to AS/400

I am trying to create a linked table from Access to our AS/400 database. I
am basing my macro on an Excel macro that uses a SQL call to download data
from the database. My code to build a table using ADOX is below"

Private Sub CreateAS400LinkedTable()

Dim adoCn As ADODB.Connection
Dim adoCat As ADOX.Catalog
Dim adoTbl As ADOX.Table

Set adoCat = New ADOX.Catalog
Set adoTbl = New ADOX.Table


'Create Link...
Set adoCn = CurrentProject.Connection

Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = adoCn

Set adoTbl.ParentCatalog = adoCat
adoTbl.Name = "LinkTable"

adoTbl.Properties("Jet OLEDB:Link Datasource") = "Data
Source=OSK2.OSK.CORP.TRUCK;"
adoTbl.Properties("Jet OLEDB:Link Provider String") =
"Provider=IBMDA400;Force Translate=0"
adoTbl.Properties("Jet OLEDB:Remote Table Name") = "CPJDDTA81.F4105"
adoTbl.Properties("Jet OLEDB:Create Link") = True

' Append the table to the tables collection
adoCat.Tables.Append adoTbl

MsgBox "Link Created..."

End Sub


When I run the code, I get an error "Could not find installable ISAM" when
it tries to append the table. The Provider and Data Source properties work
when called using a SQL connection and string, so how can I use that
information here?

Thanks,
Matthew Pfluger

hi Matthew,

Matthew Pfluger wrote:
When I run the code, I get an error "Could not find installable ISAM" when
it tries to append the table. The Provider and Data Source properties work
when called using a SQL connection and string, so how can I use that
information here?
Use DAO:

CurrentDb.TableDefs.Append _
CurrentDb.CreateTableDef(ADestinationName, 0, _
ASourceName, AConnection)

ADestinationName is the name of the linked table in Access, ASourceName
of your table on your AS/400 including the schema prefix, AConnection is
the ODBC connection string to your AS/400 database.

See

http://connectionstrings.com/?carrier=as400

for the correct connection string.


mfG
-- stefan --

Thanks for your reply. I went to that site, copied the appropriate string,
and ran the new code, but I still get the error "Could not find installable
ISAM (Run-time error '3170')".

Any other suggestions? Thanks,
Matthew Pfluger

"Stefan Hoffmann" wrote:

hi Matthew,

Matthew Pfluger wrote:
When I run the code, I get an error "Could not find installable ISAM" when
it tries to append the table. The Provider and Data Source properties work
when called using a SQL connection and string, so how can I use that
information here?
Use DAO:

CurrentDb.TableDefs.Append _
CurrentDb.CreateTableDef(ADestinationName, 0, _
ASourceName, AConnection)

ADestinationName is the name of the linked table in Access, ASourceName
of your table on your AS/400 including the schema prefix, AConnection is
the ODBC connection string to your AS/400 database.

See

http://connectionstrings.com/?carrier=as400

for the correct connection string.


mfG
-- stefan --

hi Matthew,

Matthew Pfluger wrote:
Thanks for your reply. I went to that site, copied the appropriate string,
and ran the new code, but I still get the error "Could not find installable
ISAM (Run-time error '3170')".
Then check your AS/400 driver. Is it correctly installed? Can you create
a working *.udl file?


mfG
-- stefan --

Stefan Hoffmann wrote:
hi Matthew,

Matthew Pfluger wrote:
Thanks for your reply. I went to that site, copied the appropriate
string, and ran the new code, but I still get the error "Could not
find installable ISAM (Run-time error '3170')".
Then check your AS/400 driver. Is it correctly installed? Can you
create a working *.udl file?

I work against an ISeries (formerly AS400) quite a bit and I have never been
able to get the DSNLess connection strings found on that web site to work
(for creating a table link). In fact I have never been able to get any sort
of DSNLess connection to work against an ISeries database. I always end up
using a DSN.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

hi Rick,

Rick Brandt wrote:
I work against an ISeries (formerly AS400) quite a bit and I have never been
able to get the DSNLess connection strings found on that web site to work
(for creating a table link). In fact I have never been able to get any sort
of DSNLess connection to work against an ISeries database. I always end up
using a DSN.
I never had to deal with an AS400, but I had once the same problem with
an Informix driver...

mfG
-- stefan --

I was able to make it work after pouring through the IBM help files. I ended
up using a System DSN and setting its translation property to Convert binary
data (CCSID 65535) to text. That did it!

Thanks for the help,
Matthew

"Stefan Hoffmann" wrote:

hi Rick,

Rick Brandt wrote:
I work against an ISeries (formerly AS400) quite a bit and I have never been
able to get the DSNLess connection strings found on that web site to work
(for creating a table link). In fact I have never been able to get any sort
of DSNLess connection to work against an ISeries database. I always end up
using a DSN.
I never had to deal with an AS400, but I had once the same problem with
an Informix driver...

mfG
-- stefan --