Re: ISAM error when trying to create tablelink

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 ...

--
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: ISAM error when trying to create tablelink
    ... Doug Steele, Microsoft Access MVP ... tabledef is deleted the ISAM error occurs as the new tabledef is appended. ... Dim rs As New ADODB.Recordset ... Dim str As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: find and display files
    ... Dim strFilter As String ... Dim strInputFileName as String ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.formscoding)
  • Re: ISAM error when trying to create tablelink
    ... tabledef is deleted the ISAM error occurs as the new tabledef is appended. ... Doug Steele, Microsoft Access MVP ... Dim rs As New ADODB.Recordset ... Dim str As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Code for List Boxes
    ... Doug Steele, Microsoft Access MVP ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: find and display files
    ... Dim strFilter As String ... Dim strFolder As String ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.formscoding)