Re: ISAM error when trying to create tablelink

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()



--
RobGMiller


"Pieter Wijnen" wrote:

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

"RobGMiller" <RobGMiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5BFBC6A0-0EF4-4A34-B629-975BF8FA4F58@xxxxxxxxxxxxxxxx
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.


--
RobGMiller


"Douglas J. Steele" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@xxxxxxxxxxxxxxxx
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" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Mark" <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:91B63395-65B9-427E-A454-2540FB264D92@xxxxxxxxxxxxxxxx
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" wrote:

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

Sorry about that.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E6171895-EA3A-4F5F-9595-A804DA929B9E@xxxxxxxxxxxxxxxx
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" wrote:

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"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Mark" <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@xxxxxxxxxxxxxxxx
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

















.

Relevant Pages
Loading