Re: ISAM error when trying to create tablelink

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

  • Re: AllowBypassKey
    ... Dim db as Database ... Doug Steele, Microsoft Access MVP ... Dim prop As DAO.Property ...
    (microsoft.public.access.security)
  • Re: Calendar Form
    ... Doug Steele, Microsoft Access MVP ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't ... I am creating a database for a security company. ...
    (microsoft.public.access.forms)
  • Re: Calendar Form
    ... Doug Steele, Microsoft Access MVP ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't cause ... I am creating a database for a security company. ...
    (microsoft.public.access.forms)
  • Re: Calendar Form
    ... Doug Steele, Microsoft Access MVP ... Is there perhaps a way that I can insert a combo box on my calendar form for "CompanyName", in which I can select a company from the list, and then add/view information on the calendar for that specific company? ... you need to find the reference for Microsoft Office 12.0 Access database engine and select it. ... The fact that the previous line (Dim db As DAO.Database) doesn't cause problems implies that the DAO library is properly referenced. ...
    (microsoft.public.access.forms)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
Loading