ISAM error when trying to create tablelink

[Guest]

Hi,

I'm using Access 2003 and trying to create a linked table from an AS400
database.

Here's what I have that's not working.
===========
Dim db As Database
Dim tdf As TableDef
dim str as string

str = "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"

Set tdf = db.CreateTableDef("TEST")
tdf.Connect = str
tdf.SourceTableName = "TBL1"
db.TableDefs.Append tdf
===========

When I get to the last line, I get the error "could not find installable ISAM"

Now, two things
1) if i use the same connectiong string (str) and open up a recordset object
to TBL1, I can successfully get to that table, so that tells me the
connection string is okay.
2) If I put in the wrong password in the connection string, tdf.connect
doesn't give any error.

Okay, any ideas how I can fix this?

Thanks!
-Mark

 

[Douglas J. Steele]

I'm assuming that your recordset is using ADO.

What you've got appears to be an Ole DB connection string (actually, the
MSDASQL Ole DB provider is considered obsolete by Microsoft: for
replacements, see what Carl Prothman has at
http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).

When creating linked tables and/or pass-through queries, your only choice is
ODBC. Since you apparently have a DSN created, try:

str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"

 

[Guest]

Hi Doug,

So, I'm pretty much a newbie with this and I'm not sure I'm following your
suggestion correctly. I updated str so
str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"

where the character between the ODBC and DSN is a pipe (it copies as the
letter 'l'). I didn't change anything else in the code, but am still getting
the same error. Was there something else I needed to change?

Oh, and yes, i'm using ADO.

Thanks for taking my call!
-Mark

 

[Douglas J. Steele]

I must have made a typo. That was supposed to be a semi-colon between ODBC
and DSN=.

Sorry about that.

 

[Guest]

Hi Doug,

Tried that change and unfortunately still not working. Here's the full sub,
with the password and user removed to protect the innocent. I tried "ODBC;"
and "ODBC=;" and first one gave me another error, but the second one worked.
I'm able to see the rs recordset, but appending the tdf tabledef causes that
same ISAM issue.

And thank you so much for your help.
-Mark


Sub ADOConnect()
Dim rs As New ADODB.Recordset
Dim db As Database
Dim conn As ADODB.Connection
Dim i As Integer, j As Integer
Dim str As String
Dim tdf As TableDef

Set conn = New ADODB.Connection
str = "ODBC=;DSN=Strategy;USER=;PWD="

conn.ConnectionString = str
conn.Open

Set db = CurrentDb
Set tdf = db.CreateTableDef("TEST")
tdf.Connect = str
tdf.SourceTableName = "PINSQUERY"

'rs opens okay with the connection.
rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic, adLockReadOnly
Debug.Print rs(0).Name
Debug.Print rs(0).Value

'errors out here.
db.TableDefs.Append tdf

End Sub

 

[Douglas J. Steele]

Try creating a linked table through File | Get External Data | Link Tables.
Assuming that works, examine the Connect property of the table you created.

 

[Jamie Collins]

What you've got appears to be an Ole DB connection string (actually, the
MSDASQL Ole DB provider is considered obsolete by Microsoft: for
replacements, see what Carl Prothman has athttp://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProvide...).

Note than 'deprecated' (I assume you have no citation for 'obsolete')
in context probably means 'deprecated in MDAC' and IMO does not
translate to 'do not use'. Consider:

Windows DAC/MDAC Deprecated Components
http://msdn2.microsoft.com/en-us/library/ms692882.aspx
ODBC Provider (MSDASQL)
"You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to
it, will not be available on 64-bit, and will not be accessible from
the OLE DB NET Data Provider."

Note the article stops short of saying that MSDASQL is no longer
supported or that MSDASQL will not be supported in its current guise
in the future. This is because SQL DBMSs (and other data sources)
which have a native ODBC driver far outnumber those with a native OLE
DB provider and OleDb is alive and well in ADO.NET.

Also consider that the same article explicitly states, "Microsoft has
deprecated the Microsoft Jet Engine".

Jamie.

--

 

[Douglas J. Steele]

Take up your quibbles with Carl, as he's the one who called it obsolete, not
me: I merely quoted him.

 

[Jamie Collins]

Take up your quibbles with Carl, as he's the one who called it obsolete, not
me: I merely quoted him.

Friendly advice: please make it clear when attributing a claim to
someone else: it looked to me like you were using his site to
corroborate a claim *you* were making. I would suggest putting the
quoted text in quotation marks; when quoting multiple lines I tend to
do something like:

Quoted text goes here

....and here

[Unquote]

Regardless of source, it was you who introduced the issue into this
thread so I think I am correct in redressing the point by replying to
*you* e.g. if I sent a private email to the site's webmaster, how
would that help the OP make an informed decision about MSDASQL?

Jamie.

--

 

[Guest]

My apologies for the late response. I had a high priority project due...

I was successfully able to link and tried to copy the description in the
table design, but got the same issue.

-Mark

 

[Douglas J. Steele]

Sorry, I can't think of other possible solutions.

 

[Guest]

I am having a similar problem linking tables to SQLServer or a different
access database. In my case, I get the ISAM error when I try to change the
tabledef.connect from one to the other.

The connection strings I use work if I am not changing from one type to
another.

ACCESS:
;Database=O:\FullPath\databasename.mdb

SQL
Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security Info=True

So I can change to a different access database or I can change to a
different SQL server or database in the same server.

I've looked at Doug's code on
http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried other
methods.

Id doesn't matter if the table to be changed is deleted first or not. If the
tabledef is deleted the ISAM error occurs as the new tabledef is appended. If
the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink

The simplified code comes down to the following when resetting the link
without deleting the existing tabledef:

tDef.Attributes = DB_ATTACHSAVEPWD
tDef.Connect = NewConnectionString
tDef.RefreshLink

And the following when deleting the tabledef:

DB.TableDefs.Delete (TableName)
DB.TableDefs.Refresh
tDef.Name = TableName
tDef.Attributes = DB_ATTACHSAVEPWD
tDef.SourceTableName = Owner & "." & TableName"
DB.TableDefs.Append tDef (error: Could not find installable isam)
DB.TableDefs(TableName).RefreshLink

This code and connect strings works well when not changing to a differnet
type of database.

Note: I know this works because I've done it before in a different
application but the same technique does not work in this database.

Thanks in advance for your help.

 

[Douglas J. Steele]

Check that the SourceTableName property is correct. If you're changing from
linking to Jet vs. linking to SQL Server, your SourceTableName will be
missing the owner (dbo.TableName, as opposed to TableName)

 

[Pieter Wijnen]

ODBC won't wash with ADO, I think
You need to use DAO to link, ah well, DAO tables (as you discovered)
ADOX is probably an alternative, but...

Pieter

 

[Guest]

Thanks for your quick reply Doug,

If I link the table manually to SQL Server the SourceTableName is
dbo.tablename. I hard coded the full name in the new tabledef either with the
same case and with all upper case. I get the same result.

 

[Guest]

Thanks for the quick reply Pieter,

As far a I understand, its all DAO.

I suppose I could have posted the initialization code as follows:

Dim DB As DAO.Database
Dim tDef As DAO.TableDef

Set DB as CurrentDb()

 

[Pieter Wijnen]

You need to include the table name in the link
Something like:

Const pODBCConnect = "ODBC;Driver={SQL Native Client};
Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security
Info=True"
'....
TDef.Connect = pODBCConnect & ";Table=dbo." & TDef.Name
'......

HtH

Pieter

 

[Guest]

Just to clarify, as a general comment about the missing ISAM.

Can we assume that if the tables can be linked manually or the link can be
updated programmatically without changing the linked database type, then the
ISAM is present and healthy?

If that is the case, then, another way to look at it would be that a table
cannot be created from scratch in this database with the same connect string
that will work to change an existing similar tabledef.connect string.

To explore that outlook on the problem I was able to create a table from
scratch using the same connect string that is used when the table is first
linked manually. Then I was able to change that connect string to the one I
want without an error.

I guess we have a solution except for one thing; The manual connect string
is using a ODBC Machine DSN and I dont really want to have to install a DSN
on every computer using this application. So my choice is to learn how to
create the correct Machine DSN programatically or figure out why the desired
connect string will not allow a tabledef to be created.

The SQL Server connect strings that will work to create a table
programmatically is:

"ODBC;DSN=EngManager;APP=Microsoft Office
2003;WSID=ROB-HOME;DATABASE=EngManager"

The DSN-less one that wont work is:

"Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security Info=True

I've trieed changing {SQL Native Client} to {SQL Server} and removing the
Persist Security Info=True

Thanks for the suggestions ...

 

[Douglas J. Steele]

I don't see where you're setting the Connect property in your " the
following when deleting the tabledef:" code

 

[Guest]

Sorry to detract from that problem at hand with that omission. I do set it
using tDef.connect = "strung".